Vlookup and Hlookup

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.

Home
General Finance
Mergers / Acquisitions
Money Market
Options
Real Estate
Retirement
Other Personal Finance
Opinions / Essays
Excel
Excel Shortcuts
sumif function
vlookup and hlookup
Mandelbrot Set
Links






Contact Us

Site Map

Disclaimer