Master Excel’s INDEX MATCH MATCH Function: A Step-by-Step Guide to Accurate Search Results

The INDEX function is used to retrieve a value from a specific cell within a range of cells. The syntax for INDEX is “=INDEX(range, row_num, col_num)”, where range is the range of cells you want to retrieve a value from, row_num is the row number of the cell you want to retrieve, and col_num is the column number of the cell you want to retrieve.

The MATCH function is used to find the position of a value within a range of cells. The syntax for MATCH is “=MATCH(lookup_value, lookup_range, match_type)”, where lookup_value is the value you want to find, lookup_range is the range of cells to search in, and match_type is 0 for an exact match or 1 for an approximate match.

In the INDEX MATCH MATCH formula, the first MATCH function is used to find the row number of the lookup value within the lookup range. The second MATCH function is used to find the column number of the column header within the header range.

The INDEX function then uses the row number and column number returned by the MATCH functions to retrieve the value from the corresponding cell within the data range.

For example, let’s say you have a table of data with headers in row 1 and values in rows 2 through 6. The lookup value is in cell D1, and you want to retrieve the value in the “Full Name” column that corresponds to the lookup value.

You would use the following formula:

=INDEX(G2:S1001,(MATCH(A6,$F$2:$F$1001,0)),(MATCH(B5,$G$1:$S$1,0)))

Here,

G2:S1001 is the data range which contains all the data
A6 is the cell which contains the lookup value
$F$2:$F$1001 is the lookup range which contains all the values that you want to match against
B5 is the column header which you want to retrieve the corresponding value of
$G$1:$S$1 is the header range which contains all the column headers
The MATCH function will first look for the exact match of A6 in the $F$2:$F$1001 range, and return the row number where it is found, let’s say it is 5
Then it will look for the column header B5 in the $G$1:$S$1 range and return the column number, let’s say it is 1

Now INDEX function will use these 2 numbers, 5 and 1 to retrieve the value from the 5th row and 1st column, which is the “Full Name” corresponding to the lookup value A6

Note: The value returned will be the value of the cell in the data range that corresponds to the lookup value in the lookup range, and the column header in the header range.