totn Excel Functions

MS Excel: Two-Dimensional Lookup (Example #4)

This Excel tutorial explains how to perform a two-dimensional lookup (with screenshots and step-by-step instructions). This is example #4.

Question: In Microsoft 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 look at an example.

Download Excel spreadsheet (as demonstrated below)

Microsoft Excel

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

Microsoft Excel

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.