If any lookup values does not found in the list of array an error message #NA display in the resulted cells. If you want to remove an error value during lookup the value then you must know how to use IFNA with VLookup function. Sometimes when the data your are looking for is not found in the list, you will receive #N/A error message.
Must Read: VLookup with Two Lookup Values using IFError function
Lot of person familiar with IFNA with VLookup function in Excel. When you lookup any value in the list of array you will get the result which match the criteria. Sometime when any criteria does not match in the list it displays an error message. In this guide we have to know how to use IFNA function with VLookup function in Excel.
Let’s take an example and which helps you to know how you can ignore VLOOKUP #N/A error value in Excel. To get the correct result without #N/A error value you have to use IFNA with VLookup function. To do this job you must have to take few given simple steps.
We have an employee database in two sheets, now you want to update the information in another data with the help of sheet 2. If any data not found in the list it displays #N/A error value but you can easily remove error value using IFNA with VLookup function.
How to use IFNA with VLookup function
Step 1: Prepare the following given employee database in any excel sheet.
Must Read: 3 Ways to find duplicate values on same worksheet – Excel
Step 2: Now you want to lookup incentive amount which match the employee code in employee data 2. To lookup the incentive amount which is based on emp code and name you have to type the given function. =VLOOKUP(A3&” “&B3,$F$2:$J$9,5,0)
Drag the given function for rest of the cells, but few emp code which does not match excel displays an error message #N/A. If you don’t want to keep given error message then you have to update the function with IFNA function.
=IFNA(VLOOKUP(A3&” “&B3,$F$2:$J$9,5,0),”Not Earn Incentive”)
Now, you can check all #N/A error value replaced with an error message which emp code does not found in the list.
If you have good command on excel function then you must have to do more practise on Excel function. If you have any suggestion regarding the guide then send us with comment box.