Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove #N/A in vlookup result

Tags:

excel

vlookup

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!

like image 209
office-rat Avatar asked Jan 07 '13 20:01

office-rat


2 Answers

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

like image 88
barry houdini Avatar answered Oct 26 '22 22:10

barry houdini


if you are looking to change the colour of the cell in case of vlookup error then go for conditional formatting . To do this go the "CONDITIONAL FORMATTING" > "NEW RULE". In this choose the "Select the rule type" = "Format only cells that contains" . After this the window below changes , in which choose "Error" in the first drop-down .After this proceed accordingly.

like image 29
Abhishek Madan Avatar answered Oct 26 '22 21:10

Abhishek Madan