Using VLOOKUP and ISNA functions to find matching values in different sheets in Excel

In this article we will learn how to use ISNA formula with Vlookup function to find matching values in different sheets in Excel.

There are many error-trapping functions like ISNA, IFNA, IFERROR, and ISERROR which we can use in Excel. These help the formulae to run smoothly.

To hide the #NA error we can use the Vlookup and ISNA functions together.

First, let us understand the reasons why the vlookup error #N/A occurs:

  • The data we want to lookup does not exist in our table
  • The table is not set in ascending order

Let’s take an example and understand:-

In a spreadsheet, we have Emp id, Sales Person & Units sold in column A, B & C respectively.

image 1

 

Sometimes, we need to use the Vlookup function in a different sheet other than the current sheet.

In sheet2, we are applying a Vlookup where B3 contains the Lookup Value i.e. 110 which is not present in the sheet “Datasheet” column A. In this case, cell C3 will return the #N/A error. Refer to the below screenshot.

image 2

 

To ignore #N/A error we will use ISNA function along with Vlookup function. Let’s understand by following simple steps:-

  • Enter the formula in cell C3
  • =IF(ISNA(VLOOKUP($B$3,Data!$A$3:$C$11,3,0)),"Not Exist",VLOOKUP($B$3,Data!$A$3:$C$11,3,0))
  • Press Enter

image 3

 

In above image you can see that as result we are “Not Exist”.

 

Formula Explanation:-

[=IF(ISNA(VLOOKUP($B$3,Data!$A$3:$C$11,3,0)),"Not Exist" ] :- In this part ISNA function will check that Vlookup function is giving #N/A error, if yes then IF function is helping to define the result which would be at the place of result, if no VLOOKUP($B$3,Data!$A$3:$C$11,3,0)) again it will go for Vlookup function to pick the value.

home button
Previous VLOOKUP with MATCH function
Next VLOOKUP with ISERROR function

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Comments

  1. Nice one ty
    Can you tell me how to get to know the contain of one sheet are fully available in sheet two. If not available then how to find which value is not covered
    E.g
    Sheet 1 contain two columns
    A with numbers 1 2 3 4
    B with values 42 43 44 45
    Sheet 2 contain numbers 1 2 3 4 5
    With values 32 33 34 35 36
    Now,with the help of vlookup we match numbers of both sheet
    But how can I get to know that which numbers are not incorporated in sheet.

  2. sir we have one problem plz solve this

    ,=INDEX(performance!$E$2:$L$301,MATCH(today!$E2&today!$F2&today!$G2&today!$H2&today!$I2&today!$J2&today!$K2,performance!$E$2:$E$301&performance!$F$2:$F$301&performance!$G$2:$G$301&performance!$H$2:$H$301&performance!$I$2:$I$301&performance!$J$2:$J$301&performance!$K$2:$K$301,0),8)

    this formula was given exject but we want + or - 3% range between plz solve my problem

    thank u Advance

  3. sir we have one problem plz solve this

    ,=INDEX(performance!$E$2:$L$301,MATCH(today!$E2&today!$F2&today!$G2&today!$H2&today!$I2&today!$J2&today!$K2,performance!$E$2:$E$301&performance!$F$2:$F$301&performance!$G$2:$G$301&performance!$H$2:$H$301&performance!$I$2:$I$301&performance!$J$2:$J$301&performance!$K$2:$K$301,0),8)

    this formula was given exject but we want +/- 3% range between plz solve my problem

    thank u Advance

  4. I used
    '=IF(ISNA(VLOOKUP($B$3,Datasheet!$A$4:$C$10,3,0)),”Not
    Exist”,VLOOKUP($B$3,Datasheet!$A$4:$C$10,3,0))'
    as a model, and it worked fine. Thanks!!

  5. i need formula for match two cell value from sheet 1 to Sheet2 if they match i want to call value from Sheet2
    for Exp.
    in Sheet1 in Sheet2
    # col1 Col2 col3 # col1 col2
    1 1/1/2014 Item1 2 1 2/1/2014 item1
    2 1/1/2014 Item2 2 1/1/2014 item1
    3 1/1/2014 Item3
    i want to replace in col3 which row no is found

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.