*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.

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.

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

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.

*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 [email protected]*

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

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

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

Hi Anand,

Please post your query @ http://www.excelforum.com, you will get appropriate reply immediately.

Thanks

Site Admin

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

Thanks for the tip on the correct IF(ISNA … syntax in a Vlookup formula. Exactly what I was looking for,