Array lookup

  • Thread starter Thread starter JRichardson
  • Start date Start date
J

JRichardson

I am trying to write a formula that will return a price from an array based
on a product & location. The array that I am want to do the lookup off of
looks like this:

Product 1 Product 2 Product 3
Location 1 $1.00 $2.00 $3.00
Location 2 $1.25 $2.25 $3.25
Location 3 $1.50 $2.50 $3.50

My spreadsheets has a "pulldown" (validation) for the location & then I have
a list of the products. I need a lookup/index that will pull the right cost
if the location & product intersect.

I have tried match & index and I think I must be doing something wrong as I
keep getting #N/A's.

Hope this makes sense. Thanks! j
 
An index/match should do the job ..
Assume your source table as posted is in A1:D4
Assume the DV for the location is in G1
and in F2 down are the products, eg:
Location 2
Product 1
Product 2
Product 3

Put in G2:
=INDEX($B$2:$D$4,MATCH(G$1,$A$2:$A$4,0),MATCH($F3,$B$1:$D$1,0))
Copy down to extract the required prices, viz:

Location 2
Product 1 1.25
Product 2 2.25
Product 3 3.25

Modify to suit. Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Back
Top