Have you ever encountered a #REF! error in Google Sheets and wondered how to fix it? You’re not alone! In this tutorial, I will show you how to fix and remove most #REF! errors in Google Sheets, including scenarios where the IFERROR function fails. So, let’s dive in!
VLOOKUP Out-of-Bounds Range Error
The VLOOKUP out-of-bounds range error occurs when you use the wrong index number. For example, if you specify an index number that is higher than the number of columns in the range, you’ll get a #REF! error.
To fix this error, simply correct the index number in your formula to match the actual number of columns in the range. For instance, if you have 5 columns, the index number should be between 1 and 5.
If you want to remove the #REF! error and display a custom value or a blank cell instead, you can use the IFERROR function. Here’s an example formula:
=IFERROR(VLOOKUP(A1,A4:E,6,0),"")Circular Dependency Detected Errors
A circular dependency detected error occurs when a formula refers to the cell it is entered into. To fix this error, you can turn on iterative calculation in the settings or correct the formula itself.
If you want to remove the #REF! error caused by a circular dependency, you can use conditional formatting. Simply create two conditional format rules to mask the error and display the value of the cell if it doesn’t contain an error.
Deleting a Row or Column and #REF! Errors
When you delete a row or column that is referenced in a formula, you may encounter a #REF! error. To fix this error, you need to edit the formula and replace the #REF! error with the proper cell references.
If you want to remove the #REF! error caused by deleting a row or column, you can use the IFERROR function. Here’s an example formula:
=IFERROR(XLOOKUP("Apple",#REF!,A:A),"This formula has an issue!")#REF! Error Caused by Mismatched Row Sizes in Concatenated Arrays
If you combine two arrays using curly braces in Google Sheets, the number of rows in both arrays must match. Otherwise, you’ll get a #REF! error.
To fix this error, you can replace the curly braces with the HSTACK function in Google Sheets. For example:
=HSTACK(B2:B6,C2:C5)If you want to remove the #REF! error caused by mismatched row sizes in concatenated arrays, you can use the IFERROR function. Here’s an example formula:
=IFERROR({B2:B6,C2:C5},"This formula has an issue!")Array Result Was Not Expanded Error
The array result was not expanded error occurs when a formula returns values in more than one cell but can’t fill the cells due to existing values.
To fix this error, you can remove the existing values in the cells that cause the problem or use the ARRAY_CONSTRAIN function to limit the size of the output.
If you want to remove the #REF! error caused by the array result not being expanded, you can use conditional formatting or a workaround solution. For example, you can create a helper cell with a drop-down menu to control the display of the formula result.
Allow Access IMPORTRANGE Error
When you use the IMPORTRANGE function to import data from one sheet to another, you may encounter a #REF! error if you haven’t authorized access to the source sheet.
To fix this error, you need to allow access to the source sheet either as the owner or by requesting access from the sheet owner.
To remove the #REF! error caused by the IMPORTRANGE function, you can wrap the formula with the IFERROR function. This will prevent the error from being displayed.
Conclusion
In this tutorial, we’ve explored different types of #REF! errors in Google Sheets and learned how to fix and remove them. Remember, it’s important to fix the errors rather than just hiding them. Using the IFERROR function and other techniques mentioned here, you can ensure your Google Sheets are error-free and accurate. For more Google Sheets tips and tutorials, visit Crawlan.com. Happy spreadsheeting!
