Vlookup left

You want to return a value to the left of your lookup column?

Last time, we discussed the differences between vlookup and index/match, I mentioned using Index(Match) to return a value to the left. Unfortunately, Vlookup cannot be used to return a value to the left (or above) your lookup value.

Vlookup

Since Vlookup returns a value based on a table, it must assume that the index of the table is on the left. It is possible to use Vlookup to the left, but it’s much easier to remember how to use Index(Match) instead.

Index / Match

When I first discussed using Index(Match), I noted that we can use Index for the row number and Match for the column number. Let’s use the same data from that discussion –

Example Data

If you want to know what Price you sell a Fruit for, you can find the Fruit and return the Price. What are Lemons selling for?

=INDEX($C$2:$C$7,MATCH("Lemons",$A$2:$A$7,0)) = $3.00

In other words, find lemons in Column A and return the value from Column C.

Index / Match to the left

But what if you want to know what is selling for $2.50? Simply find 2.50 in Column C and return the value from Column A

 =INDEX($A$2:$A$7,MATCH(2.50,$C$2:$C$7,0)) = Coconuts

What about the most expensive item you can buy with $2.25?

=INDEX($A$2:$A$7,MATCH(2.25,$C$2:$C$7,1)) = Cherries

Using the argument 1 for [match_type] allows us to return the item that is closest to our lookup_value without going over. If you want to know what item is the smallest value that is greater than or equal to lookup_value you can use the -1 [match_type].

Index / Match above

With the horizontal table, to find what sells for $2.50, swap the ranges –

=INDEX($H$1:$M$1,MATCH(2.50,$H$3:$M$3,0)) = Coconuts

This can be used to look something up above, or upward.

Conclusion

As I’ve stated before, “inevitably, =Index(Match) is always the best option.”. As demonstrated above, there really is no limit on what it can do!

Leave a Reply

Your email address will not be published. Required fields are marked *