Vlookup returns #N/A correctly...but...

[Linda]

Hi All,

I am using the Vlookup formula to compare two files daily extractions. The
#N/A value is retuned when there is correctly no match found. I want to
create a formula in a seperate column, based on the results of the Vlookup,
that would return a text string based on those results. IF statements work
great except for the #N/A results. I have tried IF(ISNA(cell
ref),"XYZ","ABC") and it still uses #N/A as a result of the IF. Any
suggestions would be greatly appreciated!

 

[Pete_UK]

I've just responded to your other post pointing out how to get rid of
the #N/A error. See what effect that has and then repost here if you
need any further help.

Pete

 

[Max]

See nothing wrong with

.. IF(ISNA(cell ref),"XYZ","ABC")

It should work fine with #N/A errors, eg:
=IF(ISNA(B7),"XYZ","ABC")

Perhaps there's other errors returned?
Try: =IF(ISERROR(B7),"XYZ","ABC")

 

[Linda]

I am getting the results I want...what I needed to change was the Vlookup
formula, not the seperate column formula. This is the statement I used:
=IF(ISNA(VLOOKUP(C2,'Invoice Q'!$C:$AR,3,FALSE)),"NOT in
EDW",VLOOKUP(C2,'Invoice Q'!$C:$AR,3,FALSE))

 

[Pete_UK]

Thanks for feeding back, Linda - glad you got it to work.

Pete

 

[Ron Rosenfeld]

Hi All,

I am using the Vlookup formula to compare two files daily extractions. The
#N/A value is retuned when there is correctly no match found. I want to
create a formula in a seperate column, based on the results of the Vlookup,
that would return a text string based on those results. IF statements work
great except for the #N/A results. I have tried IF(ISNA(cell
ref),"XYZ","ABC") and it still uses #N/A as a result of the IF. Any
suggestions would be greatly appreciated!

IT works fine here. There must be something different about what you are
really doing, and what you posted here.

For example.

B1: =IF(ISNA(A1),"abc","xyz")

A1: #N/A then B1: abc
A1: 0 then B1: xyz
--ron