Excel vlookup reference purposely missing shows #N/A – stop it

Posted on

QUESTION :

I have a cell which does a vlookup.

But the table to which it refers is always changing and when the specific value is there is shows fine.

But when the value isn’t there it shows #N/A – how can I get it to stop this and just display nothing?

Example: =VLOOKUP($P5,GW30!$CI:$CL,2,FALSE) and P5 = Arsenal

So when Arsenal play at home I get a value and it’s ok. But when they play away they are listed in a different column and I get a #N/A

I need to stop it showing #N/A please.

ANSWER :

You want to use the IFERROR function:

=IFERROR(VLOOKUP($P5,GW30!$CI:$CL,2,FALSE),"")

If there’s no error, it will return the value as normal. If there is, it will return what’s after the comma, in this case an empty string.

You can wrap your formula with the iferror condition and set the default value for the error condition to be blank e.g.

iferror(VLOOKUP($P5,GW30!$CI:$CL,2,FALSE),"")

Leave a Reply

Your email address will not be published. Required fields are marked *