Lookup Function  Part II
Posted by
Sokhom
Labels:
Functions,
Macro and VBA
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 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.VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) range_lookup can be TRUE or FALSE, if omitted the default is TRUE (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 30030115). =VLOOKUP(TEXT(A1,"00000"),Table,2,FALSE) or =VLOOKUP(A1+0,Table,2,FALSE) 
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 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 i.e. $A$2:$B$7 and then typing the name of the range in the name box, i.e. v.grades
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)
 
A  B  
1  Acct#  Balance 
2  848001  12456.01 
3  848002  5463.01 
4  848003  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  B  C  D  E  
1  Catalog#  Count  Description  Unit Price  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 
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 fillout 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 fillhandle
=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 19990715 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  8002341212  Sales  01  Chuck 
3  George  MacDuff  5415555  Warehouse  02  MacDuff 
4  Angus  MacPherson  5465555  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 fillhandle
Drag down the formula in F2 with the fillhandle
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 
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)*(H3VLOOKUP(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)*
(H3VLOOKUP(H3,$A$3:$F$19,6,TRUE))
HELP > Index > VLOOKUP Worksheet Function
(also always check See Also and Examples in help articles)
syntax: (also always check See Also and Examples in help articles)
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 leftmost 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. MeadsRe: 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
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 
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
 Table SHO  Oldname/Newname

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 
Looking for incorrect/missing items between two tables (#missing)
An interesting solution provided to Tom Ogilvy, misc, 20010509 (Tallying of Datas)
Coloring added for illustrative purposes.
Coloring added for illustrative purposes.
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 
=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  PlantName  Prod. Name  Jan  Feb  March  April  
2  PlantA  Prod A  5  12  8  22  Given:  
3  PlantA  Prod B  6  15  15  18  Plant  PlantA  
4  PlantA  Prod C  8  66  42  45  Prod.  Prod B  
5  PlantB  Prod A  2  45  12  16  Month  March  
6  PlantB  Prod B  4  22  6  45  
7  PlantB  Prod C  6  15  2  52  Find value  (see formula) 
Bob Ulmas, solved this problem 20010530 in worksheet.functions as follows:
If “Plant Name” is in cell A2, then this formula will workArrayenter:
=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)
arrayenter 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, 20021031).
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, 20020507, worksheet.functions
=VLOOKUP(MAX(B2:B14),B2:C14,2,FALSE)
=VLOOKUP(MAX(B:B),B:C,2,FALSE)  Tomas Kraus, 20020507, 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 (20041127, programming).
=VLOOKUP(3,A1:G8,{2,5,6})
 array entered (ctrl+shift+enter) into a 3cell 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 3cell 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, 20001229)
=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: I call that a double lookup. 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. Solution: by Peo Sjoblom, 20021030 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 (20070828) 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 (20051020) 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 (3034 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)) 
Using MATCH with HYPERLINK to go to stock in table (#stock)
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 A  B  
1  IBM  go there 
2  
3  CE  2 
4  DELL  4 
5  IBM  3 
6  MRK  3 
7  WLMT  4 
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
Subscribe to:
Post Comments (Atom)
Post a Comment