57

I have a Google Sheet (example) with a basic vlookup to create a summable column. It returns "#N/A" for every search key not found, and attaches the following error to those cells:

Error Did not find value 'me@me.com' in VLOOKUP evaluation.

After much searching the only solution I found was to wrap the vlookup in an IF(ISNA()), given in How to link various Google spreadsheets using IMPORTRANGEs that contain VLOOKUP formulas without getting #N/A returned?. This works, but it really seems like I should not have to do this. Is there another way?

Community
  • 1
  • 1
Skipwave
  • 603
  • 1
  • 5
  • 7

4 Answers4

65

Update 2019-03-01: The best solution is now =IFNA(VLOOKUP(…), 0). See this other answer.

 

You can use the following formula. It will replace any #N/A value possibly returned by VLOOKUP(…) with 0.

=SUMIF(VLOOKUP(…),"<>#N/A")

How it works: This uses SUMIF() with only one value VLOOKUP(…) to sum up. So the result is that one value, but only if unequal to #N/A as per the condition argument. If the value equals #N/A however, the sum is zero. That's just how SUMIF() works: if no values match the conditions, it returns 0, not NULL, not #N/A.

Advantages:

  • Compared to the solution =IF(ISNA(VLOOKUP(…)),"",VLOOKUP(…)) referenced in the question, this solution contains the VLOOKUP(…) part only once. This makes the formula shorter and simpler, and avoids the mistakes that happen when editing only one of the two VLOOKUP(…) parts.

  • Compared to the solution =IFERROR(VLOOKUP(…)) from the other answer, errors are not suppressed as that would make detecting and debugging them more difficult. Only #N/A values are suppressed.

tanius
  • 14,003
  • 3
  • 51
  • 63
  • 4
    This solution only works well with number fields. If you want to pass text through, use `IFERROR()` as per the other answer. – Anson Kao Jan 12 '19 at 20:37
56

=IFNA(VLOOKUP(...), "")

Not sure if this has changed recently, but the IFNA implementation supports a single listing of the VLOOKUP now. That is, you don't have to wrap it in another IF.

An advantage there is that you could choose "", 0, NULL, etc. as the value to show on failure.

adamc
  • 800
  • 6
  • 9
  • Hmm I get "Wrong number of arguments to ISNA. Expected 1 arguments, but got 2 arguments." when testing in Google Sheets. And only MS Excel >2013 seems to have `IFNA(…)`. This really works in Google Sheets for you? – tanius Feb 27 '19 at 18:30
  • 1
    Thanks for the comment — fixed the typo above as this really is `IFNA`. I made [this sheet](https://docs.google.com/spreadsheets/d/1FnV6J1lyih06OY9TyBW7oQIYTOpZMts8R2kycsMhoNY/edit?usp=sharing) to prove it to myself. It really does work, but it won't list `IFNA` in the function list or suggest it as you type. Maybe this is in limited release? – adamc Mar 01 '19 at 16:28
  • Indeed, it works! Great find, this is the best solution now. (Just hadn't seen `IFNA` listed in the Google Sheets manual and assumed you were talking about Excel maybe.) – tanius Mar 02 '19 at 16:54
  • 1
    This is amazing!! I've been hoping/waiting for this to simplify stacks of formulas! I can confirm it works! – Camden S. Feb 08 '20 at 23:57
  • 1
    fwiw - the send argument of `""` is optional to the `IFNA` function, and also might be optional for when one just wants an empty string returned if there was an error. – rsteier Dec 03 '21 at 20:16
38

A simpler way to suppress error messages - of any kind - is to use the iferror wrapper:

=iferror(vlookup(A1,Lookup!A:B,2,FALSE))

I don't think there can be an easier way than that. By design, vlookup should not simply return blank if the key wasn't found: this would be indistinguishable from the situation where the key was found but the corresponding entry in second column was blank. Some error has to be thrown, and then it's up to the spreadsheet user how to handle it.

  • Thanks for that useful wrapper! Makes sense regarding vlookup, though I think the error attached to the cell would suffice. – Skipwave Jun 17 '16 at 19:42
  • 4
    `=iferror(vlookup(A1,Lookup!A:B,2,FALSE, "your message"))` will replace #NA with your custom message. – ALLSYED Jun 06 '18 at 08:27
  • with this formula you'll still get empty cells, so you should filter them too. You can do it by using SORT operation for example – Praytic May 12 '20 at 22:16
2

Just add TRUE, "" to your list of parameters, like so:

IFS(condition1, value1, condition2, value2, TRUE, "")

This works, because IFS "returns a value that corresponds to the FIRST true condition."

Bryan Chung
  • 21
  • 2