Using INDEX and MATCH to Replace VLOOKUP
What we really need is to be able to look up the Model and the Displacement at the same time. MATCH is a function that gives you the location of an item in an array. The syntax for MATCH is as follows:
=MATCH(lookup_value, lookup_array, [match_type])
The lookup_value is what you are searching for.
The lookup_array is the array of values you are trying to find the lookup_value in.
The optional match_type determines whether MATCH must find the lookup_value exactly (with a 0), or return the closest match that comes before it (with a 1) or after it (with a -1) alphanumerically.
The INDEX function takes a location and returns the value that is in the cell. The syntax for INDEX is as follows:
=INDEX(array, row_num, [col_num])
The array is the table of data that contains the cell value you want.
The row_num is the relative row number of the cell you want.
The col_num is the relative column number of the cell you want.
By combining INDEX and MATCH we can produce the same result as VLOOKUP. Using the same search we did for VLOOKUP, the INDEX/MATCH pair looks like this:
C4:H1159 is the array that INDEX uses to find the value.
A2 is the cell that the value we want MATCH to find.
C4:C1159 is the lookup_array that MATCH looks through to find the value in A2.
The 0 means that MATCH will look for the exact value instead of an approximate one.
Column 6 is the column in the C4:H1159 array that holds the Combined Fuel Economy values.
The result is identical to the VLOOKUP result. MATCH finds the first Combined Fuel Economy value for the Acura ILX, which means it will give 38 MPG for the 1.5 instead of one of the other engine options. To find a specific Model and engine Displacement combination, we need to modify our INDEX/MATCH formula into an array formula.
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
Other posts in this series...
- VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)
- Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE
- Extract a List of Values Filtered by Criteria with Sub-Arrays
- How to Combine (Concatenate) Data from Multiple Rows into One Cell
- How to Unpivot Columns into Rows in Excel Using Power Query