Excel vlookup reference purposely missing shows #N/A

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.

user3087904

Posted 2016-05-21T18:41:39.683

Reputation: 41

Possible duplicate of Quick replace #N/A by 0 when vlookup

– Raystafarian – 2016-05-21T19:05:14.143

Answers

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.

T.J.L.

Posted 2016-05-21T18:41:39.683

Reputation: 711

I wonder if this also applies to google spreadsheet (I ahve the same problem) – speeder – 2016-05-22T04:12:05.790

2Note for some older versions of excel (and maybe Google spreadsheets), if iferror() isn't available, just do If(IsError([formula]),"",[formula]) – BruceWayne – 2016-05-22T05:19:53.060

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),"")

Jim Nielsen

Posted 2016-05-21T18:41:39.683

Reputation: 445

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

Answers