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

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" <stanuyan@gmail.com> wrote in message
news:1145043765.923689.53380@j33g2000cwa.googlegroups.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(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
>

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.

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

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

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" <stanuyan@gmail.com> wrote in message
news:1145043765.923689.53380@j33g2000cwa.googlegroups.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(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
>

>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" <biffinpitt@comcast.net> wrote in message
news:ulWG10DYGHA.5100@TK2MSFTNGP03.phx.gbl...
> 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" <stanuyan@gmail.com> wrote in message
> news:1145043765.923689.53380@j33g2000cwa.googlegroups.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(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
>>
>
>