# VLOOKUP Worksheet Function

Location:   http://www.mvps.org/dmcritchie/excel/vlookup.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)    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)     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). ``` =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)

 A B 1 LowerLimit Grade 2 0 F 3 50 E 4 60 D 5 70 C 6 80 B 7 90 A

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

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)

 A B C D 1 Acct# Description Balance Formula as displayed using GetFormula() 2 848-001 Travel & Entertainment 12,456.01 =VLOOKUP(A2,Trial_Balance!A1:B3,2,FALSE) 3 848-003 T&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))

 A B 1 Acct# Balance 2 848-001 12456.01 3 848-002 5463.01 4 848-003 16543.01

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

 A B C 1 Stock Number Description Price 2 Stock#002 Widgets - Single .35 3 Stock#003 Widgets - 2 per pack .50 4 Stock#004 Widgets - 3 per pack .60 5 Stock#005 Widgets - 6 per pack 1.00 6 Stock#006 Widgets - 12 per pack 1.70 7 Stock#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.

 A B C D E 1 Catalog# Count Description UnitPrice Invoice Amount 2 Stock#002 3 Widgets - Single .35 1.05 3 Stock#004 4 Widgets - 3 per pack .60 2.40 4 Stock#007 2 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 Price for 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
=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)

Thomas Ogilvy <twogilvy...> 1999-07-15 wrote in message news:eSrD3mZz#GA.304@cppssbbsa04...
 A B C D E F 1 Firstname Lastname Phone Location Service Code Short Name 2 Charles W. Behr 800-234-1212 Sales 01 Chuck 3 George MacDuff 541-5555 Warehouse 02 MacDuff 4 Angus MacPherson 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
fill-handle

 A B C 1 Firstname Lastname Short Name 2 Charles W. Behr Chuck 3 George MacDuff MacDuff 4 Angus MacPherson Angus

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

 A B C D E F G H I J K 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.

```   =VLOOKUP(H3,etax,3,TRUE)+VLOOKUP(H3,etax,5,TRUE)*(H3-VLOOKUP(H3,etax,6,TRUE))
```
is equivalent to the following
```   =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))
```
HELP --> Index --> VLOOKUP Worksheet Function

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

## 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.

 A B C D E 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

```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

 A B C D 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
 A B 1 LastnameN LastnameO 2 burnsN BurnsO 3 JohnsN JohnsO 4 SmithN SmithO
Table SHO - Oldname/Newname
 A B 1 LastnameO LastnameN 2 burnsO BurnsN 3 JohnsO JohnsN 4 SmithO SmithN

Table SHR - Oldname/Residence

 A B C D 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.
```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
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)

 A B C D E 1 Book Id Location Date In Date Out 2 23 1 02/12/2001 04/22/2001 3 45 5 04/17/2001 04/19/2001 Location Mismatch 4 57 6 03/18/2000 03/19/2000 5 59 7 05/12/2001

 A B C D 1 Book Id Location Date Purchased 2 23 1 02/12/2000 3 45 2 04/17/2001 Location Mismatch 4 56 5 04/20/2000 Missing ID 56 5 57 6 03/18/1999 6 59 7 05/12/2000

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)

 A B C D E F G H I 1 Plant-Name Prod. Name Jan Feb March April 2 Plant-A Prod A 5 12 8 22 Given: 3 Plant-A Prod B 6 15 15 18 Plant Plant-A 4 Plant-A Prod C 8 66 42 45 Prod. Prod B 5 Plant-B Prod A 2 45 12 16 Month March 6 Plant-B Prod B 4 22 6 45 7 Plant-B Prod C 6 15 2 52 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
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)

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)
```   =if(countif(ListNumbers!A:A,B1)>0,B1,"")
```

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

 A B C D E 1 Color Name Tint Formula Cost 2 8422 Hampestead B1 C4 F1 \$12.25 3 8427 Wishing Star M2 T8 \$ 6.35 4 8433 Ashford C7 F12 S8 \$ 8.75 5 8449 Modernist I6 S5 T4 \$ 9.25 6 8460 Bridal Wreath AXN1 D12 E6 \$ 9.45 7 Most Expensive: \$12.25 8422 Hampestead 8 9 CELL Formula 10 C7 =MAX(C2:C6) 11 D7 =OFFSET(A1,MATCH(C7,C2:C6,0),0) 12 13 you can rewrite formulas as 14 C7 =MAX(C\$2:OFFSET(C7,-1,0)) 15 D7 =OFFSET(\$A\$1,MATCH(C7,C\$2:OFFSET(C7,-1,0),0),0)

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

 A B C D E F G H 1 color\model Model1 Model2 Model3 Model4 Arguments 2 Brown 5 4 3 2 model color 3 Red 0 2 8 9 Model2 blue 4 Green 10 15 17 20 5 Blue 8 9 10 11 6 Rainbow 1 8 40 80 7 8 4 =MATCH(H3,A2:A6,0) 9 2 =MATCH(G3,B1:E1,0) 10 9 =INDEX(A1:E6,(MATCH(H3,A2:A6,0)+1),(MATCH(G3,B1:E1,0)+1)) 11 9 =INDEX(A1:E6,5,3)    Row=5, col=3 equivalent to above 12 9 =INDEX(A1:E6,MATCH("Blue",A1:A6,0),MATCH("Model2",A1:E1,0))     equivalent to above

## 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)

Problem:
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.
=INDEX(C2:C10,MATCH(1,(A2:A10=G1)*(B2:B10=H1),0))

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

=INDEX(C2:C10,MATCH(1,(A2:A10=3)*(B2:B10=15),0))

would return 4

 A B C D E 1 Cat1 SubCat1 Data1 Data2 Data3 2 1 15 1.2 4.5 6 3 1 18 2.2 6 8.2 4 1 22 3.1 7.1 9 5 2 15 3 5 6 6 2 18 3.1 5.1 6.1 7 2 22 3.2 5.4 6.2 8 3 15 4 4 4 9 3 18 5 5 5 10 3 22 6 6 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:

=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

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