Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match/Index Returning #N/A
Hi,
Been searching previous post for an answer to my question, but I got nothing. Here's my problem: I have two tables in two worksheets. Fist table consists of sizes, broken down by their different types, i.e. Fractional Letter WireGauge Metric Then I have another table that consists of product categories. The categories are determined by the product type and size, i.e. Fractional Letter WireGauge Metric Product type Cat1 Cat2 Cat3 Cat4 In a third worksheet I have a 32,000 row list of the products and their different sizing. I need to categorize these products. The formula that I thought would do it is =Index(Table,MATCH(product,ProductList,0),MATCH(In dex(SizeNames,OR(IF(MATCH(size,Fractional,0),1,0), IF(MATCH(size,Letter,0),2,0),IF(MATCH(size,WireGau ge,0),3,0),IF(MATCH(size,Metric,0),4,0)),SizeList, 0+1) I thought that by setting the value if false to 0, I would get the value that corresponds to the correct size type, but instead it returns #N/A. I can't think of anything that could fix it. Hope I explained everything well enough. Any help would be appreciated. -Sern |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match/Index Returning #N/A
I've been able to debug my problems by clicking on one portion of the
equation at a time, and then selecting the fx next to the command line. It will show the results of the function you are looking at. You can then drill down into the function to figure out what is wrong. "Will" wrote in message ups.com... Hi, Been searching previous post for an answer to my question, but I got nothing. Here's my problem: I have two tables in two worksheets. Fist table consists of sizes, broken down by their different types, i.e. Fractional Letter WireGauge Metric Then I have another table that consists of product categories. The categories are determined by the product type and size, i.e. Fractional Letter WireGauge Metric Product type Cat1 Cat2 Cat3 Cat4 In a third worksheet I have a 32,000 row list of the products and their different sizing. I need to categorize these products. The formula that I thought would do it is =Index(Table,MATCH(product,ProductList,0),MATCH(In dex(SizeNames,OR(IF(MATCH(size,Fractional,0),1,0), IF(MATCH(size,Letter,0),2,0),IF(MATCH(size,WireGau ge,0),3,0),IF(MATCH(size,Metric,0),4,0)),SizeList, 0+1) I thought that by setting the value if false to 0, I would get the value that corresponds to the correct size type, but instead it returns #N/A. I can't think of anything that could fix it. Hope I explained everything well enough. Any help would be appreciated. -Sern |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match/Index Returning #N/A
My preferred method for big formulae, giving similar functionality to Barb's and with an interface I prefer, is to select the evaluate formula icon on the formula auditing toolbar. This progressively toggles through every part of a formula showing what that portion evaluates to. -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=533029 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match/Index Returning #N/A
The source of #N/A is most likely due to match not finding a value. Match
returns an error if it fails. =IF(MATCH(size,Fractional,0),1,0) will not return a 0 if MATCH fails. =--NOT(ISERROR(MATCH(size,Fractional,0))) OR (save a nesting level) =--(ISERROR(MATCH(size,Fractional,0))<0) will return a 1 if a match is found, 0 if it is not. You could multiply it by 2,3,4 for your subsequent tests. I'm not really following how your data is set up, so there may be a cleaner way of writing your formula. I'm only trying to address the source of your #N/A issue. "Will" wrote: Hi, Been searching previous post for an answer to my question, but I got nothing. Here's my problem: I have two tables in two worksheets. Fist table consists of sizes, broken down by their different types, i.e. Fractional Letter WireGauge Metric Then I have another table that consists of product categories. The categories are determined by the product type and size, i.e. Fractional Letter WireGauge Metric Product type Cat1 Cat2 Cat3 Cat4 In a third worksheet I have a 32,000 row list of the products and their different sizing. I need to categorize these products. The formula that I thought would do it is =Index(Table,MATCH(product,ProductList,0),MATCH(In dex(SizeNames,OR(IF(MATCH(size,Fractional,0),1,0), IF(MATCH(size,Letter,0),2,0),IF(MATCH(size,WireGau ge,0),3,0),IF(MATCH(size,Metric,0),4,0)),SizeList, 0+1) I thought that by setting the value if false to 0, I would get the value that corresponds to the correct size type, but instead it returns #N/A. I can't think of anything that could fix it. Hope I explained everything well enough. Any help would be appreciated. -Sern |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match/Index Returning #N/A
Hi!
Your problem is with the OR( ).......MATCH ( ) functions. I'm assuming that "size" can only be in one of the 4 named ranges. So, one of the Match functions will return a number but the others will return #N/A. I'm not really following your logic so I can't suggest a solution. Biff "Will" wrote in message ups.com... Hi, Been searching previous post for an answer to my question, but I got nothing. Here's my problem: I have two tables in two worksheets. Fist table consists of sizes, broken down by their different types, i.e. Fractional Letter WireGauge Metric Then I have another table that consists of product categories. The categories are determined by the product type and size, i.e. Fractional Letter WireGauge Metric Product type Cat1 Cat2 Cat3 Cat4 In a third worksheet I have a 32,000 row list of the products and their different sizing. I need to categorize these products. The formula that I thought would do it is =Index(Table,MATCH(product,ProductList,0),MATCH(In dex(SizeNames,OR(IF(MATCH(size,Fractional,0),1,0), IF(MATCH(size,Letter,0),2,0),IF(MATCH(size,WireGau ge,0),3,0),IF(MATCH(size,Metric,0),4,0)),SizeList, 0+1) I thought that by setting the value if false to 0, I would get the value that corresponds to the correct size type, but instead it returns #N/A. I can't think of anything that could fix it. Hope I explained everything well enough. Any help would be appreciated. -Sern |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match/Index Returning #N/A
More correctly <g
=--NOT(ISNA(MATCH(size,Fractional,0))) OR (save a nesting level) =--(ISNA(MATCH(size,Fractional,0))<0) "JMB" wrote: The source of #N/A is most likely due to match not finding a value. Match returns an error if it fails. =IF(MATCH(size,Fractional,0),1,0) will not return a 0 if MATCH fails. =--NOT(ISERROR(MATCH(size,Fractional,0))) OR (save a nesting level) =--(ISERROR(MATCH(size,Fractional,0))<0) will return a 1 if a match is found, 0 if it is not. You could multiply it by 2,3,4 for your subsequent tests. I'm not really following how your data is set up, so there may be a cleaner way of writing your formula. I'm only trying to address the source of your #N/A issue. "Will" wrote: Hi, Been searching previous post for an answer to my question, but I got nothing. Here's my problem: I have two tables in two worksheets. Fist table consists of sizes, broken down by their different types, i.e. Fractional Letter WireGauge Metric Then I have another table that consists of product categories. The categories are determined by the product type and size, i.e. Fractional Letter WireGauge Metric Product type Cat1 Cat2 Cat3 Cat4 In a third worksheet I have a 32,000 row list of the products and their different sizing. I need to categorize these products. The formula that I thought would do it is =Index(Table,MATCH(product,ProductList,0),MATCH(In dex(SizeNames,OR(IF(MATCH(size,Fractional,0),1,0), IF(MATCH(size,Letter,0),2,0),IF(MATCH(size,WireGau ge,0),3,0),IF(MATCH(size,Metric,0),4,0)),SizeList, 0+1) I thought that by setting the value if false to 0, I would get the value that corresponds to the correct size type, but instead it returns #N/A. I can't think of anything that could fix it. Hope I explained everything well enough. Any help would be appreciated. -Sern |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match/Index Returning #N/A
I'm not really following your logic so I can't suggest a solution.
Well, I can but..... Replace the OR with MAX and combine the MATCH with an ISNA: MAX(IF(ISNA(MATCH(size,Fractional,0)),0,1), IF(ISNA(MATCH(size,Letter,0)),0,2), IF(ISNA(MATCH(size,WireGauge,0)),0,3), IF(ISNA(MATCH(size,Metric,0)),0,4)) I'm sure there has to be a better way to do this but I'd need to see the file for myself to figure it out. Biff "Biff" wrote in message ... Hi! Your problem is with the OR( ).......MATCH ( ) functions. I'm assuming that "size" can only be in one of the 4 named ranges. So, one of the Match functions will return a number but the others will return #N/A. I'm not really following your logic so I can't suggest a solution. Biff "Will" wrote in message ups.com... Hi, Been searching previous post for an answer to my question, but I got nothing. Here's my problem: I have two tables in two worksheets. Fist table consists of sizes, broken down by their different types, i.e. Fractional Letter WireGauge Metric Then I have another table that consists of product categories. The categories are determined by the product type and size, i.e. Fractional Letter WireGauge Metric Product type Cat1 Cat2 Cat3 Cat4 In a third worksheet I have a 32,000 row list of the products and their different sizing. I need to categorize these products. The formula that I thought would do it is =Index(Table,MATCH(product,ProductList,0),MATCH(In dex(SizeNames,OR(IF(MATCH(size,Fractional,0),1,0), IF(MATCH(size,Letter,0),2,0),IF(MATCH(size,WireGau ge,0),3,0),IF(MATCH(size,Metric,0),4,0)),SizeList, 0+1) I thought that by setting the value if false to 0, I would get the value that corresponds to the correct size type, but instead it returns #N/A. I can't think of anything that could fix it. Hope I explained everything well enough. Any help would be appreciated. -Sern |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning a blank cell rather then #N/A (Lookup) | Excel Discussion (Misc queries) | |||
Returning Forumla For Result | Excel Discussion (Misc queries) | |||
Vlookup returning No data. | Excel Worksheet Functions | |||
MY VLOOKUP IS RETURNING N/A AND THEREFORE MY TOTAL SUM AT THE BOT. | Excel Discussion (Misc queries) | |||
returning a text cell based on a number cell | Excel Worksheet Functions |