Prantick Bala
When the VLOOKUP function can’t find the lookup value in the lookup array, it will show an error sign, #N/A. But you can get rid of this problem by using the IFERROR function with the VLOOKUP function. In this article, I show you 5 different uses of IFERROR with VLOOKUP in Excel.
Here’s an overview of the 3 methods along with the generic use of VLOOKUP. See the later part for more details along with 2 more methods.
How to Use IFERROR with VLOOKUP in Excel: 5 Different Uses
In order to avoid #N/A, we can combine IFERROR with VLOOKUP. In the following section it is described for 5 different cases along with the generic use.
Generic Use of VLOOKUP Function
First, we will see what will happen if we use only the VLOOKUP function. Suppose Jessica is a student whose name does not belong in our dataset. Now if we find the obtained mark of Jessica by using the VLOOKUP function, Excel will show an error sign #N/A. We can deal with this error sign by using the IFERROR function with the VLOOKUP function.
Now let’s see the different uses of IFERROR with VLOOKUP.
1. Replace #N/A with Custom Text
Suppose, when you search for a student whose name isn’t in the list, you want to show custom text such as “Not found”. For doing the task, enter the following formula in cell C14 and press ENTER.
Here, Now if you type any student name from your list in cell C13, you will get his/her obtained marks in cell C14. And if you type any student name which isn’t in your list, in cell C13, the C14 cell will show your custom text Not Found. Similar Readings: If you want to keep the cell empty when the searched name isn’t in your list, type the following formula in cell C13, Here, Now if you type any student name from your list in cell C13, you will get his/her obtained marks in cell C14. But if you type any name which isn’t in the dataset, The cell C14 will remain empty. Suppose, you have two lists in your dataset. You want to find the obtained marks for any student from both lists. Type the following formula in cell C13 and press ENTER. Here, Now if you type any of the names from any of your lists, in cell C13, you will get the obtained marks of that person in cell C14. Let’s say, you have contact numbers of different branches of your company in your dataset. Now you want to show a contact number if anyone searches for any of the branches even if the branch name isn’t your list. If the branch name isn’t in the list, you want to show the contact number of the Head office. Type the following formula in any empty cell and press ENTER. Here, Now if you type any branch name in cell C10 which isn’t in the list, you will get the contact number of Head office in the cell where you typed the formula. In Excel 2013 or in any older version the IFERROR function isn’t available. But you can do the same task by using the IF function and the ISNA function along with the VLOOKUP function. Type the following formula in cell C14 and press ENTER Now if you type any student name from your list in cell C13, you will get his/her obtained marks in cell C14. And if you type any student name which isn’t in your list, in cell C13, Cell C14 will show your custom text Not Found. Download Practice Workbook Download this practice workbook to exercise while you are reading this article. The IFERROR function allows you to omit the error value of the VLOOKUP function. You can use IFERROR with VLOOKUP for any one of the uses described in this article. If you face any problem while implying the functions together, please leave a comment. If you know of any additional uses of IFERROR with VLOOKUP, please let us know about that in the comment section.=IFERROR(VLOOKUP(C13,B4:C11,2,FALSE), "Not Found")
C13 = Lookup value which will be searched in the list
B4:C11 = Lookup range that is your dataset
2 = Lookup column that is the column of Obtained Marks
FALSE means the function will lookup for an exact match
2. Get Empty Cell Instead of #N/A
=IFERROR(VLOOKUP(C13,B4:C11,2,FALSE), " ")
C13 = Lookup value which will be searched in the list
B4:C11 = Lookup range that is your dataset
2 = Lookup column that is the column of Obtained Marks
FALSE means the function will lookup for an exact match
3. IFERROR with VLOOKUP for Split Dataset
=IFERROR(VLOOKUP(C13,B4:C11,2,FALSE),VLOOKUP(C13,B14:C20,2,FALSE))
C13 = Lookup value which will be searched in the list
B4:C11 =1st lookup range that is the 1st list of the dataset
B14:C20 = = 2nd lookup range that is the 2nd list of the dataset
2 = Lookup column that is the column of Obtained Marks
FALSE means the function will lookup for an exact match
4. IFERROR with VLOOKUP to Return Output Every Time
=IFERROR(VLOOKUP(C13,B4:C8,2,FALSE),VLOOKUP("Head office",B4:C8,2,FALSE))
C13 = Lookup value which will be searched in the list
B4:C11 = Lookup range that is your dataset
2 = Lookup column that is the column of Contact Number
FALSE means the function will lookup for an exact match
5. Apply VLOOKUP in Older Version of Excel
=IF(ISNA(VLOOKUP(C13,B4:C11,2,FALSE)), "Not Found", VLOOKUP(C13,B4:C11,2,FALSE))
C13 = Lookup value which will be searched in the list
B4:C11 = Lookup range that is your dataset
2 = Lookup column that is the column of Contact Number
FALSE means the function will lookup for an exact match
Conclusion
Further Readings
