Remove #N/A in vlookup result

excel vlookup
office-rat picture office-rat · Jan 7, 2013 · Viewed 497.7k times · Source

How do I modify this function so that the result will merely be a blank cell rather than having #N/A show up if B2 has nothing in that cell?

I think I might need something like an ISERROR check but I don't 100% know what I'm doing.

=VLOOKUP(B2,Index!A1:B12,2,FALSE)

Thanks!

Answer

barry houdini picture barry houdini · Jan 7, 2013

If you only want to return a blank when B2 is blank you can use an additional IF function for that scenario specifically, i.e.

=IF(B2="","",VLOOKUP(B2,Index!A1:B12,2,FALSE))

or to return a blank with any error from the VLOOKUP (e.g. including if B2 is populated but that value isn't found by the VLOOKUP) you can use IFERROR function if you have Excel 2007 or later, i.e.

=IFERROR(VLOOKUP(B2,Index!A1:B12,2,FALSE),"")

in earlier versions you need to repeat the VLOOKUP, e.g.

=IF(ISNA(VLOOKUP(B2,Index!A1:B12,2,FALSE)),"",VLOOKUP(B2,Index!A1:B12,2,FALSE))

Related questions

Excel - find cell with same value in another worksheet and enter the value to the left of it

I have a report that is generated in Excel which contains an employee's number, but not his/her name. Not every employee will be on this worksheet on any given day. In a 2nd worksheet I have a list of …

Read More
Comparing two columns, and returning a specific adjacent cell in Excel

I am using a combination of if, vlookup, match, iserror functions, and unfortunately I've not been able to find the right formula. Comparing two columns for matches is easy enough. the tough part has been returning a specific cell once …

Read More
Check if an excel cell exists on another worksheet in a column - and return the contents of a different column

What I want to do is to say if the contents of cell D3 (on current worksheet) exist in column A in the first worksheet (in my case entitled list). (and they always do exist somewhere). Return the contents of …

Read More