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?

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

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?

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?

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?