backup value when VLOOKUP gives #N/A

  • Thread starter Thread starter Tom Ossieur
  • Start date Start date
T

Tom Ossieur

Hello,

I used the VLOOKUP function to look for names in a certain
range. However, when the name is not found within the
specific range, the result is "#N/A".
Instead, I would like to have a default value as a result.

(So when a name is not found, I would like to get as a
result the contents of cell D10 e.g., or "not found",...)

I tried as follows [suppose: AAA to represent the original
formula VLOOKUP(A1,B2:D10,3,0)]:

=IF(ERROR.TYPE(AAA/7)=1,"not found",AAA)

However this solves the problem in case of #N/A, but ruins
in all other cases.

Who can help me with this problem?

Many thanks in advance!

Tom
 
Thanks very much, it works indeed...
-----Original Message-----
Hi
try
=IF(ISNA(VLOOKUP(A1,B2:D10,3,0)),"not found",VLOOKUP (A1,B2:D10,3,0))

--
Regards
Frank Kabel
Frankfurt, Germany

Tom said:
Hello,

I used the VLOOKUP function to look for names in a certain
range. However, when the name is not found within the
specific range, the result is "#N/A".
Instead, I would like to have a default value as a result.

(So when a name is not found, I would like to get as a
result the contents of cell D10 e.g., or "not found",...)

I tried as follows [suppose: AAA to represent the original
formula VLOOKUP(A1,B2:D10,3,0)]:

=IF(ERROR.TYPE(AAA/7)=1,"not found",AAA)

However this solves the problem in case of #N/A, but ruins
in all other cases.

Who can help me with this problem?

Many thanks in advance!

Tom
Click to expand...
.
Click to expand...
 
Back
Top