There are many ways of looking up and retrieving values from a table of data in Excel. The VLOOKUP() and HLOOKUP() functions are widely used because of their simplicity, but they have some limitations and can fall down in certain situations. The INDEX(MATCH()) combo is a far more robust and reliable method. This blog will detail how to use the formula to best effect.
This blog will assume you already know how to use the VLOOKUP function. You will therefore know that the following limitations can sometimes be a source of frustration:
- You cannot look up values to the left of the reference. VLOOKUP() only looks to the right.
- You must specify a single column reference (e.g. 5th column to the right) where the desired data must be located.
- You can only specify a single lookup value as a reference.
The INDEX() and MATCH() functions both have uses in their own right, but it is when they are paired together that they yield the greatest benefit.
On its own, INDEX() comprises a minimum of two parts:
- An array (i.e. column(s) of data where the target value can be found;
- A row reference;
- A column reference (if more than one column is specified in the array)
In the example below, the formula is searching for data in columns B and C. It is pulling in the value that is on the 5th row of the array, and in the 2nd column.
On its own, MATCH() effectively performs the reverse function. It reads through a row or column of data, searching for a specified lookup value. It will then return the row / column number where the lookup value is located.
In the example, below, we have specified the value in cell I4 (i.e. “USB Cable”) as the lookup value, and column C as the array. It has returned a value of ‘5’ because USB Cable is on row 5 of the table.
The MATCH() function is therefore acting as our lookup, returning a column and row reference, which then allows the INDEX() function to pull out the relevant data.
An example of the two functions working in tandem is shown below. The product “USB Cable” is our lookup value. The three cells in yellow all contain INDEX(MATCH()) formulas.
You can see that the MATCH() part of the formula references a lookup in column C, and the INDEX() part of the formula then finds a value from column B, which is to the left of the lookup. This would not be possible with a VLOOKUP() formula.
In each of these formulas, the INDEX() element refers to just one column, hence only one MATCH() expression is required. If the INDEX() refers to multiple columns, a second MATCH() expression is required to provide a column reference. This can be referenced against the column header, as below:
This formula does not contain any pre-defined row or column references. Everything is based on lookup values, which means that even if rows or columns are inserted into the table, the formula will still find the required data.
The final trick that INDEX(MATCH()) allows you to do is to interrogate an array based on multiple lookup criteria. For example, in our sample data set we might want to find the details for the product “Keyboard”, but this appears in the list twice.
We therefore need to specify both a region and a product as lookup values. This can be done using the first MATCH() expression. Two lookup values can be set, separated by an ampersand (&). The two lookup ranges would also be separated by an ampersand, as shown below.
Note: This formula must be entered as an array (CTRL, SHIFT + ENTER).
Download The Workbook
Formula Breakdown
The standard (and most useful) INDEX(MATCH()) combo formula is shown below. It calculates using the following steps:-
INDEX() Element
=INDEX($B$3:$E$14,
Defines the data range where the target value can be found
First MATCH() Element
MATCH($I$14,$C$3:$C$14,0),
Find the value in cell I14 in the defined range and return the row number
Second MATCH() Element
MATCH(H$13,$B$2:$E$2,0))
Find the value in cell H13 in the defined range and return the column number
Get The Formula
The full formula used in this blog is as follows:-
=INDEX($B$3:$E$14,MATCH($I$14,$C$3:$C$14,0),MATCH(H$13,$B$2:$E$2,0))
angus
thanks a lot – saved me a load of time to work it our for myself 🙂