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 –
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!