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. ## 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. 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. 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. ### 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. But if you type any name which isn’t in the dataset, The cell C14 will remain empty. ### 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. ### 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. ### 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. 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. ## 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.  