Array lookup

[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

 

[Dave Peterson]

Look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions03.html
Especially example 2

 

[Max]

An index/match should do the job ..
Assume your source table as posted is in A14
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