Workiva Support Center

You can use MATCH with INDEX to create a lookup formula that's more flexible than VLOOKUP. This combination is often referred to as INDEX-MATCH.

Here is a scenario to demonstrate INDEX-MATCH using this table of sales data:

Now, let's say you want to look up a salesperson's commission based on their name. Here are the equivalent INDEX-MATCH and VLOOKUP formulas:

• INDEX-MATCH: =INDEX(D2:D6,MATCH("Bob",A2:A6,0))

• VLOOKUP: =VLOOKUP("Bob",A2:D6,4,FALSE)

Both formulas will return 600, which is Bob's commission.

Here's why INDEX-MATCH is more flexible:

• Column Independence: With VLOOKUP, the column containing the return value (commission) must always be to the right of the lookup column (name), and you need to count columns to specify which one contains your desired data (4 in this case). 
  With INDEX-MATCH, the return column can be anywhere in the table. If the columns are rearranged so that Commission was in column B, you can easily adjust the INDEX-MATCH formula (replacing D2:D6 with B2:B6). 
  =INDEX(B2:B6,MATCH("Bob",A2:A6,0)) 
  The VLOOKUP formula would break in this scenario.
• Left Lookup: VLOOKUP can only look up values in the leftmost column of a range. If you wanted to look up a name based on a commission value, you couldn't use VLOOKUP. However, INDEX-MATCH allows you to use any column:
  =INDEX(A2:A6,MATCH(600,D2:D6,0)) 
  This would return "Bob".
• Performance: For large datasets, INDEX-MATCH can be more efficient than VLOOKUP, especially if you're doing many lookups on the same table.