Last week’s #FunctionFriday video tutorial covered VLOOKUP tips and techniques for marketers. This week’s video explores the INDEX MATCH alternative to VLOOKUP.
Why would VLOOKUP need an understudy? Because VLOOKUPs require that the lookup column be the far-left column in your lookup data set (aka the table_array argument). The INDEX-MATCH dream team doesn’t have that requirement, making it more flexible.
Comparing VLOOKUP and MATCH
I make a comment in the video about how the MATCH function is nearly identical to the VLOOKUP function and wanted to expand on that here.
Here’s the syntax for VLOOKUP:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
And here’s the syntax for MATCH:
MATCH(lookup_value, lookup_array, [match_type])
For marketing purposes, they’re practically fraternal twins. But there are a couple key differences:
- With VLOOKUP, you’re working with a table, so you need the col_index_num argument. With MATCH you’re just feeding it one column or row.
- Much like the INDEX function, VLOOKUP returns the value in the cell that meets the lookup requirements; MATCH returns the relative position of the cell where it finds a match to your lookup value.
Note: Since marketers are most commonly working with exact match for both of these functions and you can use 0 in lieu of FALSE in a VLOOKUP, the range_lookup and match_type arguments accomplish identical objectives: telling Excel to only look for exact matches.
You can download the file I used in the video tutorial here.