Index/Match not working

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

Hi!

Match only works on a 1 dimensional array.

$A$3:$O$25000

Try this:

=INDEX(Item,MATCH($D7,'Item 5.17.06'!$A$4:$A$25000,0),MATCH($U7,'Item
5.17.06'!$A$3:$O$3,0))

Biff

"frosterrj" <[email protected]> wrote in message
news:[email protected]...
> 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
>

The range you use to search on the second MATCH will always give you #N/A, as
it using a range with more than one column/row. Can you use one single
column there, like $H$4:$H$25000 or $A$4:$Z4?

Hope this helps,
Miguel.

"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
>

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
>

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 here: 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),MATCH(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),MATCH(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
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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 here:
> 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),MATCH(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),MATCH(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
>> >

the lookup range would look like this: (assume the a,b,c,d is the item and
the 1234... is the zone, the a1,a2,b8, etc are the prices I'm trying to
rerturn in the other sheet by looking up the item and zone combo (the match
portion))

1 2 3 4 5 6 7 8
a a1 a2 a3 a4.... a8
b b1 b2 ............... b8
c c1 c2....... c8

the item#/zone combination is unique - items are only listed once and they
each have 8 zone prices.
so if my sheet says index the range above, match item#a, match zone#4, the
formula should return a4. I keep getting #N/A.

Hope this is a little clearer.

Thanks,
Robert


"Peo Sjoblom" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 here:
> > 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),MATCH(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),MATCH(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
> >> >
>
>
>