Array lookup

JRichardson · External Since: Nov 03, 2009 Posts: 1

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 · External Since: Jul 08, 2005 Posts: 16049

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

JRichardson wrote:
>
> 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

Max · External Since: Mar 17, 2004 Posts: 1751

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
---
"JRichardson" wrote:
> 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
>