Lookup functions are powerful tools in Excel that allow you to find and retrieve data from a table or range based on certain criteria. Some of the most common lookup functions are VLOOKUP, HLOOKUP, INDEX, and MATCH. However, sometimes these functions may return errors, such as #N/A, #REF, #VALUE, or #DIV/0, when they cannot find a match, refer to an invalid cell, use an inappropriate argument, or perform an illegal operation. These errors can make your spreadsheet look messy and unprofessional, and may also affect other formulas that depend on the lookup results.
Fortunately, Excel provides several ways to hide or handle these errors, so that you can display more meaningful and user-friendly information instead. In this article, we will explore some of the methods to hide errors returned by lookup functions in Excel, such as using the IFERROR function, the IFNA function, conditional formatting, and custom number formats. We will also show you how to use nested IFERROR functions to perform sequential lookups and always find something.
Using the IFERROR Function
The easiest way to hide error values on your spreadsheet is with the IFERROR function. The IFERROR function takes two arguments: a value (or a formula) and a value_if_error. The function returns the first argument if it is not an error, and the second argument if it is an error. For example, the formula =IFERROR(A1/B1,"") will return the result of dividing A1 by B1, unless B1 is zero or blank, in which case it will return an empty string.
Using the IFERROR function, you can replace the error that’s shown with any value you like, such as a text message, a zero, a blank cell, or even an alternative formula. For example, if you want to use a VLOOKUP function to find the price of a product in a table, but display “Not found” if the product is not in the table, you can use the following formula:
=IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"Not found")
where E2 is the product name, A2:B10 is the table range, and 2 is the column index number. The formula will return the price of the product if it is in the table, and “Not found” if it is not.
The IFERROR function can catch any kind of error, not only #N/A. This can be useful if you want to mask all possible errors, but it can also be risky if you want to distinguish between different types of errors and handle them differently.
Using the IFNA Function
If you only want to hide the #N/A error that VLOOKUP or other lookup functions return when they can’t find a lookup value, you can use the IFNA function instead of the IFERROR function. The IFNA function works similarly to the IFERROR function, but it only applies to the #N/A error. For example, the formula =IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"Not found") will return the same result as the previous example, but it will not hide other errors, such as #REF or #VALUE.
The IFNA function is available in Excel 2013 and later versions. If you are using an earlier version of Excel, you can use a combination of the IF and ISNA functions to achieve the same result. For example, the formula =IF(ISNA(VLOOKUP(E2,A2:B10,2,FALSE)),"Not found",VLOOKUP(E2,A2:B10,2,FALSE)) will also return the price of the product or “Not found” if it is not in the table.
Using Conditional Formatting
Another way to hide error values on your spreadsheet is to use conditional formatting to change the font color of the cells that contain errors to match the background color of the cells. This way, the error values will not be visible, but they will still be in the cells and can be seen in the formula bar or copied to other cells.
To apply conditional formatting to hide error values, follow these steps:
- Select the cells that contain the lookup formulas.
- On the Home tab, click Conditional Formatting > New Rule.
- In the New Formatting Rule dialog box, click Format only cells that contain.
- Under Format only cells with, make sure Cell Value appears in the first list box, and error appears in the second list box.
- Click the Format button.
- Click the Font tab and then, under Color, select the same color as the background of the cells. You can use the eyedropper tool to pick the color from a cell.
- Click OK twice.
The error values will now be hidden, but you can still see them in the formula bar or copy them to other cells.
Using Custom Number Formats
Another option to hide error values on your spreadsheet is to use custom number formats to display nothing in the cells that contain errors. Custom number formats allow you to specify how you want to display numbers, dates, times, text, and error values in different scenarios. You can use the following syntax to create a custom number format:
[positive value];[negative value];[zero value];[text value]
For example, the custom number format 0.00;-0.00;0;@ will display numbers with two decimal places, negative numbers with a minus sign, zero as zero, and text as text. However, if you want to display nothing for error values, you can use three semicolons as the fourth argument, like this:
0.00;-0.00;0;;
This custom number format will display numbers as before, but it will display nothing for error values and text values.
To apply a custom number format to hide error values, follow these steps:
- Select the cells that contain the lookup formulas.
- On the Home tab, click the dialog box launcher in the Number group.
- In the Format Cells dialog box, click the Number tab and then, under Category, click Custom.
- In the Type box, enter the custom number format that suits your needs, such as
0.00;-0.00;0;;or;;;. - Click OK.
The error values will now be hidden, but they will still be in the cells and can be seen in the formula bar or copied to other cells.
Using Nested IFERROR Functions for Sequential Lookups
Sometimes, you may want to perform multiple lookups in different tables or ranges, and return the first match that is found. For example, you may have a list of products and their prices in different currencies, and you want to find the price of a product in your preferred currency, or in another currency if the preferred one is not available. In this case, you can use nested IFERROR functions to perform sequential lookups and always find something.
For example, suppose you have the following tables that show the prices of some products in US dollars, euros, and pounds:
| Product | Price (USD) |
|---|---|
| Apple | 1.00 |
| Banana | 0.50 |
| Cherry | 2.00 |
| Date | 3.00 |
| Product | Price (EUR) |
|---|---|
| Apple | 0.85 |
| Banana | 0.42 |
| Cherry | 1.70 |
| Fig | 4.00 |
| Product | Price (GBP) |
|---|---|
| Apple | 0.72 |
| Banana | 0.36 |
| Cherry | 1.44 |
| Grape | 5.00 |
If you want to find the price of a product in US dollars, or in euros if US dollars are not available, or in pounds if neither US dollars nor euros are available, you can use the following formula:
=IFERROR(VLOOKUP(E2,A2:B5,2,FALSE),IFERROR(VLOOKUP(E2,C2:D5,2,FALSE),IFERROR(VLOOKUP(E2,E2:F5,2,FALSE),"Not found")))
where E2 is the product name, A2:B5 is the table range for US dollars, C2:D5 is the table range for euros, and E2:F5 is the table range for pounds. The formula will return the price of the product in the first available currency, and “Not found” if the product is not in any of the tables.
You can nest as many IFERROR functions as you need, depending on how many lookup sources you have. However, be careful not to make the formula too complex and hard to read or debug.
