Looking Up a Value Based on a Two-Way Matrix in Excel Formula

A two-way lookup (also known as a matrix lookup) is a technique to find a value at the intersection of a certain row and column in a table. For example, you may want to look up the sales of a specific product in a specific month, or the population of a certain animal in a certain year.

There are different ways to perform a two-way lookup in Excel, but the most common ones are using the INDEX and MATCH functions, or the VLOOKUP and MATCH functions. These functions allow you to create a dynamic formula that can adjust to changes in the table size, layout, or data.

The INDEX function returns a value from a range based on a given row and column number. The syntax is:

INDEX(array, row_num, [column_num])

The MATCH function returns the relative position of a value in a range. The syntax is:

MATCH(lookup_value, lookup_array, [match_type])

The VLOOKUP function returns a value from a range based on a given value in the first column. The syntax is:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Procedures

To perform a two-way lookup using INDEX and MATCH, you need to nest two MATCH functions inside the INDEX function, one for the row number and one for the column number. The formula is:

INDEX(data_array, MATCH(vlookup_value, lookup_column_range, 0), MATCH(hlookup_value, lookup_row_range, 0))

To perform a two-way lookup using VLOOKUP and MATCH, you need to use the MATCH function as the column index argument for the VLOOKUP function. The formula is:

VLOOKUP(value, table, MATCH(value, range, 0), 0)

In both cases, you need to set the match type argument to 0 to force an exact match. You also need to make sure that the lookup ranges match the size of the data array.

Comprehensive Explanation

Let’s see how these formulas work with an example. Suppose you have the following table of sales data for different products and months:

You want to find the sales of product C in March. You can use either of the following formulas:

=INDEX(B2:E5, MATCH("C", A2:A5, 0), MATCH("Mar", B1:E1, 0))

=VLOOKUP("C", A2:E5, MATCH("Mar", A1:E1, 0), 0)

Here is how the formulas work:

• The INDEX function takes the data array as B2:E5, which contains the sales values.
• The first MATCH function takes the vlookup value as “C”, which is the product name we want to find. It searches for this value in the lookup column range A2:A5, which contains the product names. It returns 3, which is the relative position of “C” in A2:A5.
• The second MATCH function takes the hlookup value as “Mar”, which is the month name we want to find. It searches for this value in the lookup row range B1:E1, which contains the month names. It returns 3, which is the relative position of “Mar” in B1:E1.
• The INDEX function then returns the value at the intersection of the 3rd row and the 3rd column in B2:E5, which is 340. This is the sales of product C in March.
• The VLOOKUP function takes the lookup value as “C”, which is the same as the vlookup value in the INDEX formula. It searches for this value in the first column of the table array A2:E5, which is the same as the data array and the lookup column range in the INDEX formula.
• The MATCH function takes the value as “Mar”, which is the same as the hlookup value in the INDEX formula. It searches for this value in the range A1:E1, which spans the same number of columns as the table array. It returns 3, which is the same as the column number in the INDEX formula.
• The VLOOKUP function then returns the value in the 3rd column of the row where “C” is found in A2:E5, which is 340. This is the same as the value returned by the INDEX formula.

Example

Let’s see another example with a different table of data. Suppose you have the following table of population data for different animals and years:

You want to find the population of pandas in 2010. You can use either of the following formulas:

=INDEX(B2:E4, MATCH("Panda", A2:A4, 0), MATCH(2010, B1:E1, 0))

=VLOOKUP("Panda", A2:E4, MATCH(2010, A1:E1, 0), 0)

Here is how the formulas work:

• The INDEX function takes the data array as B2:E4, which contains the population values.
• The first MATCH function takes the vlookup value as “Panda”, which is the animal name we want to find. It searches for this value in the lookup column range A2:A4, which contains the animal names. It returns 2, which is the relative position of “Panda” in A2:A4.
• The second MATCH function takes the hlookup value as 2010, which is the year we want to find. It searches for this value in the lookup row range B1:E1, which contains the years. It returns 3, which is the relative position of 2010 in B1:E1.
• The INDEX function then returns the value at the intersection of the 2nd row and the 3rd column in B2:E4, which is 1500. This is the population of pandas in 2010.
• The VLOOKUP function takes the lookup value as “Panda”, which is the same as the vlookup value in the INDEX formula. It searches for this value in the first column of the table array A2:E4, which is the same as the data array and the lookup column range in the INDEX formula.
• The MATCH function takes the value as 2010, which is the same as the hlookup value in the INDEX formula. It searches for this value in the range A1:E1, which spans the same number of columns as the table array. It returns 3, which is the same as the column number in the INDEX formula.
• The VLOOKUP function then returns the value in the 3rd column of the row where “Panda” is found in A2:E4, which is 1500. This is the same as the value returned by the INDEX formula.

Explanation

To illustrate the formulas visually, you can use an Excel table to show the data and the results. Here is an example of how the table might look like:

The formula in cell G2 is:

=INDEX(B2:E4, MATCH(F2, A2:A4, 0), MATCH(F5, B1:E1, 0))

or

=VLOOKUP(F2, A2:E4, MATCH(F5, A1:E1, 0), 0)

The formula returns 1500, which is the value in the cell D3, where the row header is “Panda” and the column header is 2010.

Other Approaches

Besides using INDEX and MATCH or VLOOKUP and MATCH, there are other ways to perform a two-way lookup in Excel. Here are some alternatives:

• You can use the XLOOKUP function, which is a new function introduced in Excel 365. It can perform a two-way lookup by nesting one XLOOKUP inside another. The formula is:

=XLOOKUP(hlookup_value, lookup_row_range, XLOOKUP(vlookup_value, lookup_column_range, data_array))

For example, to find the population of pandas in 2010, you can use:

`=XLOOKUP(2010, B1:E1, XLOOKUP(“Panda”, A2:A4, B2:E4))`

The XLOOKUP function can return a value from a range based on a given value and a match mode. The syntax is:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

For example, to find the population of pandas in 2010, you can use:

=XLOOKUP(2010, B1:E1, XLOOKUP("Panda", A2:A4, B2:E4))

Here is how the formula works:

• The inner XLOOKUP function takes the lookup value as “Panda”, which is the animal name we want to find. It searches for this value in the lookup array A2:A4, which contains the animal names. It returns the corresponding row from the return array B2:E4, which contains the population values.
• The outer XLOOKUP function takes the lookup value as 2010, which is the year we want to find. It searches for this value in the lookup array B1:E1, which contains the years. It returns the corresponding value from the return array, which is the row returned by the inner XLOOKUP function.
• The XLOOKUP function then returns the value at the intersection of the row where “Panda” is found and the column where 2010 is found, which is 1500. This is the population of pandas in 2010.
• You can use the SUMIFS function, which can sum the values in a range that meet multiple criteria. The formula is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

For example, to find the population of pandas in 2010, you can use:

=SUMIFS(B2:E4, A2:A4, "Panda", B1:E1, 2010)

Here is how the formula works:

• The SUMIFS function takes the sum range as B2:E4, which contains the population values.
• The first criteria range is A2:A4, which contains the animal names. The first criteria is “Panda”, which is the animal name we want to find.
• The second criteria range is B1:E1, which contains the years. The second criteria is 2010, which is the year we want to find.
• The SUMIFS function then sums the values in B2:E4 that meet both criteria, which is the value at the intersection of the row where “Panda” is found and the column where 2010 is found, which is 1500. This is the population of pandas in 2010.