ISNA function and how to use it to ignore the error in Vlookup formula

 

To eliminate the #N/A error while using the “VLOOKUP” function we need to use the “ISNA” function along with the “IF” function in Microsoft Excel 2010.

IF :-IF condition allows us to use multiple conditions in a cell. It helps to make the function flexible. . Excel allows up-to a maximum of 64 conditions which can be checked in a cell.
Syntax of “IF” function: =if (logical test, [value_if_true], [value_if_false])

 

Example:Cells A2 and A3 contain the numbers 3 and 5 respectively. Lets put the condition that if the number in the cell is 3 then the formula should display “Yes” otherwise “No”.

=IF (A1=3,”Yes”,”No”)

img1

 

ISNA: Returns the logical value “TRUE” if the formula returns the #N/A error otherwise it returns the value “FALSE”.

Syntax of “ISNA” function: =ISNA (value)

img3

 

To understand how you can get rid of the #N/A error in Microsoft Excel – lets check the below example

Example : We have 2 data sets in which we need to populate the joining date from the 1st data set into the 2nd data set.

img4

 

  • Select the Cell H3, and write the formula.
  • =IF(ISNA(VLOOKUP(G3,$A$3:$B$13,2,FALSE)=TRUE),”Not Found”, VLOOKUP(G3,$A$3:$B$13,2,FALSE))
  • The function will return the joining date.
  • Copy the formula to the rest of the cells to return the joining date for each row populated in column A.
  • To Copy the formula in all cells press key “CTRL + C”in cell H3 and select the cellsH4 to H13 and press key “CTRL + V”on the keyboard.
  • The employee codes which are present in the 2nd data set but not present in the 1st data set will return the “Not Found” result instead of the #N/A error.

img5

 

To conclude, you can use this formula if you want the formula to return some text instead of the #N/A or any such error. You can even ask the formula to return blank cells by replacing the “Not Found” with “”.



One thought on “ISNA function and how to use it to ignore the error in Vlookup formula

  1. This is not a good formula because it uses four functions, and if the lookup value can be found, two VLOOKUPS are done, which is inefficient and calculation hungry when a large number of such formulae are deployed.
    Besides, Excel 2007 introduced the IFERROR function which eliminates the need for IF function.
    All that is required is:
    =IFERROR(VLOOKUP(G3,$A$3:$B$13,2,FALSE),”Not Found”)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>