software

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 quot;Itemquot; 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,
RobertHi!

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

quot;frosterrjquot; gt; wrote in message
...
gt; THis is driving me crazy! The formula below:
gt; =INDEX(Item,MATCH($D7,'Item 5.17.06'!$A$4:$A$25000,0),MATCH($U7,'Item
gt; 5.17.06'!$A$3:$O$25000,0)) keeps returning #N/A no matter what I do.
gt; Using
gt; array formulas does not help.
gt;
gt; I'm trying to find the intersection of the Item number (cell D) and Zone
gt; price (cell U) zones are in columns H to O in the Item array.
gt;
gt; My quot;Itemquot; named range includes the column headers (where the zone numbers
gt; are). I tried replacin the named range with the actual sheet/column
gt; references, but still doesn't work.
gt;
gt; Any help appreciated, even if there;s a better function combination that
gt; is
gt; not as flaky as the Index/Match combo seems to be.
gt;
gt; Thanks,
gt; Robert
gt;
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.

quot;frosterrjquot; wrote:

gt; THis is driving me crazy! The formula below:
gt; =INDEX(Item,MATCH($D7,'Item 5.17.06'!$A$4:$A$25000,0),MATCH($U7,'Item
gt; 5.17.06'!$A$3:$O$25000,0)) keeps returning #N/A no matter what I do. Using
gt; array formulas does not help.
gt;
gt; I'm trying to find the intersection of the Item number (cell D) and Zone
gt; price (cell U) zones are in columns H to O in the Item array.
gt;
gt; My quot;Itemquot; named range includes the column headers (where the zone numbers
gt; are). I tried replacin the named range with the actual sheet/column
gt; references, but still doesn't work.
gt;
gt; Any help appreciated, even if there;s a better function combination that is
gt; not as flaky as the Index/Match combo seems to be.
gt;
gt; Thanks,
gt; Robert
gt;

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:

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

Peo Sjoblom

quot;frosterrjquot; wrote:

gt; THis is driving me crazy! The formula below:
gt; =INDEX(Item,MATCH($D7,'Item 5.17.06'!$A$4:$A$25000,0),MATCH($U7,'Item
gt; 5.17.06'!$A$3:$O$25000,0)) keeps returning #N/A no matter what I do. Using
gt; array formulas does not help.
gt;
gt; I'm trying to find the intersection of the Item number (cell D) and Zone
gt; price (cell U) zones are in columns H to O in the Item array.
gt;
gt; My quot;Itemquot; named range includes the column headers (where the zone numbers
gt; are). I tried replacin the named range with the actual sheet/column
gt; references, but still doesn't work.
gt;
gt; Any help appreciated, even if there;s a better function combination that is
gt; not as flaky as the Index/Match combo seems to be.
gt;
gt; Thanks,
gt; Robert
gt;

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 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#ItmDescClsCdICDescRepCstActCstMktCst12345678
10COL HEADCHEESE ENDS amp; PIECES90KITCHEN0.80.80.8221.51.51.25111
12COL DRY SALAME ENDS amp; PIECES90KITCHEN0.80.80.8221.51.51.25111

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.

Robertquot;Peo Sjoblomquot; wrote:

gt; What you should do when you build a formula like that is to test each part of
gt; the formula by itself then assemble it. Try each match function and you'll
gt; see that this is incorrect
gt;
gt; MATCH($U7,'Item 5.17.06'!$A$3:$O$25000,0), match need a one column/row
gt; dimension, not a
gt;
gt; 15 X 25000 array, so your formula will never return a match, the whole idea
gt; behind a formula like this is to use index like A3:O25000
gt;
gt; then you match in A3:A25000 to get the first match and then in A2:O22 to get
gt; the second, the index will return the intersection, see:
gt;
gt; www.contextures.com/xlFunctio...ml#IndexMatch2
gt; that the formula you have constructed is flaky, not the functions themselves
gt;
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; quot;frosterrjquot; wrote:
gt;
gt; gt; THis is driving me crazy! The formula below:
gt; gt; =INDEX(Item,MATCH($D7,'Item 5.17.06'!$A$4:$A$25000,0),MATCH($U7,'Item
gt; gt; 5.17.06'!$A$3:$O$25000,0)) keeps returning #N/A no matter what I do. Using
gt; gt; array formulas does not help.
gt; gt;
gt; gt; I'm trying to find the intersection of the Item number (cell D) and Zone
gt; gt; price (cell U) zones are in columns H to O in the Item array.
gt; gt;
gt; gt; My quot;Itemquot; named range includes the column headers (where the zone numbers
gt; gt; are). I tried replacin the named range with the actual sheet/column
gt; gt; references, but still doesn't work.
gt; gt;
gt; gt; Any help appreciated, even if there;s a better function combination that is
gt; gt; not as flaky as the Index/Match combo seems to be.
gt; gt;
gt; gt; Thanks,
gt; gt; Robert
gt; gt;

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 3a
b
c
d
e
fso 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
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;frosterrjquot; gt; wrote in message
...
gt; So how do I get the intersection of item and zone when the item is down
gt; the
gt; left and there are 8 zones across the top?.
gt; Each item is listed only once in the lookup array and each row in the
gt; worksheet I want to put the price (the item/zone intersection) has a
gt; separate
gt; cell for the item and zone numbers. Seems like this is classic
gt; index/match.
gt;
gt; the examples he
gt; www.contextures.com/xlFunctio...ml#IndexMatch2
gt; do exactly what I want. I modified them to read my lookup array, and ran
gt; but i still get the N/A. I created a named range which lists the Itm# to
gt; Zone8 only (ItmIndex)
gt; Like this:
gt; =INDEX(ItmIndex,MATCH(D9,INDEX(ItmIndex,,1),0),MAT CH(U9,INDEX(ItmIndex,1,),0))
gt;
gt; and like this:
gt; INDEX('Chain Special Pricing
gt; 5.17.06'!$H$4:$O$16407,MATCH(D8,$A$4:$A$16407,0),M ATCH(U8,$H$3:$O$3,0))
gt;
gt; Here is the lookup array (few rows) but they get wrapped here (the 1 is
gt; Zone1):
gt; A B C D E
gt; F..............
gt; Itm# ItmDesc ClsCd ICDesc RepCst ActCst MktCst 1 2 3 4 5 6 7 8
gt; 10 COL HEADCHEESE ENDS amp; PIECES 90 KITCHEN 0.8 0.8 0.8 2 2 1.5 1.5 1.25 1
gt; 1 1
gt; 12 COL DRY SALAME ENDS amp; PIECES 90 KITCHEN 0.8 0.8 0.8 2 2 1.5 1.5 1.25 1
gt; 1 1
gt;
gt; So I'm just trying to get the formula to return the intersection of Item
gt; number and Zone # (match column A and row 3). For example match item#10,
gt; zone1 should return .8.
gt;
gt; Robert
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; What you should do when you build a formula like that is to test each
gt;gt; part of
gt;gt; the formula by itself then assemble it. Try each match function and
gt;gt; you'll
gt;gt; see that this is incorrect
gt;gt;
gt;gt; MATCH($U7,'Item 5.17.06'!$A$3:$O$25000,0), match need a one column/row
gt;gt; dimension, not a
gt;gt;
gt;gt; 15 X 25000 array, so your formula will never return a match, the whole
gt;gt; idea
gt;gt; behind a formula like this is to use index like A3:O25000
gt;gt;
gt;gt; then you match in A3:A25000 to get the first match and then in A2:O22 to
gt;gt; get
gt;gt; the second, the index will return the intersection, see:
gt;gt;
gt;gt; www.contextures.com/xlFunctio...ml#IndexMatch2
gt;gt; that the formula you have constructed is flaky, not the functions
gt;gt; themselves
gt;gt;
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; quot;frosterrjquot; wrote:
gt;gt;
gt;gt; gt; THis is driving me crazy! The formula below:
gt;gt; gt; =INDEX(Item,MATCH($D7,'Item 5.17.06'!$A$4:$A$25000,0),MATCH($U7,'Item
gt;gt; gt; 5.17.06'!$A$3:$O$25000,0)) keeps returning #N/A no matter what I do.
gt;gt; gt; Using
gt;gt; gt; array formulas does not help.
gt;gt; gt;
gt;gt; gt; I'm trying to find the intersection of the Item number (cell D) and
gt;gt; gt; Zone
gt;gt; gt; price (cell U) zones are in columns H to O in the Item array.
gt;gt; gt;
gt;gt; gt; My quot;Itemquot; named range includes the column headers (where the zone
gt;gt; gt; numbers
gt;gt; gt; are). I tried replacin the named range with the actual sheet/column
gt;gt; gt; references, but still doesn't work.
gt;gt; gt;
gt;gt; gt; Any help appreciated, even if there;s a better function combination
gt;gt; gt; that is
gt;gt; gt; not as flaky as the Index/Match combo seems to be.
gt;gt; gt;
gt;gt; gt; Thanks,
gt;gt; gt; Robert
gt;gt; gt;
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,
Robertquot;Peo Sjoblomquot; wrote:

gt; I don't understand how you data is setup, you say that one lookup values
gt; would be vertical like in
gt;
gt; column A going down so if c was the values it would return 3
gt;
gt;
gt; a
gt; b
gt; c
gt; d
gt; e
gt; f
gt;
gt;
gt; so if we assume the formula would retrun something from the third row, where
gt; are the values that you want to return the intersection of ?
gt;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Excel 95 - Excel 2007
gt; Northwest Excel Solutions
gt; www.nwexcelsolutions.com
gt; quot;It is a good thing to follow the first law of holes;
gt; if you are in one stop digging.quot; Lord Healey
gt;
gt;
gt; quot;frosterrjquot; gt; wrote in message
gt; ...
gt; gt; So how do I get the intersection of item and zone when the item is down
gt; gt; the
gt; gt; left and there are 8 zones across the top?.
gt; gt; Each item is listed only once in the lookup array and each row in the
gt; gt; worksheet I want to put the price (the item/zone intersection) has a
gt; gt; separate
gt; gt; cell for the item and zone numbers. Seems like this is classic
gt; gt; index/match.
gt; gt;
gt; gt; the examples he
gt; gt; www.contextures.com/xlFunctio...ml#IndexMatch2
gt; gt; do exactly what I want. I modified them to read my lookup array, and ran
gt; gt; but i still get the N/A. I created a named range which lists the Itm# to
gt; gt; Zone8 only (ItmIndex)
gt; gt; Like this:
gt; gt; =INDEX(ItmIndex,MATCH(D9,INDEX(ItmIndex,,1),0),MAT CH(U9,INDEX(ItmIndex,1,),0))
gt; gt;
gt; gt; and like this:
gt; gt; INDEX('Chain Special Pricing
gt; gt; 5.17.06'!$H$4:$O$16407,MATCH(D8,$A$4:$A$16407,0),M ATCH(U8,$H$3:$O$3,0))
gt; gt;
gt; gt; Here is the lookup array (few rows) but they get wrapped here (the 1 is
gt; gt; Zone1):
gt; gt; A B C D E
gt; gt; F..............
gt; gt; Itm# ItmDesc ClsCd ICDesc RepCst ActCst MktCst 1 2 3 4 5 6 7 8
gt; gt; 10 COL HEADCHEESE ENDS amp; PIECES 90 KITCHEN 0.8 0.8 0.8 2 2 1.5 1.5 1.25 1
gt; gt; 1 1
gt; gt; 12 COL DRY SALAME ENDS amp; PIECES 90 KITCHEN 0.8 0.8 0.8 2 2 1.5 1.5 1.25 1
gt; gt; 1 1
gt; gt;
gt; gt; So I'm just trying to get the formula to return the intersection of Item
gt; gt; number and Zone # (match column A and row 3). For example match item#10,
gt; gt; zone1 should return .8.
gt; gt;
gt; gt; Robert
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Peo Sjoblomquot; wrote:
gt; gt;
gt; gt;gt; What you should do when you build a formula like that is to test each
gt; gt;gt; part of
gt; gt;gt; the formula by itself then assemble it. Try each match function and
gt; gt;gt; you'll
gt; gt;gt; see that this is incorrect
gt; gt;gt;
gt; gt;gt; MATCH($U7,'Item 5.17.06'!$A$3:$O$25000,0), match need a one column/row
gt; gt;gt; dimension, not a
gt; gt;gt;
gt; gt;gt; 15 X 25000 array, so your formula will never return a match, the whole
gt; gt;gt; idea
gt; gt;gt; behind a formula like this is to use index like A3:O25000
gt; gt;gt;
gt; gt;gt; then you match in A3:A25000 to get the first match and then in A2:O22 to
gt; gt;gt; get
gt; gt;gt; the second, the index will return the intersection, see:
gt; gt;gt;
gt; gt;gt; www.contextures.com/xlFunctio...ml#IndexMatch2
gt; gt;gt; that the formula you have constructed is flaky, not the functions
gt; gt;gt; themselves
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Regards,
gt; gt;gt;
gt; gt;gt; Peo Sjoblom
gt; gt;gt;
gt; gt;gt; quot;frosterrjquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; THis is driving me crazy! The formula below:
gt; gt;gt; gt; =INDEX(Item,MATCH($D7,'Item 5.17.06'!$A$4:$A$25000,0),MATCH($U7,'Item
gt; gt;gt; gt; 5.17.06'!$A$3:$O$25000,0)) keeps returning #N/A no matter what I do.
gt; gt;gt; gt; Using
gt; gt;gt; gt; array formulas does not help.
gt; gt;gt; gt;
gt; gt;gt; gt; I'm trying to find the intersection of the Item number (cell D) and
gt; gt;gt; gt; Zone
gt; gt;gt; gt; price (cell U) zones are in columns H to O in the Item array.
gt; gt;gt; gt;
gt; gt;gt; gt; My quot;Itemquot; named range includes the column headers (where the zone
gt; gt;gt; gt; numbers
gt; gt;gt; gt; are). I tried replacin the named range with the actual sheet/column
gt; gt;gt; gt; references, but still doesn't work.
gt; gt;gt; gt;
gt; gt;gt; gt; Any help appreciated, even if there;s a better function combination
gt; gt;gt; gt; that is
gt; gt;gt; gt; not as flaky as the Index/Match combo seems to be.
gt; gt;gt; gt;
gt; gt;gt; gt; Thanks,
gt; gt;gt; gt; Robert
gt; gt;gt; gt;
gt;
gt;
gt;