Subscribe Us

Enter your email address:

Loading

Lookup Function - Part II

Labels: ,

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
Lower
Limit
Grade
2
0
F
3
50
E
4
60
D
5
70
C
6
80
B
7
90
A
  

C
D
E
F
1
Student
Score
Grade
Formula as displayed using GetFormula() macro
2
Abe
73
C
=VLOOKUP(D2,v.grades!$A$2:$B$7,2,TRUE)
3
Ada
95
A
=VLOOKUP(D3,v.grades!$A$2:$B$7,2,TRUE)
4
Alan
80
B
=VLOOKUP(D4,v.grades!$A$2:$B$7,2,TRUE)
5
Alicia
65
D
=VLOOKUP(D5,v.grades!$A$2:$B$7,2,TRUE)
6
Amos
90
A
=VLOOKUP(D6,v.grades!$A$2:$B$7,2,TRUE)
7
Ann
85
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)


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
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
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 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
    (also always check See Also and Examples in help articles)
    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
   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


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

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.


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.

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 








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




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 

'



0 comments:

Post a Comment

Post a Comment