So, you want to look something up in a table better
Last time, we explored the three main ways to look up values in tables to return corresponding data:
hlookup. As I said, “inevitably,
=Index(Match) is always the best option.” If you’re anything like me, once I knew
vlookup like the back of my hand, I figured why do I need this? Versatility
Consider the same “table” (Range), both oriented vertically and horizontally.
index(array, row_num, [column_num])
- array: The “table” range to reference
- row_num: The row number of the element you want
- [column_num]: The column of the element you want. Optional.
The first thing you notice is that, no, this can’t find a value. All it does is return a value in an array. Using the vertical array above:
=Index($A$1:$C$7,1) = #REF =Index($A$1:$C$7,1,3) = "Price" =Index($A$1:$C$7,3,1) = "Bananas"
match(lookup_value, lookup_array, [match_type])
- lookup_value: What you want to look up.
- lookup_array: Where you want to look.
- [match_type]: How you want to match the lookup_value. Optional.
match_type can be 1 (default), 0 or -1. Just consider 1 to be the same as
TRUE in vlookup as it will behave the same way (Price Is Right rules: the closest without going over). 0 will only return an exact match. -1 is basically a vlookup with
TRUE that searches from the bottom of the list rather than the top. The list must be in ascending order for ` and descending order for -1.
Ah, a lookup_value, that looks familiar! The difference is that
match returns a number – the location of your lookup_value. Consider the vertical array above:
=MATCH("lemons",$A$2:$C$7,0) = #N/A =MATCH("lemons",$A$2:$A$7,0) = 5 =MATCH(70,$B$2:$B$7,0) = 2 =MATCH(1.25,$C$2:$C$7,0) = #N/A =MATCH(1.25,$C$2:$C$7,1) = 1
You see that
Match only works with a 1 dimensional array, in this case a column, and it returns the row_num of the match. This is the position of the match within the list.
Now watch what happens With the horizontal array above:
=MATCH("cherries",$G$1:$M$1,0) = 4
It’s returning the col_num as the position in the list. You see, both arrays can be thought of as lists. Regardless of whether it’s a vertical or horizontal list,
match returns your lookup_value’s number on the list. “Cherries” is the 4th member of the horizontal list above, but its actual column position is 10. This is an important rule to remember, your result will give you answers relative to your array, not your worksheet.
We have two arguments we can use in
index – row_num and column_num. And we can return the position of a value in a list vertically or horizontally with
match. You know where this is going, don’t you? You can index your results relative to your data array and return the value from a position relative to your array. So if you want to look up the inventory for “lemons” in the vertical array-
=INDEX($B$2:$B$7,MATCH("lemons",$A$2:$A$7,0)) = 90
match replaced the
row_num argument in
index. Wait, what?
=MATCH("lemons",$A$2:$A$7,0) = 5
So we want the 5th item in the inventory list
=Index($B$2:$B$7, 5) = 90
It’s that simple. Use
match to get the position of what you want in the column you’ve indexed. But how’s that different than vlookup? I said it before – versatility. Suppose you want to know what fruit is the most expensive fruit under $3.00. How would vlookup return a column to the left of Price? Let me tell you – not very easily.
Index(Match) to the rescue!
=INDEX($A$2:$A$7,MATCH(2.99,$C$2:$C$7,1)) = Coconuts
If you grab a copy of the example workbook, you’ll see how to use
Index(Match) on the horizontal table, but why not give it a try before looking at the answer? I’m confident you’ll get it with the information above!