VLOOKUP for multiple columns

Hi,

just as the name says, MS Excel’s VLOOKUP is pretty useful to look up values. However, it only works for comparing one cell with a certain value. What shall we do when we have more than one column to compare?

The easiest solution seems to be to create a dummy cell that combines the cells which we would like to search for by string concatenation. For example, let’s say we have a table that combines colors and shapes and assigns it prices:

A B C
1 Color Shape Price in $
2 Red Circle 15
2 Blue Square 10
3 Red Rectangle 20
4 Yellow Rhomb 25

(whatever the practical implications of that model might be. Maybe, a child tinkers something and sells it for fun.)

Now, in some other cell, we would like to explore the price for a yellow rhomb. What can we do?

We insert a new column before column D “color shape combination”. We write into C2:

=A2&B2

By double-clicking the black square in the bottom-right corner of the cell we can extend the formula to all the rows. The result is:

A B C D
1 Color Shape Color Shape Combination Price in $
2 Red Circle RedCircle 15
2 Blue Square BlueSquare 10
3 Red Rectangle RedRectangle 20
4 Yellow Rhomb YellowRhomb 25

Now, if we want to look up the price for, e.g., a yellow rhomb, we can just write:

=VLOOKUP("YellowRhomb";C2:D5;2;FALSE)

And we will get the desired result: 25. FALSE should be used in general when you want to have exact results.

Maybe, this is helpful for some of you. If you have other suggestions, feel free to post them.

Have a nice day,
Michael

Advertisements