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.