• Excel Book Excerpt

Excel Two-Way Lookup

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Two-Way Lookup

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.

LE10000447.jpg

Figure 442 Select a product and month.

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

LE10000448.jpg

Figure 443 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).

LE10000449.jpg

Figure 444 The MATCH in J6 is like an HLOOKUP.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: