Use INDEX(MATCH) instead of an Excel LOOKUP Function
For some lookups, the INDEX and MATCH functions used together may be the solution. Together, they are more robust and versatile than VLOOKUP, HLOOKUP, and LOOKUP.
MATCH returns the position of the matched item in a one-dimensional list. It can do an exact or approximate match. MATCH(lookup_value, lookup_array, 0) does an exact match.
INDEX returns the value at given row and column numbers in a range. INDEX (array, row_num, column_num) where row_num and/or column_num are the result of a MATCH.
In the example above, column A contains the results of the formula as documented in column B.
In cell A18, =MATCH(“Gizmo”, A4:A13, 0) looks down the range A4:A13 for an exact match. It finds “Gizmo” in the fifth position, and returns the value 5.
In cell A19, =MATCH(2022, B3:E3, 0) looks across the range B3:E3 for an exact match. It finds the year 2022 in the fourth position and returns the value 4.
In cell A20, =INDEX(B4:E13, A18, A19) then uses the previous two results to return the value 1463. It is at the 5th row and 4th column of the range B4:E13.
In cell A21 we show how the whole process can work in one cell, using =INDEX(B4:E13, MATCH(“Gizmo”, A4:A13, 0), MATCH(2022, B3:E3, 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!
Note that the lookup ranges that MATCH uses do not have to be next to –or indeed anywhere near– the range from which INDEX gets the final value.
MATCH Syntax
MATCH(lookup_value, lookup_array, [match_type])
The MATCH function has these arguments:
lookup_value (required): The value to find in lookup_array. This can be a literal value (number, text, or logical value) or a cell reference to a number, text, or logical value.
lookup_array (required): The one-dimensional range of cells you are searching.
match_type (optional): The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The options are:
- If match_type = 0, MATCH does an exact match. It finds the first value exactly equal to lookup_value. If it cannot find an exact match, it returns the #N/A error value. The values in lookup_array can be in any order.
- If match_type = 1 or not used, MATCH does an approximate match. It finds the largest value that is less than or equal to lookup_value. The values in lookup_array must be in ascending order, that is: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.
- If match_type = -1, MATCH does a reverse order approximate match. It finds the smallest value that is greater than or equal to lookup_value. The values in lookup_array must be in descending order, that is: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …
MATCH returns a number, the position of the matched value within lookup_array, not the value itself.
When finding text, MATCH is case-insensitive (it treats upper- and lower-case the same).
If match_type is 0 (exact) and lookup_value is text, you can use wildcard characters in the lookup_value. A question mark (?) matches any single character; an asterisk (*) matches any number of any characters. To find an actual question mark or asterisk, put a tilde (~) before the character (~? or ~*).
INDEX Syntax
INDEX(array, row_num, [column_num])
array (required): A range of cells or an array constant. If array contains only one row or column, the corresponding row_num or column_num argument is optional.
row_num (required unless column_num is present): Selects the row in array from which to return a value. If row_num is not used, column_num is obligatory.
column_num (optional): Selects the column in array from which to return a value. If column_num is not used, row_num is obligatory.
1.1.3 INDEX Notes
If you use both row_num and column_num, INDEX returns the value in the cell at that row and column in the array.
row_num and column_num must point to a cell within array; otherwise, INDEX returns a #REF! error.
If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
INDEX normally returns a value. However, if it is inside another formula that expects a cell reference, it returns a cell reference. Microsoft literature refers to this as the “Reference form”. For Example:
In the example above, column A contains the results of the formula as documented in column B.
In cell A24, the formula =INDEX(B4:E13, 1, 1) finds the value 1009.
In cell A25, =CELL(“address”, A24) yields the cell address $A$24, as you would expect.
But in cell A26, we nest the formula from A24 inside the one in A25. The combined formula =CELL(“address”, INDEX(B4:E13, 1, 1)) gives the answer “$B$4”. Here, INDEX(B4:E13, 1, 1) is used as a reference to cell B4. The value in cell B4 would be useless as input to the CELL formula. Clever! Sometimes you may want this. At other times you may puzzle at the seeming inconsistency in Excel “going the extra mile”.
INDEX: The optional 4th parameter [area_num]
This section is obscure and you’ll probably never need it but, just in case…
When typing in an INDEX formula, you may see this:
The “reference” version of the formula takes an optional 4th parameter [area_num]. The syntax is
=INDEX(reference, row_num, [column_num], [area_num])
“Reference” can refer to many separate ranges. You can’t enter several range references as the first parameter when typing or using the formula editor. But you can use a named range:
Here, we pre-defined the named range “Ref_Range” = B4:E7, B9:E13 (as highlighted in the picture). For some obscure reason, we wanted to exclude Gizmos but didn’t want to create a new table.
We then use the range name Ref_Range as the first parameter. Ref_Range consists of two ranges, B4:E7 and B9:E13. In the above example, we use the optional 4th parameter area_num=2 to specify that we want data from the second block, B9:E13. In that block, for row_num=1 and column_num=1, in cell A17, INDEX finds the value 1154 (circled above).
If area_num=1 or not used (cell A20), INDEX finds the value 1009 from the first block, B4:E7.