Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
frosterrj
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Index/Match not working

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" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Miguel Zapico
 
Posts: n/a
Default Index/Match not working

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default Index/Match not working

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
frosterrj
 
Posts: n/a
Default Index/Match not working

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default Index/Match not working

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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
frosterrj
 
Posts: n/a
Default Index/Match not working

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
click & drag not working Teresa Excel Discussion (Misc queries) 2 February 25th 06 02:52 AM
amount of working days per month Nigel Excel Discussion (Misc queries) 2 November 29th 05 11:41 AM
Calculation with Working day of the year Box666 Excel Discussion (Misc queries) 4 November 10th 05 07:33 PM
Working days left in the month compared to previous months qwopzxnm Excel Worksheet Functions 8 October 24th 05 08:00 PM
Working time and days Nortos Excel Worksheet Functions 5 May 6th 05 04:17 PM


All times are GMT +1. The time now is 01:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"