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.
Video
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.
Excel Download
You can download the file I used in the video tutorial here.
The link to download the file seems to be broken
It’s working for me. It’s a Dropbox link.
Ah, gotcha. That’s blocked at work. Thanks!
Humph. Here you go! https://annielytics.com/?attachment_id=2332
Awesome. Thanks!
You’re welcome!
It’s also possible to use INDEX MATCH to act like VLOOKUP when you’re trying to use two values as criterias!
=INDEX(table with value you want,MATCH(1,(ColumnA=Criteria1)*(ColumnB=Criteria2),0),column with the data you want)
All you need to do is CTRL-ALT-ENTER when entering the function.
I’d love to see an actual example of this – perhaps a screenshot or uploaded workbook?
Hi –
I’m working on a project where I need to use the MATCH function, but I need to consider that the content I’m looking for is not an exact match. For example, I pulled a list of companies from Salesforce, and we want to match those against internal records. Since company names are not always entered in properly in SFDC, or don’t have the “Inc.” or “Ltd.” included, for example, I need flexibility to search for part or variations of the company name. What do you suggest I use for this formula?
Thanks!
You can use wildcards to get a partial match. I use them a lot with SEARCH functions, but you can use them with any text-lookup function in Excel.
Hi Annie. This video is awesome.
I just had one question…can you explain the COLUMN() function you use at the end of the INDEX function? How would this work if I didn’t have an empty far right column?
Thanks!
The COLUMN function just enables the formula to update dynamically so that you don’t have to manually write the formula for each column. I explain how to use it in the video on this page as well as the video on this page: https://annielytics.com/blog/excel-tips/vlookup-tips-tricks-marketers/.