Excel: Two-Dimensional Lookup (Example #4)

Question: In Excel, I want to match the values in two columns on sheet A (size and frequency) to a column and a row on sheet B (size and frequency) to return a value on sheet B.

For example, size=2 and frequency=2 on Sheet A would return $23.00 from the chart on Sheet B (size=2, frequency=2). I would like to have these values displayed in Column D on sheet A.

Answer: In effect, what we are trying to do is perform a 2-dimensional lookup in Excel. To find a value in Excel based on both a column and row value, you will need to use both the vlookup function and the match function.

Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In the spreadsheet above, we have a chart on sheet B with Size and Frequency.

Then on Sheet A, we have Size and Frequency values in columns A and B. We want to return in column D, the correct value from the chart in Sheet B using these Size and Frequency values.

So if you looked up a Size=2 and Frequency=2 in the chart in sheet B, it should return $23.00. To do this, we will use the following formula in cell D2:

=VLOOKUP(A2,B!$A$2:$G$8,MATCH(B2,B!$A$2:$G$2,0),FALSE)

This formula returns $23.00.

In cell D3, if we wanted to look up a Size=2 and Frequency=1, it should return $22.00. To do this, we will use the following formula:

=VLOOKUP(A3,B!$A$2:$G$8,MATCH(B3,B!$A$2:$G$2,0),FALSE)

This formula return $22.00.