How to Use IFERROR with XLOOKUP: A Complete Guide
Using IFERROR with XLOOKUP in Excel can be a game changer when it comes to handling errors in formulas. The XLOOKUP function is one of Excel’s most powerful tools for searching data, but sometimes, when it doesn’t find the exact match, it returns an error. This is where IFERROR comes in handy, allowing you to manage such errors and replace them with more user-friendly results.
In this article, we’ll go through how to combine these two functions, explore practical examples, and ensure your Excel workbooks operate smoothly.
What is IFERROR in Excel?
IFERROR is a function that helps manage and handle errors in Excel formulas. It works by evaluating a formula, and if that formula results in an error, the IFERROR function replaces the error with a value or message you specify. Errors like #N/A
, #DIV/0!
, or #VALUE!
can disrupt your workflow, and IFERROR ensures you display more understandable messages instead of showing those errors.
Syntax of IFERROR
IFERROR(value, value_if_error)
- value: The formula or expression that is being evaluated.
- value_if_error: The result that will be returned if an error is found.
For example:
=IFERROR(A1/B1, "Error: Division by Zero")
If B1 contains a zero, instead of returning #DIV/0!
, this formula will return “Error: Division by Zero”.
What is XLOOKUP in Excel?
The XLOOKUP function is a powerful alternative to older lookup functions like VLOOKUP and HLOOKUP. It allows you to search a range or array for a match and return a corresponding value from another range or array.
Syntax of XLOOKUP
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: The value you want to search for.
- lookup_array: The range where you’re searching for the lookup value.
- return_array: The range from which you want to return the corresponding value.
- if_not_found: (Optional) The value to return if no match is found.
- match_mode: (Optional) Defines the type of match (exact, approximate, etc.).
- search_mode: (Optional) Specifies the search direction (first-to-last or last-to-first).
For example:
=XLOOKUP("Product A", A2:A10, B2:B10, "Not Found")
This function searches for “Product A” in A2:A10 and returns the corresponding value from B2:B10. If “Product A” is not found, it will return “Not Found”.
Why Combine IFERROR with XLOOKUP?
The XLOOKUP function can return an error if the lookup_value doesn’t exist in the lookup_array. While XLOOKUP has an optional if_not_found argument, using IFERROR provides more flexibility and control in handling errors.
By combining IFERROR with XLOOKUP, you can catch any errors and replace them with meaningful messages or actions, especially when you’re dealing with more complex data.
Basic Example of IFERROR with XLOOKUP
=IFERROR(XLOOKUP("Product A", A2:A10, B2:B10), "Product not available")
In this example, if XLOOKUP cannot find “Product A” in the A2:A10 range, the IFERROR function will catch the error and return “Product not available” instead of showing an error code.
How to Use IFERROR with XLOOKUP – Step-by-Step Guide
Step 1: Identify the Lookup Value and Arrays
Start by determining what value you want to search for and where the search will occur. For example, suppose you have a list of products in column A and their prices in column B. You want to look up the price of a product.
- lookup_value: The product name you’re searching for.
- lookup_array: The list of products.
- return_array: The list of prices.
Step 2: Write the XLOOKUP Formula
Create the basic XLOOKUP formula. For instance:
=XLOOKUP("Product A", A2:A10, B2:B10)
In this example, the formula will search for “Product A” in the A2:A10 range and return the corresponding price from the B2:B10 range.
Step 3: Wrap the XLOOKUP in IFERROR
Now, wrap the XLOOKUP formula with the IFERROR function to handle any potential errors:
=IFERROR(XLOOKUP("Product A", A2:A10, B2:B10), "Product not found")
With this setup, if “Product A” is not found, the formula will return “Product not found” instead of displaying an error.
Advanced Examples of IFERROR with XLOOKUP
Example 1: Dynamic Lookup with Cell References
In more dynamic cases, you might want to use cell references for your lookup_value. This allows you to search for different products without changing the formula manually.
=IFERROR(XLOOKUP(D1, A2:A10, B2:B10), "Product not available")
Here, D1 contains the product you’re searching for. The formula will return the corresponding value from the B2:B10 range or display “Product not available” if the product isn’t found.
Example 2: Multiple Return Columns
You can use XLOOKUP to return multiple values by extending the return_array across several columns. Let’s say you have columns for product names, prices, and stock levels. To return both price and stock information:
=IFERROR(XLOOKUP(D1, A2:A10, B2:C10), "Product not available")
In this case, if the product is found, the formula returns both the price (from column B) and the stock level (from column C). If the product is not found, it returns “Product not available”.
Example 3: Error Handling for Invalid Data Types
Sometimes, you might encounter situations where the lookup value is invalid due to a mismatch in data types. For example, if you’re looking up a text value in a numeric array, XLOOKUP will return an error. By using IFERROR, you can manage these cases smoothly.
=IFERROR(XLOOKUP("123", A2:A10, B2:B10), "Invalid data type")
This formula checks whether the search for “123” is valid within the range. If it’s not (due to incompatible data types), it returns “Invalid data type”.
Example 4: Using IFERROR with Nested XLOOKUP
You can also nest multiple XLOOKUP functions within an IFERROR to check more than one condition. For example, if you’re trying to search for a product in two different lists:
=IFERROR(XLOOKUP("Product A", A2:A10, B2:B10), XLOOKUP("Product A", D2:D10, E2:E10), "Product not available in either list")
This formula first searches in the A2:A10 range and returns the corresponding value from B2:B10. If the product is not found, it moves on to search in D2:D10 and returns the value from E2:E10. If it’s not found in either list, it displays “Product not available in either list”.
Practical Applications of IFERROR with XLOOKUP
The combination of IFERROR and XLOOKUP is extremely useful in various real-world scenarios:
- Inventory management: When searching for items in stock, you can avoid displaying confusing error messages when an item is unavailable.
- Sales reporting: Look up product sales data and show a custom message when a product’s sales data is missing.
- Data validation: Validate inputs and return helpful prompts when incorrect data types are entered into a worksheet.
Using Tables for Data Lookup
When working with larger datasets, tables can make your lookup formulas cleaner and more manageable. Here’s an example using a table for product data:
Product Name | Price | Stock Level |
---|---|---|
Product A | $10 | 100 |
Product B | $20 | 50 |
Product C | $15 | 75 |
With the data organized in a table, your XLOOKUP formula becomes simpler:
=IFERROR(XLOOKUP("Product A", Table1[Product Name], Table1[Price]), "Product not available")
This formula searches the Product Name column in Table1 and returns the price. If the product is not found, it returns “Product not available”.
Common Mistakes to Avoid
- Not using IFERROR for non-existent values: If you skip IFERROR, your XLOOKUP formula could return unwanted errors that confuse end users.
- Incorrect range references: Ensure your lookup_array and return_array ranges match in size. Otherwise, XLOOKUP may fail.
- Overcomplicating formulas: Use IFERROR and XLOOKUP only when necessary. Simplify your formulas as much as possible.
Final Thoughts
Using IFERROR with XLOOKUP in Excel is a practical way to manage errors and enhance the user experience of your spreadsheets. By handling potential errors proactively, you can ensure your data analysis and lookup processes run smoothly without interruptions from unwanted error codes.
Whether you’re managing a product list, performing data validation, or creating reports, combining IFERROR with XLOOKUP gives you the flexibility to handle various scenarios effectively.
FAQs
What is the purpose of using IFERROR with XLOOKUP in Excel?
The purpose of using IFERROR with XLOOKUP is to handle errors that occur when the XLOOKUP function doesn’t find a match. IFERROR allows you to return a custom message or value, preventing disruptive error codes from showing up in your spreadsheet.
How does IFERROR work with XLOOKUP?
IFERROR wraps around the XLOOKUP function. If XLOOKUP results in an error (such as when a lookup value isn’t found), IFERROR intercepts that error and returns the specified alternative message or value.
What kind of errors can IFERROR handle?
IFERROR can handle all types of Excel errors such as #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, and #NUM!. This makes it highly versatile when combined with functions like XLOOKUP.
Can I use IFERROR with XLOOKUP to handle multiple lookup arrays?
Yes, you can use IFERROR with nested XLOOKUP functions to handle multiple lookup arrays. This is useful when searching through more than one range for a value and returning a custom message if the value is not found in any of them.
What happens if XLOOKUP doesn’t find a match and IFERROR is not used?
If XLOOKUP doesn’t find a match and IFERROR is not used, it will return an error (usually #N/A). This error can be unsightly and confusing, which is why IFERROR is helpful in managing such cases.
How can I display custom messages using IFERROR with XLOOKUP?
You can display custom messages by specifying a message in the second argument of IFERROR. For example, IFERROR(XLOOKUP(‘Product A’, A2:A10, B2:B10), ‘Product not available’) will return ‘Product not available’ if XLOOKUP cannot find ‘Product A’.

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.