Remove N A in vlookup result

Question

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

User · Accepted Answer

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

User · Answer

To avoid errors in any excel function  use the Error Handling functions that start with IS  in Excel   Embed your function with these error handing functions and avoid the undesirable text in your results   More info in OfficeTricks Page

User · Answer

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

[excel] Remove #N/A in vlookup result

Examples related to excel

Examples related to vlookup