How do you replace Vlookup #NA errors with blank or zero?

How do you replace Vlookup #NA errors with blank or zero?
 

Question Info


Last updated May 22, 2019 Views 126,871 Applies to:
Answer
Answer

Use

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

for a blank

=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

for a zero instead of #N/A.

---
Kind regards, HansV
www.eileenslounge.com

57 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer

Try,

=IF(ISNA(VLOOKUP(<value> , <array>, <index>)), "", VLOOKUP(<value> , <array>, <index>))

...or possibly,

=IF(VLOOKUP(<value> , <array>, <index>)=NA(), "", VLOOKUP(<value> , <array>, <index>))


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

38 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.