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

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/tips/how-to-remove-na-error-result-when-using-vlookup-formula-in-microsoft-excel.html
SHARE




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 “”.

Please follow and like us:
0


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 *

To avoid automated spam,Please enter the value *

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>