Or Function and #NA

[Biff]

I am using an OR function with two different VLookup function within each of
the Or logical statements. I know that one of the two Or logical arguments
will not return any data, but the other will. Because the one returns an
#NA, the whole formula returns #NA.

Here is the formula.

=OR(VLOOKUP(B3,'C:\Documents and Settings\alex\Desktop\[Model Summary -
Tamarac.xls]Model Level Reference
Codes'!$A$2:$D$291,4,FALSE),VLOOKUP(B3,'C:\Documents and
Settings\alex\Desktop\[Model Summary - Tamarac.xls]Model Level Reference
Codes'!$J$29:$K$38,2,FALSE))

Any help,
Biff

 

[JLatham]

I believe I'd use a nested IF statement here, although that may not give the
results you want because if the first VLOOKUP() returns anything, then the
second one wouldn't be performed.

In any case, to prevent the N/A error, wrap up each VLOOKUP() like this

IF(ISNA(VLOOKUP(B3,'C:\Documents and Settings\alex\Desktop\[Model Summary -
Tamarac.xls]Model Level Reference
Codes'!$A$2:$D$291,4,FALSE),"",VLOOKUP(B3,'C:\Documents and
Settings\alex\Desktop\[Model Summary - Tamarac.xls]Model Level Reference
Codes'!$A$2:$D$291,4,FALSE))

that will cause an empty string to be returned when an #N/A would have been
returned without the wrap.

 

[Biff]

Now I get a #Value error message.

I believe I'd use a nested IF statement here, although that may not give the
results you want because if the first VLOOKUP() returns anything, then the
second one wouldn't be performed.

In any case, to prevent the N/A error, wrap up each VLOOKUP() like this

IF(ISNA(VLOOKUP(B3,'C:\Documents and Settings\alex\Desktop\[Model Summary -
Tamarac.xls]Model Level Reference
Codes'!$A$2:$D$291,4,FALSE),"",VLOOKUP(B3,'C:\Documents and
Settings\alex\Desktop\[Model Summary - Tamarac.xls]Model Level Reference
Codes'!$A$2:$D$291,4,FALSE))

that will cause an empty string to be returned when an #N/A would have been
returned without the wrap.

I am using an OR function with two different VLookup function within each
of
the Or logical statements. I know that one of the two Or logical
arguments
will not return any data, but the other will. Because the one returns an
#NA, the whole formula returns #NA.

Here is the formula.

=OR(VLOOKUP(B3,'C:\Documents and Settings\alex\Desktop\[Model Summary -
Tamarac.xls]Model Level Reference
Codes'!$A$2:$D$291,4,FALSE),VLOOKUP(B3,'C:\Documents and
Settings\alex\Desktop\[Model Summary - Tamarac.xls]Model Level Reference
Codes'!$J$29:$K$38,2,FALSE))

Any help,
Biff