Getting #N/A on a nested multi IF(ISNA statement

Hi everyone,

I have the following formula:

Please Login or Register  to view this content.

This formula is copied down about 20 times and the only item that changes is the "-03" number (goes from 1 - 20). For some reasons, I'm getting a #N/A on random rows. So In this case, I get a #N/A for "-03" but on none of the other numbers. In fact, this is the only one that doesn't become blank if the figure does not appear in the data sheet. Any ideas what I'm doing wrong?

Thanks guys!

You need to reverse the vlookups in the "Value if False" parts of each if..
Perhaps some color coding will help.

=IF(ISNA(VLOOKUP($A$16&"-03",'Data Month 1'!W:W,1,0)),IF(ISNA(VLOOKUP($A$16&"-03",'Data Month 2'!W:W,1,0)),IF(ISNA(VLOOKUP($A$16&"-03",'Data Month 3'!W:W,1,0)),IF(ISNA(VLOOKUP($A$16&"-03",'Data Month 4'!W:W,1,0)),"",VLOOKUP($A$16&"-03",'Data Month 1'!W:W,1,0)),VLOOKUP($A$16&"-03",'Data Month 2'!W:W,1,0)),VLOOKUP($A$16&"-03",'Data Month 3'!W:W,1,0)),VLOOKUP($A$16&"-03",'Data Month 4'!W:W,1,0))

The RED Vlookup is the "Value if False" for the RED IF(ISNA
The BLUE Vlookup is the "Value if False" for the BLUE IF(ISNA
The GREEN Vlookup is the "Value if False" for the GREEN IF(ISNA
The ORANGE Vlookup is the "Value if False" for the ORANGE IF(ISNA

So say the first 3 vlookups are NA
But the 4th one is NOT NA, it then proceeds to execute the RED Vlookup (which is different from the one that was tested by the Red IF(ISNA)

Hope that helps.

Hi Jonmo,

Thanks for the reply. I'm sorry I don't quite understand what you're saying. What do you mean by reverse the "value if false" parts? ALso, I'm not sure I understand your colour coding considering the contents of each vlookup. Could you clarify?

Thanks again for your reply

Actually I think I might have worked it out. thanks Jonmo

The basic IF has 3 parts
Expression
ValueIfTrue
ValueIfFalse
=IF(Expression,ValueIfTrue,ValueIfFalse)

When you NEST if's, a 2nd IF takes the place of either the ValueIfTrue or the ValueIfFalse part
You have them in the ValueIfTrue parts..

But each IF still has it's own ValueIfTrue AND ValueIfFalse parts.
And they go like this
=IF(Expression1,IF(Expression2,valueif2istrue,valueif2isfalse),valueif1isfalse)
The 2nd IF was put in the place of the ValueIfTrue for The first IF
Notice how the ValueIFFalse parts Descend 2 to 1 from left to right.
The RED valueif1isfalse executes if the RED Expression1 is FALSE
The BLUE valueif2isfalse executes if the BLUE Expression2 is FALSE

Glad to help.

Now after taking a closer look, using a 1 column Vlookup
VLOOKUP($A$16&"-03",'Data Month 1'!W:W,1,0)

That just returns the same value that is being looked up (if it's there).

So essentially you're just verifying the value exists in one of the 4 sheets, right?
This might be a simpler method

=IF(OR(COUNTIF('Data Month 1'!W:W,$A$16&"-03"),COUNTIF('Data Month 2'!W:W,$A$16&"-03"),COUNTIF('Data Month 3'!W:W,$A$16&"-03"),COUNTIF('Data Month 4'!W:W,$A$16&"-03")),$A$16&"-03","")