I don't understand how you data is setup, you say that one lookup values
would be vertical like in

column A going down so if c was the values it would return 3


a
b
c
d
e
f


so if we assume the formula would retrun something from the third row, where
are the values that you want to return the intersection of ?



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"frosterrj" wrote in message
...
So how do I get the intersection of item and zone when the item is down
the
left and there are 8 zones across the top?.
Each item is listed only once in the lookup array and each row in the
worksheet I want to put the price (the item/zone intersection) has a
separate
cell for the item and zone numbers. Seems like this is classic
index/match.

the examples he
http://www.contextures.com/xlFunctio...ml#IndexMatch2
do exactly what I want. I modified them to read my lookup array, and ran
but i still get the N/A. I created a named range which lists the Itm# to
Zone8 only (ItmIndex)
Like this:
=INDEX(ItmIndex,MATCH(D9,INDEX(ItmIndex,,1),0),MAT CH(U9,INDEX(ItmIndex,1,),0))

and like this:
INDEX('Chain Special Pricing
5.17.06'!$H$4:$O$16407,MATCH(D8,$A$4:$A$16407,0),M ATCH(U8,$H$3:$O$3,0))

Here is the lookup array (few rows) but they get wrapped here (the 1 is
Zone1):
A B C D E
F..............
Itm# ItmDesc ClsCd ICDesc RepCst ActCst MktCst 1 2 3 4 5 6 7 8
10 COL HEADCHEESE ENDS & PIECES 90 KITCHEN 0.8 0.8 0.8 2 2 1.5 1.5 1.25 1
1 1
12 COL DRY SALAME ENDS & PIECES 90 KITCHEN 0.8 0.8 0.8 2 2 1.5 1.5 1.25 1
1 1

So I'm just trying to get the formula to return the intersection of Item
number and Zone # (match column A and row 3). For example match item#10,
zone1 should return .8.

Robert








"Peo Sjoblom" wrote:

What you should do when you build a formula like that is to test each
part of
the formula by itself then assemble it. Try each match function and
you'll
see that this is incorrect

MATCH($U7,'Item 5.17.06'!$A$3:$O$25000,0), match need a one column/row
dimension, not a

15 X 25000 array, so your formula will never return a match, the whole
idea
behind a formula like this is to use index like A3:O25000

then you match in A3:A25000 to get the first match and then in A2:O22 to
get
the second, the index will return the intersection, see:

http://www.contextures.com/xlFunctio...ml#IndexMatch2
that the formula you have constructed is flaky, not the functions
themselves


Regards,

Peo Sjoblom

"frosterrj" wrote:

THis is driving me crazy! The formula below:
=INDEX(Item,MATCH($D7,'Item 5.17.06'!$A$4:$A$25000,0),MATCH($U7,'Item
5.17.06'!$A$3:$O$25000,0)) keeps returning #N/A no matter what I do.
Using
array formulas does not help.

I'm trying to find the intersection of the Item number (cell D) and
Zone
price (cell U) zones are in columns H to O in the Item array.

My "Item" named range includes the column headers (where the zone
numbers
are). I tried replacin the named range with the actual sheet/column
references, but still doesn't work.

Any help appreciated, even if there;s a better function combination
that is
not as flaky as the Index/Match combo seems to be.

Thanks,
Robert