How to change and error message to text
 

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?

How to change and error message to text
 

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))


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489967

How to change and error message to text
 

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))

"NEWB" wrote:

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?

How to change and error message to text
 

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" wrote:

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?

How to change and error message to text
 

Thanks that worked

"Duke Carey" wrote:

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))

"NEWB" wrote:

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?