lookups and match

L

Lisa

Hi. I'm trying to do a lookup in a table; however, I'm stuck. I tried doing a
match and index, but I can't get it to work.

IE... For Product A, Vendor 2 would charge ____? And so forth.

Vendor
Product 1 2 3
A $5 $7 $9
B $8 $7 $6

Thanks so much,
Lisa
 
T

Toppers

=INDEX($B$2:$D$3,MATCH(F1,$A$2:$A$3,0),MATCH(F2,$B$1:$D$1,0))

where F1 = Product
F2 = Vendor
$B$1:$D$1 are the vendors
$B$2:$D$3 are your prices
$A$2:$A$3 are the Products

HTH
 
C

Chip Pearson

Lisa,

Assuming that your product names are in column A starting in row
2, and vendor numbers are in row 1, use a formula like

=OFFSET(A1,MATCH("a",A2:A10,0),MATCH(2,B1:D1,0))

This will look up product "a" and vendor 2.

See the Double Lookups section at
www.cpearson.com/excel/lookups.htm for more details on various
techniques to look up data in tables.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
L

Lisa

You're the best. Thanks so much!

Toppers said:
=INDEX($B$2:$D$3,MATCH(F1,$A$2:$A$3,0),MATCH(F2,$B$1:$D$1,0))

where F1 = Product
F2 = Vendor
$B$1:$D$1 are the vendors
$B$2:$D$3 are your prices
$A$2:$A$3 are the Products

HTH
Click to expand...
 
A

Alan Beban

Lisa said:
Hi. I'm trying to do a lookup in a table; however, I'm stuck. I tried doing a
match and index, but I can't get it to work.

IE... For Product A, Vendor 2 would charge ____? And so forth.

Vendor
Product 1 2 3
A $5 $7 $9
B $8 $7 $6

Thanks so much,
Lisa
Click to expand...
Excel's Intersection Operator (a space) is designed for this purpose.

Highlight your table and click Insert|Name|Create and check Top row,
Left column.

Then, e.g., =ProductA Vendor2 will return the corresponding price

Note that the product names and vendor names need to be in a form
acceptable for range names in a worksheet.

Alan Beban
 
Top