# Thread: ISNA Problem (Excel 2002)

1. ## ISNA Problem (Excel 2002)

Hi

This has been explained to me several times but I still can't get it correct

=IF(ISNA(\$AC\$1="Batch 1",VLOOKUP(AA7,Pivotot,3,0)),"",IF(\$AC\$1="Batch 1",VLOOKUP(AA7,Pivotot,3,0)))

Many Thanks

2. ## Re: ISNA Problem (Excel 2002)

Generally speaking, the idea is: you have a part of a formula, let's say
<code>
<big>=F</big>
</code>
but under some circumstances, F could result in #N/A. So you test for that:
<code>
<big>=IF(ISNA(F),"",F)</big>
</code>
which says "if F results in #N/A, return a blank "", else return the result of F".

In your formula, the role of F is played by
<code>
VLOOKUP(AA7,Pivotot,3,0)
</code>
As you should know by now, VLOOKUP will return #N/A if there is no match. To hide #N/A, you use
<code>
IF(ISNA(VLOOKUP(AA7,Pivotot,3,0)),"",VLOOKUP(AA7,P ivotot,3,0))
</code>
However, this "F" is embedded in a larger formula:
<code>
=IF(\$AC\$1="Batch 1",F,"?")
</code>
You haven't really specified what you want to return if \$AC\$1 does NOT equal "Batch 1", let's assume that you want a blank:
<code>
=IF(\$AC\$1="Batch 1",F,"")
</code>
Now substitute the modified version of F that includes the test:
<code>
=IF(\$AC\$1="Batch 1",IF(ISNA(VLOOKUP(AA7,Pivotot,3,0)),"",VLOOKUP(AA 7,Pivotot,3,0)),"")
</code>
This looks complicated, but it can be done by building the formula step by step, instead of trying to write it in one go.

3. ## Re: ISNA Problem (Excel 2002)

Hi Hans

Thanks for explanation, I will endeavor to try and get my head around it.