Remove # N / A as a result of vlookup

Question

How can I change this function so that the result is just an empty cell, not # N / A if B2 has nothing in this cell?

I think I might need something like an ISERROR check, but I don’t know what I am doing 100%.

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

Thank!

Answer 1

If you only want to return an empty field when B2 is empty, you can use the additional IF function for this scenario, i.e.

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

or return an empty string with any error from VLOOKUP (for example, if B2 is full, but VLOOKUP is not found), you can use the 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 VLOOKUP, for example

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

Answer 2

if you want to change the cell color in case of vlookup error, then go to conditional formatting. To do this, go to "CONDITIONING"> "NEW RULE". In this, select "Choose rule type" = "Format only cells containing". After that, the window below changes, in which select "Error" in the drop-down list. After that, execute accordingly.

Answer 3

To avoid errors in any excel function, use the error handling functions that start with IS * in Excel. Insert your function with these error handling functions and avoid unwanted text in the results. Learn more at the OfficeTricks Page.