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 leftmost column of the table/range. In hlookup, the lookup value must be in the topmost 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 workaround.
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.

