Replacing #N/A Or 0 with Blanks in Vlookup?

It seems there should be a shorter formula than this, but this one is all I could come up with...

=IF(IFERROR(VLOOKUP(E2,A$2:B$10,2,FALSE),"")=0,"",IFERROR(VLOOKUP(E2,A$2:B$10,2,FALSE),""))

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

By the way, implementing Biff's idea of using 0 instead of FALSE inside the VLOOKUP function can shorten my formula by 8 characters...

=IF(IFERROR(VLOOKUP(E2,A$2:B$10,2,0),"")=0,"",IFERROR(VLOOKUP(E2,A$2:B$10,2,0),""))

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

For Excel 2000-2003 compatilbility, you should use the ISNA() function instead of the ISERROR().

... where F2 is,

 =IF(ISNA(VLOOKUP(E2,A$2:B$10,2,FALSE)),"",IF(VLOOKUP(E2,A$2:B$10,2,FALSE)=0,"",VLOOKUP(E2,A$2:B$10,2,FALSE)))

• "Have you tried turning it off and then on again?" - Roy from 'The IT Crowd' (Emmy award-winning British comedy)

One more question, i would like to ask Rick regarding his formula below->

=IF(IFERROR(VLOOKUP(E2,A$2:B$10,2,FALSE),"")=0,"",IFERROR(VLOOKUP(E2,A$2:B$10,2,FALSE),""))

Can the IFERROR not be replaced with ISERROR to make it compatible with XL2003?

No, IFERROR is a special construction introduced in XL2007 that makes it so you do not have to repeat your function in order to test it for resulting in an error. If we did not have to convert zeroes into empty strings (that is, if you only asked to blank out the #N/A errors), here the XL2007 formula to do that...

=IFERROR(VLOOKUP(E2,A$2:B$10,2,FALSE),"")

and here is the XL2003 equivalent of it...

=IF(ISERROR(VLOOKUP(E2,A$2:B$10,2,FALSE),"",VLOOKUP(E2,A$2:B$10,2,FALSE)

Notice that in the XL2003 version, the formula is repeated twice... once to test it and again to return its value; the IFERROR construction in XL2007 is much cleaner looking because you do not have to repeat the formula. Unfortunately, to implement the test for 0 ends up requiring us to repeat the function anyway.

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Dear all,

I have the data below, Shipment in Cell A2:A10 and their times in Cell B2:B10. Now, i copied the Shipment in Cell E2:E10 and i am trying to place vlookup in Cell F2, everything goes well but when i delete any shipment from cell A2:A10, values in Cell F2:F10 returns #NA.

How to get rid of this #NA and 0.

Shipment	Times			Shipment
a	1			a	1
	2			b	#N/A
c				c	0
d	4			d	4
e	5			e	5
f				f	0
g	7			g	7
h	8			h	8
j	9			j	9

Formula used in F2 is =VLOOKUP(E2,A:B,2,FALSE)

 I want to replace #N/A with blanks. I guess Iserror function may be used but not sure of using.

Kindly help!

---

Warm regards Dharmesh

Your subject line says to replace NA or 0...

Try this...

=IF(COUNTIF(A$2:A$10,E2),IF(VLOOKUP(E2,A$2:B$10,2,0)="","",VLOOKUP(E2,A$2:B$10,2,0)),"")

So, these will be the results:

--

Biff

Microsoft Excel MVP