Performing Two-Dimensional Lookups
| Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. |
Inside Microsoft Excel
A Publication of The Cobb Group
Published November 1997
You can use the MATCH() function in conjunction with the VLOOKUP() and HLOOKUP() functions to locate entries that match two values.
Excel's VLOOKUP() function looks up the entry specified by its first argument within the first column of the range specified by its second argument and returns the entry corresponding to the matching entry from the column specified by its third argument. For example, the function
=VLOOKUP(0.75,B3:H8,2)
in cell B13 of the worksheet shown in Figure A returns the value 4.54 from cell C5, since the value in the third cell in the first column of the range specified by the second argument matches the first argument of the function, and the third argument specifies the second column of the table.
.gif "Cc750573.ime97b4a(en-us,TechNet.10).gif")
Figure A: The function in cell B13 returns the value from cell C5
The HLOOKUP() function looks up the entry specified by its first argument within the first row of the range specified by its second argument and returns the entry corresponding to the matching entry from the row specified by its third argument. For example, the function
=HLOOKUP(1.25,C2:H8,4)
in cell B13 of the worksheet shown in Figure B returns the value 11.1 from cell G5, since the value in the fifth cell in the first column of the range specified by the second argument matches the first argument of the function, and the third argument specifies the fourth row of the table.
.gif "Cc750573.ime97b4b(en-us,TechNet.10).gif")
Figure B: The function in cell B13 returns the value from cell G5
In most cases, you'll want to look up results based on a single entry—precisely what the VLOOKUP() and HLOOKUP() functions do. However, you'll sometimes want to look up results based on two different pieces of information. For example, you may want to look up the price from the table shown in Figures A and B for an item that is 0.75 inch wide and 1.25 inches tall. Unfortunately, you can't do this with VLOOKUP() and HLOOKUP() functions alone. However, you can do so by using these functions along with the MATCH() function.
Like the VLOOKUP() and HLOOKUP() functions, Excel's MATCH() function looks up the entry specified by its first argument within the range specified by its second argument. (Unlike the second argument of the VLOOKUP() and HLOOKUP() functions, the second argument of a MATCH() function must specify a single-column or single-row range.) However, instead of returning the matching entry, the MATCH() function returns the position of the cell that contains the match. For example, the function
=MATCH(0.75,B3:B8)
in cell B13 of the worksheet shown in Figure C returns the value 3, since the entry in the third cell of the single-column range specified by the second argument matches the values specified by the first argument of the function. Similarly, the function
=MATCH(1.25,C2:H2)
will return the value 5, since the value in cell G2 (the fifth cell in the single-row range specified by the second argument of the function) matches the value specified by the first argument of the function.
.gif "Cc750573.ime97b4c(en-us,TechNet.10).gif")
Figure C: The MATCH() function returns the location of the matching entry—not that entry itself.
On This Page
Two-dimensional lookups
An alternative technique
Exact-matching lookups
Two-dimensional lookups
To perform a two-dimensional lookup, you must use a function in the form
=VLOOKUP(colkey,table,MATCH(rowkey,firstrow)+1)
where colkey is the entry you want to look up from the first column of table (the lookup table) and rowkey is the entry you want to look up from firstrow (the first row of the lookup table). Adding 1 to the result of the MATCH() function returns the position of the column that contains the match for one of the two entries you're looking up. Since this formula serves as the third argument of a VLOOKUP() function that looks up the other entry from the first column of the table, the entire function will return the entry that corresponds to the two entries you're looking up.
To demonstrate this technique, let's use it to look up the price of an item that is 0.75 inch wide and 1.25 inches high. To do this, we'll begin by entering the text values Width:, Height:, and Price: in cells A10, A11, and A13, respectively. (However, you can skip this step, if you wish.) Then we'll enter the value .75 in cell B10, enter the value 1.25 in cell B11, and enter the function
=VLOOKUP(B10,B3:H8,MATCH(B11,C2:H2)+1)
in cell B13. Figure D shows the result.
.gif "Cc750573.ime97b4d(en-us,TechNet.10).gif")
Figure D: Since the value in cell B5 matches the value in cell B10, and the value in cell G2 matches the value in cell B11, the function in cell B13 returns the value from cell G5.
As you can see, this function returns the value $11.10 from cell G5. Here's why: Because the entry in cell G2 (the fifth cell of the range specified by the second argument
of the MATCH() function) matches the value specified by the first argument, the MATCH() function returns the value 5. The entry in cell B5 (the third cell in the first column of the range specified by the second argument of the VLOOKUP() function) matches the entry specified by the first argument of that function. For that reason—and because the third argument returns the value *5+1=*6—the VLOOKUP() function returns the value from cell G5: the cell at the intersection of the third row and sixth column of the table.
Figure E shows the result of changing the values in cells B10 and B11 to 1.25 and 0.75, respectively.
.gif "Cc750573.ime97b4e(en-us,TechNet.10).gif")
Figure E: Because the value in cell B7 matches the value in cell B10 and the value in cell E2 matches the value in cell B11, the function in cell B13 returns the value from cell E7.
As you can see, the function in cell B13 now returns the value $8.58 from cell E7. Because the entry in cell E2 (the third cell of the range specified by the second argument of the MATCH() function) matches the value specified by the first argument, the MATCH() function returns the value 3. The entry in cell B7 (the fifth cell in the first column of the range specified by the second argument of the VLOOKUP() function) matches the entry specified by the first argument of that function. For that reason—and because the third argument returns the value *3+1=*4—the VLOOKUP() function returns the value from cell E7: the cell at the intersection of the fifth row and fourth column of the table.
An alternative technique
If you want, you can use a function in the form
=HLOOKUP(rowkey,table,MATCH(colkey,firstcol)+1)
instead of a function in the form
=VLOOKUP(colkey,table,MATCH(rowkey,firstrow)+1)
to look up an entry from a two-dimensional range. In this case, the MATCH() function (which returns the position of the matching entry within the first column of the range) serves as the third argument of an HLOOKUP() function that looks up the matching entry from the first row of the range.
For example, Figure F shows the result of replacing the function in cell B13 of the worksheet shown in Figure E with the function
=HLOOKUP(B11,C2:H8,MATCH(B10,B3:B8)+1)
.gif "Cc750573.ime97b4f(en-us,TechNet.10).gif")
Figure F: The function in cell B13 of this worksheet returns the same result as the function in cell B13 of the worksheet shown in Figure E.
As you can see, this function returns the same result as the function it replaces. Because the entry in cell B7 (the fifth cell of the range specified by the second argument of the MATCH() function) matches the value specified by the first argument of the MATCH() function, that function returns the value 5. The entry in cell E2 (the third cell in the first row of the range specified by the second argument of the VLOOKUP() function) matches the entry specified by the first argument of that function. For that reason—and because the third argument returns the value *5+1=*6—the VLOOKUP() function returns the value from cell E7: the cell at the intersection of the third column and sixth row of the table.
Exact-matching lookups
By default, the VLOOKUP(), HLOOKUP(), and MATCH() functions work on an "entry before the first entry that exceeds the key entry" basis—not by exact match. For that reason, the functions shown in Figures D, E, and F may return nonerror results even when the value in cell B10 doesn't match the values in cells B3:B8 and/or the value in cell B11 doesn't match the values in cells C2:H2. For example, Figure G shows the result of changing the values in cells B10 and B11 of the worksheet shown in Figure F to 0.6 and 0.9, respectively.
.gif "Cc750573.ime97b4g(en-us,TechNet.10).gif")
Figure G: By default, the VLOOKUP(), HLOOKUP(), and MATCH() functions don't work by exact match.
As you can see, the function in cell B13 returns the value from cell E4, even though the value in cell B4 doesn't match the value in cell B10 and the value in cell E2 doesn't match the value in cell B11.
Fortunately, making Excel perform exact-matching two-dimensional lookups is easy: You simply use the logical value FALSE as the optional fourth argument of the VLOOKUP() or HLOOKUP() function, and you use the numeric value 0 as the optional third argument of the MATCH() function. For example, if you want the two-dimensional lookup function in cell B13 of the worksheet shown in Figure G to work by exact match, you'd change it to
=HLOOKUP(B11,C2:H8,MATCH(B10,B3:B8,0)+1,FALSE)
as we've done in Figure H. This function returns the value #N/A because the values in cells B10 and B11 don't match any of the values in cells B3:B8 and C2:H2, respectively.
.gif "Cc750573.ime97b4h(en-us,TechNet.10).gif")
Figure H: To perform an exact-matching two-dimensional lookup, you must use the logical value FALSE as the fourth argument of the VLOOKUP() or HLOOKUP() function, and use the numeric value 0 as the third argument of the MATCH() function.
The article entitled "Performing two-dimensional lookups" was originally published in Inside Microsoft Excel, November 1997. Copyright © 1997, The Cobb Group, 9420 Bunson Parkway, Louisville, KY 40220. All rights reserved. For subscription information, call the Cobb Group at 1-800-223-8720.
We at Microsoft Corporation hope that the information in this work is valuable to you. Your use of the information contained in this work, however, is at your sole risk. All information in this work is provided "as is," without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement , and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Microsoft Corporation. Microsoft Corporation shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.