Vlookup and Hlookup
The Excel vlookup and hlookup functions tend to scare a lot of people. Often they show up in job descriptions and requirements: "...knowledge of Excel including vlookup and hlookup..." Really, the vlookup and hlookup functions are not very hard to master.
Let's break down the syntax:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value is the value that you are trying to find in a table
- table_array is the table or range (eg, "A1:D20") in which you are looking for a certain value to match another value. One requirement of vlookup is that the value you are looking up be located in the left-most column of the table/range. In hlookup, the lookup value must be in the top-most column of the table/range.
- col_index_num is the column in the table/range (or the row in hlookup) which contains the value that corresponds to the lookup value.
- [range_lookup] is optional. If it is left out or set to TRUE or 1, then the function will return a value that's the closest match to what you're looking for. Usually, you will want to set this to FALSE or 0, that way it will require an exact match.
vlookup and hlookup Examples
The two tables below are basic examples of vlookup and hlookup. One thing to point out is the difference between setting range_lookup (the fourth parameter in the function) to TRUE or FALSE.
|
A |
B |
C |
| 1 |
vlookup Example 1 |
| 2 |
Basic vlookup |
| 3 |
|
|
|
| 4 |
2009 Tax Brackets |
| 5 |
Gross Income |
Marginal Rate |
Base |
| 6 |
0 |
0.1 |
0 |
| 7 |
8350 |
0.15 |
835 |
| 8 |
33950 |
0.25 |
4675 |
| 9 |
82250 |
0.28 |
16750 |
| 10 |
171550 |
0.33 |
41754 |
| 11 |
372950 |
0.35 |
108216 |
| 12 |
|
|
|
| 13 |
|
|
|
| 14 |
Client |
Bill |
(Correct Formula) |
| 15 |
Gross Income |
46500 |
|
| 16 |
Marginal Rate |
0.25 |
=VLOOKUP(B14,$A$5:$C$10,2,TRUE) |
| 17 |
Base |
4675 |
=VLOOKUP(B14,$A$5:$C$10,3,TRUE) |
| 18 |
|
|
|
| 19 |
Client |
Jack |
(Incorrect Formula) |
| 20 |
Gross Income |
31060 |
|
| 21 |
Marginal Rate |
#N/A |
=VLOOKUP(B19,$A$5:$C$10,2,FALSE) |
| 22 |
Base |
#N/A |
=VLOOKUP(B19,$A$5:$C$10,3,FALSE) |
|
A |
B |
C |
D |
E |
F |
G |
| 1 |
hlookup Example 1 |
| 2 |
Basic hlookup |
| 3 |
|
|
|
|
|
|
|
| 4 |
2009 Tax Brackets |
| 5 |
Gross Income |
0 |
8350 |
33950 |
82250 |
171550 |
372950 |
| 6 |
Marginal Rate |
0.1 |
0.15 |
0.25 |
0.28 |
0.33 |
0.35 |
| 7 |
Base |
0 |
835 |
4675 |
16750 |
41754 |
108216 |
| 8 |
|
|
|
|
|
|
|
| 9 |
|
|
|
|
|
|
|
| 10 |
Client |
Bree |
(Correct Formula) |
| 11 |
Gross Income |
87600 |
|
|
|
|
|
| 12 |
Marginal Rate |
0.28 |
=HLOOKUP(B14,$B$4:$G$6,2,TRUE) |
| 13 |
Base |
16750 |
=HLOOKUP(B14,$B$4:$G$6,3,TRUE) |
| 14 |
|
|
|
|
|
|
|
| 15 |
Client |
Jill |
(Incorrect Formula) |
| 16 |
Gross Income |
24000 |
|
|
|
|
|
| 17 |
Marginal Rate |
#N/A |
=HLOOKUP(B19,$B$4:$G$6,2,FALSE) |
| 18 |
Base |
#N/A |
=HLOOKUP(B19,$B$4:$G$6,3,FALSE) |
Note that the "incorrect" formulas return an error ("#N/A"). This is because the range_lookup parameter was set to false. The reason I called it "incorrect" is because the example is looking up tax rates. We want to find the number in the table to which the lookup number will round down.
The next vlookup example shows numbers stored as text in the table. This can be a problem when you are trying to lookup a number. Even if the number matches the text, Excel will not recognize it as a match since it cannot match different data types (numbers and text). Therefore, we must convert the number to a similar text type in the vlookup function.
|
A |
B |
C |
| 1 |
vlookup Example 2 |
| 2 |
vlookup with Numbers Stored as Text |
| 3 |
|
|
|
| 4 |
ID |
Name |
|
| 5 |
07153 |
Charlie |
|
| 6 |
09858 |
Alan |
|
| 7 |
11634 |
Bobby |
|
| 8 |
15618 |
Denise |
|
| 9 |
86515 |
Earl |
|
| 10 |
|
|
|
| 11 |
|
|
|
| 12 |
ID |
Name |
(Correct Formula) |
| 13 |
9858 |
Alan |
=VLOOKUP(TEXT(A12,"00000"),$A$4:$B$8,2,0) |
| 14 |
10050 |
#N/A |
=VLOOKUP(TEXT(A13,"00000"),$A$4:$B$8,2,0) |
| 15 |
|
|
|
| 16 |
ID |
Name |
(Incorrect Formula) |
| 17 |
9858 |
Alan |
=VLOOKUP(TEXT(A16,"00000"),$A$4:$B$8,2,1) |
| 18 |
10050 |
Alan |
=VLOOKUP(TEXT(A17,"00000"),$A$5:$B$8,2,1) |
Our final example shows how to do a vlookup in Excel when there are multiple items you need to lookup. The vlookup function is not actually built for this, so we must do a work-around.
The first part of the example shows a table with accounts and subaccounts. We want to lookup both at the same time.
|
A |
B |
C |
| 1 |
vlookup Example 3 |
| 2 |
vlookup with Multiple Criteria |
| 3 |
|
|
|
| 4 |
Acct |
Sub Acct |
Balance |
| 5 |
A105 |
S100 |
2546 |
| 6 |
A105 |
S200 |
4535 |
| 7 |
A105 |
S300 |
56754 |
| 8 |
A134 |
S100 |
45 |
| 9 |
A134 |
S200 |
3464 |
To use a vlookup with multiple criteria, we are going to concatenate the two items we are looking up. You can do this with either the CONCATENATE() function or the "&" sign. We used the "&" sign.
In order for this to work, we have to add a column next to the table and also change the lookup value in the VLOOKUP() function.
|
A |
B |
C |
D |
E |
F |
G |
| 1 |
vlookup Example 3 Continued |
| 2 |
vlookup with Multiple Criteria |
| 3 |
|
|
|
|
|
|
|
| 4 |
Combined |
(formula) |
Acct |
Sub Acct |
Balance |
|
|
| 5 |
A105.S100 |
=C4 & "." & D4 |
A105 |
S100 |
2546 |
|
|
| 6 |
A105.S200 |
=C5 & "." & D5 |
A105 |
S200 |
4535 |
|
|
| 7 |
A105.S300 |
=C6 & "." & D6 |
A105 |
S300 |
56754 |
|
|
| 8 |
A134.S100 |
=C7 & "." & D7 |
A134 |
S100 |
45 |
|
|
| 9 |
A134.S200 |
=C8 & "." & D8 |
A134 |
S200 |
3464 |
|
|
| 10 |
|
|
|
|
|
|
|
| 11 |
|
|
|
|
|
|
|
| 12 |
Acct |
Sub |
Balance |
(Correct Formula) |
| 13 |
A105 |
S200 |
4535 |
=VLOOKUP(A13 & "." & B13,$A$5:$E$9,5,0) |
| 14 |
A134 |
S200 |
3464 |
=VLOOKUP(A14 & "." & B14,$A$5:$E$9,5,0) |
| 15 |
|
|
|
|
|
|
|
| 16 |
Alternate method using the SUMPRODUCT() function |
| 17 |
(This is not recommended since SUMPRODUCT() will slow down the sheet substantially) |
| 18 |
A105 |
S200 |
4535 |
=SUMPRODUCT(($C$5:$C$9=A17)*($D$5:$D$9=B17)*($E$5:$E$9)) |
| 19 |
A134 |
S200 |
3464 |
=SUMPRODUCT(($C$5:$C$9=A18)*($D$5:$D$9=B18)*($E$5:$E$9)) |
We also included an example of the SUMPRODUCT() function for those who are interested, but note that it uses up a lot more resources and can slow down calculations quite a bit.
This concludes our relatively brief discussion of Excel's vlookup function.
|
|