Excel IFERROR & VLOOKUP - trap #N/A and other errors

This tutorial demonstrates how to combine the IFERROR and VLOOKUP functions in Excel to handle errors effectively. You'll also learn how to perform sequential VLOOKUPs using nested IFERROR functions.

Excel's VLOOKUP and IFERROR functions, while powerful individually, can be challenging to combine. This article provides clear examples illustrating their combined usage. If you're new to these functions, reviewing their individual functionalities beforehand is recommended.

Key Topics Covered:

• Using IFERROR with VLOOKUP to manage errors.
• Returning custom text, blank cells, or zeros instead of the standard "#N/A" error.
• Creating VLOOKUP formulas that always return a value.
• Performing sequential VLOOKUPs with nested IFERROR functions.

IFERROR VLOOKUP: Handling #N/A and Other Errors

When a VLOOKUP fails to find a value, it returns a "#N/A" error. This can be improved upon.

Example 1: Replacing Errors with Custom Text

To replace the error with custom text, wrap your VLOOKUP formula in IFERROR and specify the replacement text in the second argument ( value_if_error). For example, using "Not found":

=IFERROR(VLOOKUP(B2,'Lookup table'!$A$2:$B$5, 2, FALSE), "Not found")

This formula replaces "#N/A" with "Not found". A similar approach can be used with INDEX MATCH:

=IFERROR(INDEX('Lookup table'!$B$2:$B$5,MATCH(B2,'Lookup table'!$A$2:$A$5,0)), "Not found")

This is particularly useful for retrieving values from columns to the left of the lookup column.

Example 2: Returning Blank Cells or Zeros

To return a blank cell, use an empty string ("") as the value_if_error:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A$2:$B$5, 2, FALSE), "")

To return a zero, use 0:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A$2:$B$5, 2, FALSE), 0)

Important Note: IFERROR catches all errors, not just "#N/A". While convenient, this can mask errors like "#NAME?" (resulting from typos in named ranges), hindering error detection. For more precise error handling, consider using IFNA (Excel 2013 and later) or IF(ISNA(...)) for older versions.

Always Finding Something with Nested IFERROR

If a primary VLOOKUP fails, you can perform a secondary lookup to return a default value. For example, if an office number isn't found, return the central office's extension:

=IFERROR(VLOOKUP("office "&$D$2,$A$2:$B$7,2,FALSE),VLOOKUP("central office",$A$2:$B$7,2,FALSE))

This formula first searches for the specified office; if unsuccessful, it defaults to the "central office" extension. A more concise alternative is:

=VLOOKUP(IFERROR(VLOOKUP(D2,$A$2:$B$7,1,FALSE),"central office"),$A$2:$B$7,2)

Sequential VLOOKUPs with Nested IFERROR

For chained lookups across multiple sheets, nest IFERROR functions:

=IFERROR(VLOOKUP(A2,North!$A$2:$B$5,2,FALSE), IFERROR(VLOOKUP(A2,South!$A$2:$B$5,2,FALSE), IFERROR(VLOOKUP(A2,West!$A$2:$B$5,2,FALSE),"Not found")))

This formula searches sequentially across "North," "South," and "West" sheets, returning the first match found.

This comprehensive guide empowers you to leverage the combined power of IFERROR and VLOOKUP for robust and efficient data handling in Excel. Remember to choose the appropriate error-handling method based on your specific needs.

The above is the detailed content of Excel IFERROR & VLOOKUP - trap #N/A and other errors. For more information, please follow other related articles on the PHP Chinese website!