VLOOKUP Worksheet Function

Location:   http://www.mvps.org/dmcritchie/excel/vlookup.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

Introductory Remarks   (#intro)

VLOOKUP searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

Syntax:    (As always look in HELP for more information)
    range_lookup can be TRUE or FALSE, if omitted the default is TRUE

VLOOKUP will work with a list where the table arguments are sorted, and you will get the closest match to a table argument that does not exceed your lookup value.
(for sorted lists use TRUE or default for a *close* match)

VLOOKUP will work with a list where the arguments are unordered, and you either get an exact match or fail with #N/A!.
(Whether sorted or not when an *exact* match is required so is the use of FALSE)

To suppress N/A errors:
=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not found",VLOOKUP(...,...,...,False))

A cause of #VALUE! error is a zero value for col_index_num value in the function.

Do not mix cells defined as numbers with cells defined as text in the argument column of your table.  Some tips on determining data type and actual content of your data.  Your table must be consistent, but your lookup value can be forced to look like the table by using one or the other of these tricks (Peo Sjoblom 3003-01-15).

     or =VLOOKUP(A1+0,Table,2,FALSE)

For an example of using VLOOKUP with a HYPERLINK Worksheet Function see my sheets.htm page.

This is the simplest example that I can come up with.  Note the use of TRUE in the formulas indicating that the value found in the table does not have to be an exact match but must be less than or equal to the lookup_value used.  For VLOOKUP the first column of the range is the used to match the argument, the 2 used in the example indicates to return the second column of the table.  Since TRUE is used an exact match is not required, but because an exact match is not equired, the table must be in ordered in ascending order to obtain the correct result.  If VLOOKUP can’t find lookup_value, and range_lookup is TRUE, it uses the largest table argument value that is less than or equal to lookup_value.

Simple Grade Example -- Range of Values -- uses TRUE   (#gradebook)

1 Lower
20 F
350 E
460 D
570 C
680 B
790 A
1StudentScore GradeFormula as displayed using GetFormula() macro
2Abe73 C =VLOOKUP(D2,v.grades!$A$2:$B$7,2,TRUE)
3Ada95 A =VLOOKUP(D3,v.grades!$A$2:$B$7,2,TRUE)
4Alan80 B =VLOOKUP(D4,v.grades!$A$2:$B$7,2,TRUE)
5Alicia65 D =VLOOKUP(D5,v.grades!$A$2:$B$7,2,TRUE)
6Amos90 A =VLOOKUP(D6,v.grades!$A$2:$B$7,2,TRUE)
7Ann85 B =VLOOKUP(D7,v.grades!$A$2:$B$7,2,TRUE)
When TRUE is used the table must be ordered in ascending sequence.

The table above is on another sheet and the boundaries are explicity supplied.  This is for understanding, but frequently the table is that of a named range and the formula might actually look like:  =VLOOKUP(D2,vgrades,2,TRUE)

The table could be written into the formula, but that wouldn’t be real helpful, as any changes in the table would require changing all formulas:
    E2: = IF(D2="","",VLOOKUP(D2,{0,"F";0.5,"E";0.6,"D";0.7,"C";0.8,"B";0.9,"A"},2))
    E2: = LOOKUP(D2*100,{0,50,60,70,80,90},{"F","E","D","C","B","A"})
    E2: = LOOKUP(D2, {0, 50, 60, 70, 80, 90}, {"F", "E", "D", "C", "B", "A"})

By making the range v.grades!$A$2:$B$7 a named range, one could simplify use of the formula and even expand the table with additional Rows,
  i.e. A+, A, A-, B+, B, B-, C+, C, C-, etc. A named range would apply to the entire workbook.

The named range is typically created through the name box, which is to the left of the formula bar by selecting cells in the table
i.e.  $A$2:$B$7  and then typing the name of the range in the name box, i.e. v.grades

Named ranges can be created or removed with menu: insert, name, define
    name:    vgrades
    refers to:  =v.grades!$A$2:$B$7

Named Ranges are known and available to the entire workbook, you cannot name a range with the same name as a cell (i.e. AE1) nor the name of a worksheet.

Second Example -- this one requires an Exact Match -- uses FALSE   (#ex2)

1Acct#Description BalanceFormula as displayed using GetFormula()
2848-001Travel & Entertainment 12,456.01 =VLOOKUP(A2,Trial_Balance!A1:B3,2,FALSE)
3848-003T&E Meals 16,543.01 =VLOOKUP(A3,Trial_Balance!A2:B4,2,FALSE)
4  Total T&E  28,999.02 =SUM(C2:OFFSET(C4,-1,0))

Invoice Example with Exact Matches -- using FALSE   (#catalog)

1Stock Number  DescriptionPrice
2Stock#002 Widgets - Single .35
3Stock#003 Widgets - 2 per pack .50
4Stock#004 Widgets - 3 per pack .60
5Stock#005 Widgets - 6 per pack 1.00
6Stock#006 Widgets - 12 per pack  1.70
7Stock#007 Widgets - 12 doz 3.50

The cells $A$2:$C$1000 is a named table
    insert --> names --> name: catalog, range: $A$2:$C$1000

A simple invoice might be generated as follows by entering the Stock# and the number of itmes.  VLOOKUP would be used to fill in the information from the catalog and the Invoice Amount will be calculated.

1Catalog#Count Description Unit
2Stock#0023 Widgets - Single .35 1.05
3Stock#0044 Widgets - 3 per pack .60 2.40
4Stock#0072 Widgets - 12 doz 3.50 7.00

The formulas used in the above are below.  v.catalog!$A$2:$c$1000 could have been used inplace of the defined name catalog.  Two of the formulas show the table instead of the defined name.  Note the formulas use False to require an Exact match in the table.  With False the table arguments need not be in numerical order.

formula for Description Formula for Unit Pricefor Amt
=VLOOKUP(A2,catalog,2,FALSE)=VLOOKUP(A2,catalog,3,FALSE) =B2*D2
=VLOOKUP(A3,v.Catalog!$A$2:$D$1000,2,FALSE) =VLOOKUP(A3,catalog,3,FALSE)=B3*D3
=VLOOKUP(A4,v.Catalog!$A$2:$D$1000,2,FALSE) =VLOOKUP(A4,catalog,3,FALSE)=B4*D4
When False is used an exact match is required and the table does not need to be in order.

The above formulas show usage that has values for all usages.  Suppose you want to fill-out the formulas ahead of time.  You can enter a single quote into each text field of the catalog and a 0 into the numeric price field, and then perhaps format the calculated invoice line price with zero not showing.
    i.e.   #,###.00_);(#,###.00);;
and use a formula for the price as

Rather than entering a single quote into the catalog table entries, you could use ISERROR in a formula such as these formulas that can be copied down with the fill-handle
and as before format the price so that a zero does not show.
    i.e.   #,###.00_);(#,###.00);;

Additional Information on Second Sheet (#Phone2ws)

Thomas Ogilvy <twogilvy...> 1999-07-15 wrote in message news:eSrD3mZz#GA.304@cppssbbsa04...
1Firstname LastnamePhoneLocation Service Code Short Name
2Charles W.Behr 800-234-1212Sales01 Chuck
3GeorgeMacDuff 541-5555Warehouse02 MacDuff
4AngusMacPherson 546-5555 05 Angus
5       #N/A
Formula in Cell F2 is =VLOOKUP(B2,'v.phone#2'!$B$2:$C$201,2,FALSE)
Drag down the formula in F2 with the

1Firstname LastnameShort Name
2Charles W. Behr Chuck
3George MacDuff MacDuff
4Angus MacPherson Angus

The following is just an example the US Estate Tax Table changes every year (#estatetax)

1 Amount Subject to Tax  Federal Estate & Gift Tax          
2 Over        But Not Over  Flat Amount   Rate x Excess Over   TEST Tax TEST Tax
3 0 $10,000 - + 18% 0   100 18 0 -
4 10,000 20,000 1,800 + 20% 10,000   10,100 1,820 10,000 1,800
5 20,000 40,000 3,800 + 22% 20,000   20,100 3,822 20,000 3,800
6 40,000 60,000 8,200 + 24% 40,000   40,100 8,224 40,000 8,200
7 60,000 80,000 13,000 + 26% 60,000   60,100 13,026 60,000 13,000
8 80,000 100,000 18,200 + 28% 80,000   80,100 18,228 80,000 18,200
9 100,000 150,000 23,800 + 30% 100,000   100,100 23,830 100,000 23,800
10 150,000 250,000 38,800 + 32% 150,000   150,100 38,832 150,000 38,800
11 250,000 500,000 70,800 + 34% 250,000   250,100 70,834 250,000 70,800
12 500,000 750,000 155,800 + 37% 500,000   500,100 155,837 500,000 155,800
13 750,000 1,000,000 248,300 + 39% 750,000   750,100 248,339 750,000 248,300
14 1,000,000 1,250,000 345,800 + 41% 1,000,000   1,000,100 345,841 1,000,000 345,800
15 1,250,000 1,500,000 448,300 + 43% 1,250,000   1,250,100 448,343 1,250,000 448,300
16 1,500,000 2,000,000 555,800 + 45% 1,500,000   1,500,100 555,845 1,500,000 555,800
17 2,000,000 2,500,000 780,800 + 49% 2,000,000   2,000,100 780,849 2,000,000 780,800
18 2,500,000 3,000,000 1,025,800 + 53% 2,500,000   2,500,100 1,025,853 2,500,000 1,025,800
19 3,000,000 Over 3 million 1,290,800 + 55% 3,000,000   3,000,100 1,290,855 3,000,000 1,290,800

For the ETAX table on another sheet select the cells A3:F19 then type ETAX into the Name Box at the far left of the formula bar. 

The assigned name ETAX will apply to all sheets in the workbook.  Note Column B is not used in the following formula.  Column A is the first column in the table and is the basis for the lookup.

is equivalent to the following
HELP --> Index --> VLOOKUP Worksheet Function
    (also always check See Also and Examples in help articles)


Look up Old Name, and Old Residence (#residence)

This is the resulting table.  The New Lastname is filled in and table SHN or SHO will be used to find the Old Lastname, and table SHR will be used to lookup the Old Residence.

1 LastnameN LastnameO Residence Town ST
2 burnsN BurnsO 121 Burns Road Burnett CA
3 JohnsN JohnsO 31 Johnson Ave One Horse WY
4 SmithN SmithO 1441 Smith Hwy Big Pond OH
5 WagnerN #N/A #N/A #N/A #N/A

VLOOKUP -- Tables Newname/Oldname,  Oldname/Residence

The formulas used for a normal VLOOKUP, the oldname can be found with VLOOKUP because the New Name is on left of the Old Name in the SHN (Newname/Oldname) table.  Finding the Residence from the Oldname/Residence table is straightforward.
B2      =VLOOKUP($A2,SHN!$A$1:$B$20,2,FALSE)
C2      =VLOOKUP($B2,SHR!$A$1:$D$20,2,FALSE)
D2      =VLOOKUP($B2,SHR!$A$1:$D$20,3,FALSE)
E2      =VLOOKUP($B2,SHR!$A$1:$D$20,4,FALSE)
VLOOKUP requires that the search argument be in the left-most column of the indicated table, and that the data that is returned is in a column to the right in that table.  You can use INDEX and MATCH to get around this limitation in order to use your existing tables.

Another reason to use Index/Match reported by Nigel Thomas is to produce an error if the data is bad.

INDEX/MATCH -- Tables Oldname/Newname,  Oldname/Residence

This one cannot use VLOOKUP because the conversion table for Old Name to Newname from the SHO (Oldname/Newname) table has the Oldname on left of newname so is unsuitable for a VLOOKUP.  Finding the Residence from the Oldname/Residence table is straightforward.

This part with the INDEX/MATCH is based on a posting by:  Leonard E. Meads
   Re: Need Help Linking Spreadsheets

B2      =INDEX(SHO!$A$1:$A$10,MATCH(A2,SHO!$B$1:$B$20,0))
C2      =VLOOKUP($B2,SHR!$A$1:$D$20,2,FALSE)
D2      =VLOOKUP($B2,SHR!$A$1:$D$20,3,FALSE)
E2      =VLOOKUP($B2,SHR!$A$1:$D$20,4,FALSE)

What the New Table Might Really Look Like, without Oldname

1 LastnameN Residence Town ST
2 burnsN 121 Burns Road Burnett CA
3 JohnsN 31 Johnson Ave One Horse WY
4 SmithN 1441 Smith Hwy Big Pond OH
5 WagnerN #N/A #N/A #N/A

We probably would not show the oldname, so by substituting the Oldname i.e. $B2 we'll change the previous formulas with:

former $B2       INDEX(SHO!$A$1:$A$20,MATCH(A2,SHO!$B$1:$B$20,0))

B2 =VLOOKUP(INDEX(SHO!$A$1:$A$20,MATCH(A2,SHO!$B$1:$B$20,0)),SHR!$A$1:$D$20,2,FALSE) C2 =VLOOKUP(INDEX(SHO!$A$1:$A$20,MATCH(A2,SHO!$B$1:$B$20,0)),SHR!$A$1:$D$20,3,FALSE) D2 =VLOOKUP(INDEX(SHO!$A$1:$A$20,MATCH(A2,SHO!$B$1:$B$20,0)),SHR!$A$1:$D$20,4,FALSE)

If the table SHN were used the substitution in blue above would be
former $B2       VLOOKUP($A2,SHN!$A$1:$B$20,2,FALSE)
These formulas could be simplied by substituting tables:  
SHOA     SHO!$A$1:$A$20
SHOB     SHO!$B$1:$B$20
SHR      SHR!$A$1:$D$20
SHN      SHN!$A$1:$B$20

Tables (#tables)

Table SHN - Newname/Oldname
1 LastnameN LastnameO
2 burnsN BurnsO
3 JohnsN JohnsO
4 SmithN SmithO
   Table SHO - Oldname/Newname
1 LastnameO LastnameN
2 burnsO BurnsN
3 JohnsO JohnsN
4 SmithO SmithN

Table SHR - Oldname/Residence

1 LastnameO Residence Town ST
2 BurnsO 121 Burns Road Burnett CA
3 JohnsO 31 Johnson Ave One Horse WY
4 SmithO 1441 Smith Hwy Big Pond OH

Using VLOOKUP Worksheet Function in VBA   (#vba)

Examples from Peter Beach and Tom Ogilvy 2000-05-05.

You have to use VBA ranges as arguments 1 and 2

Sub Tester5()
Set rng = Range(ActiveCell.Offset(-2, -4), _
   ActiveCell.Offset(13, -3))
Results = Application.VLookup(Range("C4").Value, _
    rng, 2, False)
If Not IsError(Results) Then
    MsgBox Range("C4").Value & " was found, results: " _
      & Results
   MsgBox "Not found"
End If
End Sub

This worked fine for me in Excel 97, SR2, US English
lookup range was A16:B31 for an active cell of E18.  These just represent an
approach.  Set the ranges you need.

Looking for incorrect/missing items between two tables (#missing)

An interesting solution provided to Tom Ogilvy, misc, 2001-05-09 (Tallying of Datas)
Coloring added for illustrative purposes.

1Book IdLocation Date InDate Out 
223 102/12/2001 04/22/2001 
345 504/17/2001 04/19/2001Location Mismatch
457 603/18/2000 03/19/2000 
559 705/12/2001   

1Book IdLocation Date Purchased 
345 204/17/2001 Location Mismatch
456 504/20/2000 Missing ID 56

Formula in D2 of Sheet M.Set1:  (use fill-handle to fill down)
=IF(ISERROR(VLOOKUP(A2,M.Set2!$A$2:$C$200,2,FALSE)),"Missing ID " & A2,IF(VLOOKUP(A2,M.Set2!$A$2:$C$200,2,FALSE)<> B2,"Location Mismatch",""))

Formula in E2 of Sheet M.Set2:  (same as previous formula just for a different sheet)
=IF(ISERROR(VLOOKUP(A2,M.Set1!$A$2:$C$200,2,FALSE)),"Missing ID " &A2,IF(VLOOKUP(A2,M.Set1!$A$2:$C$200,2,FALSE)<>B2,"Location Mismatch",""))

Three Way Lookup (#threeway)

1Plant-Name Prod. Name  Jan Feb    March   April    
2Plant-A    Prod A       512822 Given:  
3Plant-A    Prod B       6151518  PlantPlant-A
4Plant-A    Prod C       8664245 Prod. Prod B
5Plant-B    Prod A       2451216 Month March
6Plant-B    Prod B       422645    
7Plant-B    Prod C       615252  Find value(see formula)
Bob Ulmas, solved this problem 2001-05-30 in worksheet.functions as follows:

If “Plant Name” is in cell A2, then this formula will work
Where A10 contains variable plant (Plant A) and A11 contains variable
product (Prod B) and A12 contains the variable month (March)
array-enter is Ctrl/shift/enter

Partial string lookups   (#partialstring)

The following is a reply from Debra Dalgleish in response to looking up part of the cell. For example, if the cell contains “Dallas, Texas” it is to pick up the city of Dallas without regard to the comma and the remainder after the comma.
See use of the FindFirstChar macro (code) described in posting (misc, 2002-10-31).

Find an associated value for the Maximum value of a Column   (#max)

Find the associated text value in Column C for the maximum numeric value in a Column B range.
=VLOOKUP(MAX(B:B),B:C,2,FALSE)     -- Tomas Kraus, 2002-05-07, worksheet.functions

Return Multiple values from VLOOKUP (#array)

Alan Beban has a webpage on array formulas.  He posted an interesting use of array formulas in conjunction with VLOOKUP Worksheet Function (2004-11-27, programming).

=VLOOKUP(3,A1:G8,{2,5,6}) -- array entered (ctrl+shift+enter) into a 3-cell row will return the values from Columns B,E & F that correspond to the value of 3 in Column A.

=VLOOKUP(3,A1:G8,{2;5;6}) -- will return them to a 3-cell column.

Neat things that don’t need VLOOKUP   (#neatstuff)

Have a list of client phone numbers in another sheet, this formula will repeat the phone number used in column B if it is found in the client phone number list.  (Tom Ogilvy, worksheet.functions, 2000-12-29)

MATCH max value with description of the first matched found (#Match)

1Color Name Tint FormulaCost   
2 8422 HampesteadB1 C4 F1 $12.25   
38427 Wishing StarM2 T8 $ 6.35    
48433 AshfordC7 F12 S8 $ 8.75    
58449 ModernistI6 S5 T4 $ 9.25    
68460 Bridal WreathAXN1 D12 E6 $ 9.45    
7  Most Expensive: $12.25 8422 Hampestead  
10C7 =MAX(C2:C6)     
11D7 =OFFSET(A1,MATCH(C7,C2:C6,0),0)  
13you can rewrite formulas as      
14C7 =MAX(C$2:OFFSET(C7,-1,0))     
15D7 =OFFSET($A$1,MATCH(C7,C$2:OFFSET(C7,-1,0),0),0)  

Index / Match, Another Example (arguments in Row 1, and Column A)

1color\model Model1Model2Model3 Model4  Arguments
2Brown5 432  modelcolor
3Red0 289  Model2 blue
4Green10 151720    
5Blue8 910 11   
6Rainbow1 84080    
8 =MATCH(H3,A2:A6,0)
9 =MATCH(G3,B1:E1,0)
10 =INDEX(A1:E6,(MATCH(H3,A2:A6,0)+1),(MATCH(G3,B1:E1,0)+1))
11 =INDEX(A1:E6,5,3)    Row=5, col=3 equivalent to above
12 =INDEX(A1:E6,MATCH("Blue",A1:A6,0),MATCH("Model2",A1:E1,0))     equivalent to above

Match without Comment, loose code (#snipets)

Double Lookup arguments in Columns A and B (#double)

Once I use the value that would be in col A, I then need to use another value to match col B, then return the value from one of cols C, D or E.

I call that a double lookup.

Solution: by Peo Sjoblom, 2002-10-30 in misc.

array entered with ctrl + shift & enter, assuming we are retrieving from column C, where G1 holds one lookup and H1 another.. Or hardcoded assume we want 3 from A and 15 from B


would return 4

1Cat1SubCat1 Data1Data2Data3
21 151.24.5 6
31 182.26 8.2
41 223.17.1 9
52 1535 6
62 183.15.1 6.1
72 223.25.4 6.2
8 3 15 4 4 4
93 1855 5
103 2266 6

Higher Value if not Equal (#higher)

For arguments without an exact match that fall between between table argument stubs use value from next higher argument's stub.
The Run Time in S2 is 13:15
The formula =VLOOKUP(S2,'M4'!G2:H13,2) returns 60 as the lowest number, what is wanted is the upper limit of 65 if there was not an exact match.

The solution by Niek Otten (2007-08-28)
Sort your data in Sheet 'M4' descending (on column G) and use this formula:


A solution based on the original table as modified from a posting by David Peterson (2005-10-20) would have been:
=INDEX('M4'!H2:H13,IF(ISNUMBER (MATCH(s2,'M4'!G2:G13,0)), MATCH(s2,'M4'!G2:G13,0),1 +MATCH(s2,'M4'!G2:G13,1)))

The reason the sheetname resembles a cell name is that it stands for "Male" runner category 4 (30-34 years) as determined by =LOOKUP(G2, {17,20,25,30,35,40,45,50,55,60,65}, {"1","2","3","4","5","6","7","8","9","10","11"})

  A second question was asked to select the correct table based on a cell with the classification and Niek modified his formula to
=INDEX(INDIRECT(S3&"!H2:H13"), MATCH(S2,INDIRECT(S3&"!G2:G13"),-1))

 1 Run Points
 2 9:20   100 
 3 9:45   95 
 4 10:00   90 
 5 10:30   85 
 6 11:00   80 
 7 11:15   75 
 8 12:00   70 
 9 13:00   65 
10 13:45   60 
11 14:00   55 
12 14:15   50 
13 14:30   45 
 1 Run Points
 2 14:30   45 
 3 14:15   50 
 4 14:00   55 
 5 13:45   60 
 6 13:00   65 
 7 12:00   70 
 8 11:15   75 
 9 11:00   80 
10 10:30   85 
11 10:00   90 
12 9:45   95 
13 9:20   100 

Using MATCH with HYPERLINK to go to stock in table (#stock)

 1 IBM  go there
 3 CE  2 
 4 DELL  4 
 5 IBM  3 
 6 MRK  3 
 7 WLMT  4 
From Worksheets in VBA Coding and in Worksheet Formulas (sheets.htm) on another page.

To find the stock symbol in cell A1 in table in $A$3:$A:$200

Formula in cell B1 is
=HYPERLINK("#"&ADDRESS(MATCH(A1,$A$3:$A$200,0)+2,1), "go there" )

Table $A$3:$A:$200 starts starts two cells down from the formula in cell A1, the table need not go down to the specified row 200.  Add 2 to to row in the table to equate to the row in the sheet.
Stock symbols in Column A starting down from A3
Match is looking for an exact match but is not case sensitive

Similar Worksheet Functions (#functions)


Selection (#selection)

Picking out items from a table for the initial entries before using VLOOKUP might be from a filtered list, or from a validation list.  See Excel -- Data Validation and specifically Combo box at contextures.com -- Also of interest is a video presentation on “Data Validation in Excel” at DataPig but it is becoming a membership site.

Related Articles (#articles)

This page was introduced on February 5, 2000. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved