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”.
[=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.
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]