VLOOKUP Worksheet Function

Introductory Remarks   (#intro)

        =VLOOKUP(TEXT(A1,"00000"),Table,2,FALSE)
     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)

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)

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

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.

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.

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
    =if(iserror(B2*D2),0,b2*d2)

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
    =ISERROR(vlookup(a2,catalog,2,false),"",vlookup(a2,catalog,2,false))
    =ISERROR(vlookup(a2,catalog,3,false),"",vlookup(a2,catalog,3,false))
    =if(iserror(B2*D2),0,b2*d2)
and as before format the price so that a zero does not show.
    i.e.   #,###.00_);(#,###.00);;

Additional Information on Second Sheet (#Phone2ws)

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

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.

   =VLOOKUP(H3,etax,3,TRUE)+VLOOKUP(H3,etax,5,TRUE)*(H3-VLOOKUP(H3,etax,6,TRUE))

   =VLOOKUP(H3,$A$3:$F$19,3,TRUE)+VLOOKUP(H3,$A$3:$F$19,5,TRUE)*
         (H3-VLOOKUP(H3,$A$3:$F$19,6,TRUE))

    syntax:
        VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

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

VLOOKUP -- Tables Newname/Oldname,  Oldname/Residence

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)

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 part with the INDEX/MATCH is based on a posting by:  Leonard E. Meads
   Re: Need Help Linking Spreadsheets
    http://groups.google.com/groups?oi=djq&ic=1&selm=an_521824369

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

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)

former $B2       VLOOKUP($A2,SHN!$A$1:$B$20,2,FALSE)

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 SHR - Oldname/Residence

Using VLOOKUP Worksheet Function in VBA   (#vba)

Application.WorksheetFunction.VLookup(Range("C4"),Range("A1:B10"),2,False)

You have to use VBA ranges as arguments 1 and 2

Sub Tester5()
Range("E18").Select
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
Else
   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)

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)

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
Array-enter:
    =INDEX(data,MATCH(A10&A11,A3:A8&B3:B8,0),MATCH(A12,2:2,0))
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.

=VLOOKUP(LEFT($A2,LEN($A2)-FIND(",",$A2)-1),Cities,2,FALSE)

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
=VLOOKUP(MAX(B2:B14),B2:C14,2,FALSE)

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)

   =if(countif(ListNumbers!A:A,B1)>0,B1,"")

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

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

Match without Comment, loose code (#snipets)

• =OFFSET(C14,MATCH(D5,{"L","P","S","M"},0),0)

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

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: =INDEX('M4'!H2:H13,MATCH(S2,'M4'!G2:G13,-1))   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))

G H  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

G H  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)

	A	B
1	IBM	go there
2
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)

HLOOKUP,  INDEX,  LOOKUP,  MATCH,  CHOOSE 

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 Sites (#related)

• Excel -- Worksheet Functions -- VLookup, Contextures.com « , Debra Dalgleish, better explanation of parameters.  Debra also has an Example used in Excel -- Create an Order Form
• Look up on two fields with this VBA alternative to VLOOKUP «, Andy Wiggins, uses the worksheet functions index and match internally, Byg Software    =VLOOKUP2(lookup_value1,lookup_value2,table_array,col_index_num)
  Based on Q59482 -- XL: Using INDEX and MATCH Against Multiple Criteria

Related Articles (#articles)

• Re: Vlookup or MatchIndex Niek Otten (2000-07-02), provides a nice summary of MATCH, VLOOKUP, and INDEX in terms of efficiency be sure to look at thread.
• Looking Up Data In Tables, Chip Pearson, formulas to handle looking up data in lists that VLOOKUP can’t handle.
• Speeding up VLOOKUP in a large table when False is used as an argument, a reply post by Harlan Grove.
• Make VLOOKUP provide the next highest value (value above) if not an exact match, a reply post by Harlan Grove, and a reply by J.E.McGimpsey.  Message-ID replacements (“#”, “%23”), (“$”, “%24”), (“%”, “%25”)
• OFFSET Worksheet Function

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

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