Two-Way Lookup

April 26, 2022 - by Bill Jelen

Problem: I need to do a lookup where I find the product ID down the left side and the month from the top row. I need to return the intersection of that row and column.

Strategy: You can use a MATCH to find the row, a second MATCH to find the column, and then an INDEX to return the correct value.

In this example, the person using the spreadsheet uses the Validation dropdowns in J2 and J3 to select a product and month.

Data Validation boxes let you choose an Account in J2 and then a Month in J3. — Figure 434. Select a product and month.

The lookup table has products in column A and months in row 1.

Given the Account and Month, do a two-way lookup into the data stored in A1:G99. This has Account in A, month in row 1, and answers at each intersection. — Figure 435. Find product A397 and Apr.

Your first formula will use MATCH to find the row within the table. Use =MATCH(J2,$A$2:$A$20,0). This is the same type of MATCH described in the previous three topics. The answer of 2 indicates that A397 is found in the second row of the lookup table.

The second formula will use MATCH to find the column within B1:G1. This means that MATCH can go both ways and essentially do an HLOOKUP. Use =MATCH(J3,B1:G1,0). The result of 4 indicates that Apr is in the fourth column of the lookup table.

Finally, use the INDEX function to return the value from row specified by the MATCH in J5 and from the column specified in J6. Use this formula: =INDEX(B2:G20,J5,J6).

Doing the two way look. First a MATCH to find which row has the account. Then a MATCH to find which column has the month. Finally, an INDEX that uses the answers from both MATCH to find the answer. — Figure 436. The MATCH in J6 is like an HLOOKUP.

This article is an excerpt from Power Excel With MrExcel

Title photo by Frank Busch on Unsplash