How to Use the Simplest INDEX(MATCH) Exact Lookup
You say, “I could never use INDEX(MATCH): It looks too complicated!” Well, here is the very simplest example, which anyone can master (or mistress). Here we are only looking for an exact match. For simplicity, we will use one-column arrays only.
Once you are happy with this, if you want two-dimensional examples, read this following article.
If you aren’t sure whether to use the INDEX and MATCH functions together, or rather VLOOKUP, HLOOKUP, or LOOKUP, see this decision tree.
MATCH returns the position number of the matched item in a one-dimensional list. MATCH(lookup_value, lookup_array, 0) does an exact match. MATCH can also do an approximate match, which we are ignoring for now.
INDEX returns the value at a given row number in a range. Use INDEX(array, row_num) where row_num is the result of a MATCH. It can do columns too, but we are ignoring that form in this post.
1. INDEX(MATCH) Example Spreadsheet:
2. Find the Price of a Gizmo:
In the example above, in rows 16 to 19, column A contains the results of the formula as documented in column B.
In cell A16, =MATCH(“Gizmo”, A4:A11, 0) looks down the range A4:A11 for an exact match. It finds “Gizmo” in the fifth position, and returns the value 5. Note that this is its number in the list, not the row number in the spreadsheet (8)!
In cell A17, =INDEX(E4:E11, A16) then uses the previous results to return the value 1463. It is the 5th item in the range B4:E11.
In cell A19 we show how the whole process can work in one cell, using =INDEX(E4:E11, MATCH(“Gizmo”, A4:A11, 0))
Nesting the MATCH functions inside the INDEX makes the formula more compact. But the intermediate calculations make the process easier to understand. It is also easier to debug!
The lookup range that MATCH uses does not have to be next to –or indeed anywhere near– the range from which INDEX gets the final value.
Notice also that we do not include the column headings in the lookup ranges: A4:A11, not A3:A11, E4:E11, not E3:E11
3. Look up Prices for Random Items:
In the spreadsheet above, in rows 21 to 24, column A contains random item names. Column B has the results of the formula as documented in column C.
Let us take the formula in cell B21 (displayed in cell C21):
=INDEX(E$4:E$11, MATCH(A21, A$4:A$11, 0))
MATCH(A21, A$4:A$11, 0) takes the item name in A21 (Widget) and looks it up in the range A4:A11.
That result is then used by INDEX to find the price in the range E4:E11.
We need to put $ signs before the row numbers of both ranges. If we copy then down without the dollar signs, they will change and use the wrong ranges.
In the formula in cell B21 (displayed in cell C21), we keep A21 without dollars. This is because we want it to change to refer to the cell in column A on the same row as the formula.
4. Conclusion
The very simplest way to use INDEX(MATCH) uses two one-column arrays and looks for an exact match. MATCH returns the position of the sought item in the one array. INDEX then returns the item at that position in the second array. Simple, no?
This should be enough for many people, and effectively replaces VLOOKUP and HLOOKUP.
If you need more versatile, two-dimensional examples, read this article.
If you aren’t sure whether to use the INDEX and MATCH functions together, or rather VLOOKUP, HLOOKUP, or LOOKUP, see this decision tree.