Index/Match

So, you want to look something up in a table better

Grab a copy of this example workbook

Last time, we explored the three main ways to look up values in tables to return corresponding data: lookup, vlookup and 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.

Example Data


Index

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

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.

Index(Match)

We have two arguments we can use in indexrow_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

The 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

Conclusion

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!

Leave a Reply

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