Thread: Comparison formula - including inherent errors

Hi

I would be most grateful if someone can assist me with the following problem - it's killing me!!

I have two columns that I want to compare - but one of them is the result of a VLOOKUP - so it may actually contain the #N/A error - how can I compare them so that:

if col 1 agrees with col 2, then "yes" is returned
if col 1 does not agree with col 2 but the VLOOKUP in col 2 has NOT returned an error (so it's still a valid comparison) then "NO" is returned
if col 2 shows a VLOOKUP #N/A error then "NO" is returned

or, perhaps more straightforwardly:

Col 1 = ABC, col 2 = ABC then return "YES"
Col 1 = ABC, col 2 = DEF then return "NO"
Col 1 = ABC, col 2 = #N/A then return "NO"

Thanks in advance to whoever cracks this for me...

Many thanks

Steve

Would this do it?

=IF(ISNA(B1),"No",IF(A1=B1,"Yes","No"))

Not unless I've applied it incorrectly, see attached file.

Thanks

You didn't adjust references in formula you copied to match row you are starting in..

in C2 you should have:

=IF(ISNA(B2),"No",IF(A2=B2,"Yes","No"))

then copy down

Thanks - that sorted it

Cheers for your help.