Array lookup

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
 
M

Max

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top