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

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

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

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

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

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

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