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

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.

Possible duplicate of [Quick replace #N/A by 0 when vlookup](http://superuser.com/questions/397104/quick-replace-n-a-by-0-when-vlookup) — Raystafarian, May 21 '16 at 19:05

score 9 · Answer 1 · answered May 21 '16 at 18:49

9

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.

answered May 21 '16 at 18:49

T.J.L.

I wonder if this also applies to google spreadsheet (I ahve the same problem) – speeder May 22 '16 at 04:12
2

Note for some older versions of excel (and maybe Google spreadsheets), if `iferror()` isn't available, just do `If(IsError([formula]),"",[formula])` – BruceWayne May 22 '16 at 05:19

score 6 · Answer 2 · edited May 21 '16 at 19:50

6

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

edited May 21 '16 at 19:50

Raystafarian

answered May 21 '16 at 18:48

Jim Nielsen

2 Answers2