Excel IFERROR with VLOOKUP (Table of Contents)
IFERROR with VLOOKUP in Excel
Vlookup with the IFError function in Excel helps the user return an error message based on the user’s choice if any situation occurs. Vlookup function lookups at the values from the selected range or table and returns the exact or approximate match. But there comes some condition where we end up getting an error if the format of the range is not right or the value not found. In that case, with the help of the IFError function, we can see any customized message if the vlookup could not be able to find the value and gives the error.
Below is the VLOOKUP Formula in Excel:
The arguments of the VLOOKUP function are explained below:
- lookup_ value: It is a number, a text string, or a cell reference, which is to be searched in the first column of a range of cells.
- table_ array: It is the cell reference or the range name of the entire range of data.
- col_ index_ num: It is the column from which the result is required.
- range_ lookup: It is specified whether you want an exact or an approximate match. The possible value is TRUE or FALSE. The TRUE value returns an approximate match, and the FALSE value returns an exact match.
The IFERROR function returns a value one specifies id a formula evaluates to an error; otherwise, it returns the formula. It is used to trap and handle errors produced by other formulas or functions. IFERROR checks for the following errors: #N/A, #VALUE! , #REF! , #DIV/0! , #NUM! , #NAME? , or #NULL!
Below is the IFERROR Formula in Excel:
The arguments of the IFERROR function are explained below:
- value: It is the value, reference, or formula to check for an error.
- value_ if_ error: It is the value to return if an error is found.
While using the VLOOKUP function in MS Excel, if the value searched for is not found in the given data, it returns a #N/A error.
Below is the IFERROR with VLOOKUP Formula in Excel:
=IFERROR( VLOOKUP (lookup_ value,table_ array,col_ index_ num, [range_ lookup]), value_ if_ error)
How to Use IFERROR with VLOOKUP in Excel?
IFERROR with VLOOKUP in Excel is very simple and easy to use. Let understand the working of IFERROR with VLOOKUP in Excel by some example.
Example #1 – IFERROR with VLOOKUP
Let us take an example of the basic pay of the employees of a company.
In the above figure, we have a list of employee ID, Employee Name and Employee basic pay.
Now, we want to search the employees’ basic pay with respect to Employee ID 5902.
We will use the following formula:
=VLOOKUP (F5, B3:D13, 3, 0)
But, that employee ID is not present in the list. In this situation, the VLOOKUP function will return the #N/A error.
So it is better to replace the #N/A error with a customized value that everyone can understand why the error is coming.
So, we will use IFERROR with VLOOKUP Function in Excel in the following way:
=IFERROR (VLOOKUP (F5, B3:D13, 3,0), “Data Not Found”)
We will observe that the error has been replaced with the customized value “Data Not Found“.
Example #2 – Use of IFERROR with VLOOKUP on a Fragmented dataset
We can also use IFERROR with the VLOOKUP function on the fragmented data sets from the same worksheet, workbook, or from different workbooks.
We can use the function in the same workbook or from different workbooks by the use of 3D cell referencing.
Let us take the example on the same worksheet to understand the usage of the function on the fragmented datasets in the same worksheet.
In the above figure, we have two sets of data of basic pay of the employees. We have two data sets of employee ID, Employee Name and Employee basic pay.
Now, we want to search the employees’ basic pay with respect to Employee ID 5902.
We will use the following formula for searching data in table 1:
=VLOOKUP (G18, C6:E16, 3, 0)
The result will come as #N/A. As the data searched for is unavailable in the table 1 data set.
We will use the following formula for searching data in table 2:
=VLOOKUP( G18, J6:L16, 3, 0)
The result will come as 9310. The employee ID 5902 is available in Table 2 data set.
Now, we want to compare both of the data sets of table 1 and table 2 in a single cell and get the result.
It is better to replace the #N/A error with a customized value that everyone can understand why the error is coming.
So we will replace the #N/A error with a customized text “Data Not Found”.
So, we will use IFERROR with VLOOKUP Function in Excel in the following way:
=IFERROR (VLOOKUP (lookup_ value, table_ array, col_ index_ num, [range_ lookup]), IFERROR (VLOOKUP (lookup_ value, table_ array, col_ index_ num, [range_ lookup]), value_ if_ error))
We have used the function in the example in the following way:
=IFERROR (VLOOKUP (G18, C6:E16, 3,0), IFERROR (VLOOKUP (G18, J6:L16, 3, 0),”Data Not Found”))
As the employee ID 5902 is available in the table 2 data set, the result will show as 9310.
If the dataset does not contain the search value in both the table, the result will come as “Data Not Found” instead of the #N/A error.
Pros:
- Useful to trap and handle errors produced by other formulas or functions.
- IFERROR checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!
Cons:
- IFERROR replaces all types of errors with the customized value.
- If any other errors except the #N/A occur, the customized value specified will still be viewed in the result.
Things to Remember
- If a value is not given, evaluation happens as an empty text or string (“) and not an error.
- If value_ if_ error is given as an empty text (“”), nothing is displayed even when an error is found.
- If IFERROR is given as a table array formula, it returns an array of results with one item per cell in the value field.
Recommended Articles
This has been a guide to IFERROR with VLOOKUP in Excel. Here we discuss the IFERROR with VLOOKUP Formula in Excel and How to use IFERROR with VLOOKUP in Excel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
