How to change and error message to text

[NEWB]

I am using VLOOKUP to compare two lists of items for different years. the
formula I am usign for that is =VLOOKUP(A20,'2005
information'!$A$4:$L$97,12,FALSE).

Now some of the clients in 2005 are not in 2004 and vice versa, so therefore
a #N/A error is returned, because there is no number to look up. I want to
change this #N/A to tect, like "No Respective Client". I have tried using
the ERROR.TYPE funtion, but this gives me a circle reference, and gets me no
where, because I want it to either return a number like VLOOKUP does, or the
Text I want. is this possible to do and if so HOW?

 

[Vito]

Change the formula to:


=IF(ISNA(VLOOKUP(A20,'2005
information'!$A$4:$L$97,12,FALSE)),"No Respective
Client",=VLOOKUP(A20,'2005
information'!$A$4:$L$97,12,FALSE))

 

[Duke Carey]

try

=if(isna(VLOOKUP(A20,'2005 information'!$A$4:$L$97,12,FALSE)),"No
Client",VLOOKUP(A20,'2005 information'!$A$4:$L$97,12,FALSE))

 

[Eddie O]

Use the IF formula in combination with ISNA, or more generally, ISERROR.
ISNA returns TRUE if there is a #N/A error.
For a result in cell A20, for example, formula would be:
=IF(ISNA(VLOOKUP(A20,'2005 information'!$A$4:$L$97,12,FALSE)),"No Respective
Client",VLOOKUP(A20,'2005 information'!$A$4:$L$97,12,FALSE))

 

[NEWB]

Thanks that worked

try

=if(isna(VLOOKUP(A20,'2005 information'!$A$4:$L$97,12,FALSE)),"No
Client",VLOOKUP(A20,'2005 information'!$A$4:$L$97,12,FALSE))