4

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.

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.

  • I wonder if this also applies to google spreadsheet (I ahve the same problem) – speeder May 22 '16 at 4: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 5:19
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),"")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.