Ok. It did work.
But now I got read of the #N/A and instead got #VALUE!.
How do I get rid of #VALUE!?


Thanks

wrote:
I normally get the #NA Error from the VLookup Function. Use the "IF"
and "ISNA" functions to replace the error with some usable text. The
formula below will search Column A and return the value in the second
column if it finds a match for the value in cell C1 or the text "Not
Found" if there is no match.

=IF(ISNA(VLOOKUP(C2,$A$2:$B$6,2,FALSE)),"Not Found",
VLOOKUP(C2,$A$2:$B$6,2,FALSE))

The logical test of the IF statement is looking for the #NA error from
the VLOOKUP formula. If if returns the #NA error, the IF formula is
true and the true part is processed. If the error is not found, the
false part of the IF statement is processed, which just happens to be
the very same VLOOKUP formula.

The easiest I have found to make this work is to get the VLOOKUP
function working first then add the IF and ISNA formulas. That way I
know I do not have an issue the VLOOKUP formula.




amirstal wrote:
Is there a way to make all the #N/As in a spreadsheet disappear?