Or Function and #NA

B

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
 
J

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.
 
B

Biff

Now I get a #Value error message.

JLatham said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top