Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use IFERROR with VLOOKUP in Excel (5 Different Uses)

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.

Overview of IFERROR with VLOOKUP


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Uses of IFERROR with VLOOKUP in Excel

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.

Generic Use of 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.

=IFERROR(VLOOKUP(C13,B4:C11,2,FALSE), "Not Found")

Here,
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

Now if you type any student name from your list in cell C13, you will get his/her obtained marks in cell C14.

Replace #N/A with Custom Text

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.

Replace #N/A with Custom Text


2. Get Empty Cell Instead of #N/A

If you want to keep the cell empty when the searched name isn’t in your list, type the following formula in cell C13,

=IFERROR(VLOOKUP(C13,B4:C11,2,FALSE), " ")

Here,
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

Now if you type any student name from your list in cell C13, you will get his/her obtained marks in cell C14.

Get Empty Cell Instead of #N/A

But if you type any name which isn’t in the dataset, The cell C14 will remain empty.

Get Empty Cell Instead of #N/A


3. IFERROR with VLOOKUP for Split Dataset

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.

=IFERROR(VLOOKUP(C13,B4:C11,2,FALSE),VLOOKUP(C13,B14:C20,2,FALSE))

 

Here,
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

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.

IFERROR with VLOOKUP for Split Dataset


4. IFERROR with VLOOKUP to Return Output Every Time

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.

=IFERROR(VLOOKUP(C13,B4:C8,2,FALSE),VLOOKUP("Head office",B4:C8,2,FALSE))

Here,
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

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.

IFERROR with VLOOKUP for Returning Output Every Time


5. Apply VLOOKUP in Older Version of Excel

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

=IF(ISNA(VLOOKUP(C13,B4:C11,2,FALSE)), "Not Found", VLOOKUP(C13,B4:C11,2,FALSE))
Here,
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

Now if you type any student name from your list in cell C13, you will get his/her obtained marks in cell C14.

Apply VLOOKUP in Older Version of Excel

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.

Apply VLOOKUP in Older Version of Excel


Conclusion

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.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo