Suppress #N/A returned by Google Sheets vlookup

google-sheets
Skipwave picture Skipwave · Jun 16, 2016 · Viewed 64.4k times · Source

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 '[email protected]' 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?

Answer

tanius picture tanius · Oct 16, 2018

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

Related questions

How to highlight cell if value duplicate in same column for google spreadsheet?

I am looking for formula for google spreadsheet highlight cell if value duplicate in same column can anyone please assist me for this query?

Read More
Conditional formatting based on another cell's value

I'm using Google Sheets for a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell - C5. If C5 is greater than 80% then the background color is green …

Read More
How to get the correct range to set the value to a cell?

I want to set text or number in Google Sheet from script. I want to set Hello or number 9 in cell F2. I found this code so far: SpreadsheetApp.getActiveRange().setValue('hello'); but that doesn't specify which cell.

Read More