how to handle errors in google sheets
IN SUMMARY
In Google Sheets, errors like #N/A (not available) often occur when using lookup functions like VLOOKUP, HLOOKUP, or MATCH. These errors indicate that the value you're looking for is not present in the specified range. To handle these errors, you can use functions like IFERROR or IFNA to display custom messages or replace the error with a specific value.
Common Causes of #N/A Errors
If the range specified in the lookup function doesn't include the value you're searching for, you'll get a #N/A error. This can happen if you accidentally exclude rows or columns from the range or if there are blank rows or columns within the range.
If the value you're searching for contains hidden characters or spaces that don't match the data in the range, the lookup function won't find a match, resulting in a #N/A error. This can occur when data is imported from different sources or systems.
The VLOOKUP function has a final parameter that specifies whether the data is sorted or not. If you use the wrong parameter (TRUE or FALSE), the function may not find the value, leading to a #N/A error.
Handling #N/A Errors with IFERROR and IFNA
The IFERROR function allows you to specify a custom value or message to display if the lookup function returns an error. For example, =IFERROR(VLOOKUP(...), "Value not found") will display "Value not found" instead of #N/A.
The IFNA function is similar to IFERROR but specifically handles #N/A errors. For example, =IFNA(VLOOKUP(...), "Check spelling") will display "Check spelling" if the lookup function returns #N/A.
If you don't want to display a custom message, you can use IFERROR or IFNA to return a blank cell or zero instead of #N/A. For example, =IFERROR(VLOOKUP(...), "") will return a blank cell, and =IFERROR(VLOOKUP(...), 0) will return zero.
Preventing #N/A Errors
The TRIM function removes leading and trailing spaces from a value. You can use it to clean up data before performing a lookup, like this: =VLOOKUP(TRIM(A1), range, ...). This can prevent #N/A errors caused by hidden spaces.
Before using a lookup function, double-check that the specified range includes all the data you need. If there are blank rows or columns within the range, remove them or adjust the range accordingly.
If you're using the VLOOKUP function with sorted data, make sure to set the final parameter to TRUE. If the data is unsorted, set it to FALSE. This will ensure that the function searches the range correctly and avoids #N/A errors.