Performing a Two-Way Lookup in Excel

In Excel, it’s common to need to find a value in a table based on both the row and column headings. This is where a two-way lookup comes in handy.

Performing a Two-Way Lookup in Excel

Performing a Two-Way Lookup in Excel

To perform a two-way lookup in Excel, you need to combine the MATCH function with the VLOOKUP function. This allows you to retrieve a value from any cell in a two-dimensional table.

The VLOOKUP function alone can only search for a value in a single column at a time, as its col_index argument is static. However, by using MATCH in conjunction with VLOOKUP, you can make the col_index dynamic.

For example, let’s say you have a table with monthly sales quantities for different zones of a company. If your boss wants to know the quantity for a particular month and zone, you can use the following formula:

=VLOOKUP(month, table, MATCH(zone, heading_row, 0), 0)

Here, the MATCH function returns the position of the zone in the heading row, and VLOOKUP uses that position to retrieve the value from the corresponding column.

By using a dynamic col_index with VLOOKUP and the MATCH function, you can easily perform two-way lookups in Excel to retrieve values from any cell in a two-dimensional table.