Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
click & drag not working | Excel Discussion (Misc queries) | |||
amount of working days per month | Excel Discussion (Misc queries) | |||
Calculation with Working day of the year | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Working time and days | Excel Worksheet Functions |