Match/Index Returning #N/A

[Will]

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(Index(SizeNames,OR(IF(MATCH(size,Fractional,0),1,0),IF(MATCH(size,Letter,0),2,0),IF(MATCH(size,WireGauge,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

 

[Barb Reinhardt]

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.

 

[John James]

My preferred method for big formulae, giving similar functionality t
Barb's and with an interface I prefer, is to select the evaluat
formula icon on the formula auditing toolbar. This progressivel
toggles through every part of a formula showing what that portio
evaluates to

 

[JMB]

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.

 

[Biff]

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

 

[JMB]

More correctly <g>

=--NOT(ISNA(MATCH(size,Fractional,0)))

OR (save a nesting level)

=--(ISNA(MATCH(size,Fractional,0))<>0)

 

[Biff]

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