Vlookup results #na and 0, how to replace with text

[samyscraps01]

Hi,

I created a vlookup(B2,"Report'!$A$1:$D$20,16,0) and these are the results I get in Column B. Except that I would like to replace my #NA's with text. So something like, NA=Employee not found and 0=Intentionally Left Blank. However, I don't want it to affect my vlookup results that return approved. Those should remain untouched. I can't seem to figure out a way to do this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Mike[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]Joe [/TD]
[TD]#NA[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

maybe use =IFERROR(IF(VLOOKUP(...)=0, "your text",VLOOKUP(...)),"Your another text")

 

[nemmi69]

Try

=IF(vlookup(B2,"Report'!$A$1:$D$20,16,0)=0,"0=Intentionally Left Blank",IF(vlookup(B2,"Report'!$A$1:$D$20,16,0)="#NA","NA=Employee",vlookup(B2,"Report'!$A$1:$D$20,16,0)))

 

[samyscraps01]

Thank you for the response. @sandy666 and Nemmi69.

 

[sandy666]

you are welcome

Have a nice day

 

[nemmi69]

Your welcome