Comparing VLOOKUP, INDEX, and MATCH

Excel

VLOOKUP and the combination of MATCH and INDEX retrieve cell contents from a table. But, there are important differences between the two.

You need to know when to use VLOOKUP and when to use INDEX and MATCH because each has unique abilities. Here’s a quick decision table that will help you know when to use each of them,

Vertical Horizontal Two-Way
Search In Difficulty Flexibility
HLOOKUP Yes Top row Easy Low
VLOOKUP  Yes  Leftmost col Easy Low
VLOOKUP
MATCH
 Yes Yes Leftmost col
Top row
Mid Mid
INDEX
MATCH
 Yes  Any column  Mid  Mid
INDEX
MATCH
MATCH
Yes Any column
Any row
Mid+ High
OFFSET
MATCH
MATCH
Yes Any column
Any row
Mid+ High

When should you use VLOOKUP to retrieve data from a table?

VLOOKUP is quick and easy to create, but it is inflexible and error prone.

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

  • The column being searched must be the leftmost column.
  • The data being extracted must be to the right of the column being searched. (I guess that’s a corollary to the previous bullet, but I just wanted it to be clear. There are solutions on the internet that go through excruciating trouble to show how to lookup data to the left with VLOOKUP, but that’s crazy. Just use INDEX and MATCH, it is a much easier solution.)
  • If you want an exact match for the search item you must specify a FALSE parameter for [range_lookup]. If you don’t you could get a wrong answer without knowing it.
  • You can’t look for a match in one table and extract from another.

When should you use INDEX and MATCH to retrieve data from a table?

INDEX and MATCH combinations are slightly more work to create than VLOOKUP, but they are significantly more flexible and have far more capability

INDEX(array,row_num,[column_num])

MATCH(lookup_value,lookup_array,[match_type])

INDEX(array,MATCH(lookup_value,lookup_array,[match_type]),[column_num])

  • The column being searched for a matching to the lookup_value can be any column in the table.
  • Data being retrieved can be to the left or right of the searched column.
  • Data being retrieved can even be in another table, or multiple tables, just so long as the tables have a parallel structure where the rows contain data belonging to the same “key” that was searched for.
  • Reverse lookups are easy, for example, search for Vendor by Contract Number, or search for Contract Number by Vendor. To do that an option button can be used with CHOOSE to switch between two INDEX/MATCH pairs.
  • Save calculation time if you have many lookups from one table. You can search with a single MATCH function, then use the row number it returns in many INDEX functions to retrieve many pieces of data from the row. That means you are only searching once, and using INDEX to quickly retrieve many pieces of data.
  • Retrieve data using a two-way dynamic lookup, INDEX MATCH MATCH. This means you can move, insert, or delete rows and columns and the formula still works. With this technique rows and columns do not look in a row or column specified by a static number. Instead a MATCH finds the row with the correct row data. Another MATCH finds the column with the correct column header. And then INDEX returns the data using this row and column.
  • Retrieve multiple rows of data at one time. For example, MATCH finds the first row containing a match for the search item and then INDEX retrieves that row and the following five rows.
  • The last parameter, match_type, indicates the type of match. Use -1 for matching an answer less than, 0 for an exact match, and +1 for a match greater than the lookup_value. You will almost always use 0.
Share the power...