- Google Sheets is a great web alternative for those that don't have Microsoft Excel.
- This guide will show you how to fix the array value could not be found error in Sheets.
- If you need more tutorials about this program, check out our Google Sheets Hub.
- For more articles about this amazing software suite, check out our Googles Docs page.
Google Sheets is among the best freely available spreadsheet app alternatives to Excel. Sheets includes a SUBSTITUTE formula that enables you to replace a specific letter (or text string) in one cell with a word.
The cell that includes the SUBSTITUTE formula can display the replaced text for one cell.
However, the SUBSTITUTE formula can’t display replaced text output for a range of cells. If you include a cell range within it, the formula’s cell will display an Array value could not be found error message as shown in the snapshot directly below.
If you’re wondering how to fix that error so that SUBSTITUTE can be applied to a range of cells, check out the resolutions below.
How can I fix the array value error for SUBSTITUTE?
1. Turn the SUBSTITUTE formula into an array formula
To fix the Array value could not be found error, you need to incorporate SUBSTITUTE within an array formula. An array formula is one that can return multiple output for a range of cells.
Thus, users who need to replace text in range of cells and then display the output in another range need to utilize an array formula.
So, what you need to do is add ARRAYFORMULA to the beginning of the SUBSTITUTE formula. To do that, select the cell that includes the SUBSTITUTE formula.
Then click in the formula bar, enter ARRAYFORMULA just after the equals (=) sign as shown in the snapshot directly below.
Then your SUBSTITUTE formula will display replaced text output for a range of cells instead of an array error.
In the example shown in the snapshot directly below, the formula replaces Y in three column cells with Yes and displays the output across three other cells below them.
2. Enter the REGEXMATCH formula instead
Alternatively, you could combine REGEXMATCH with an array formula for the same output.
- To do that, open the Sheet spreadsheet you need to add the formula to.
- Select a cell to include the formula in (it will display output across multiple cells).
- Then copy this formula with the Ctrl + C hotkey: =ArrayFormula(if(REGEXMATCH(B2:B4,”^Yes|yes|Y|y”)=true,”Yes”)).
- Click inside Sheets’ formula bar.
- Paste the REGEXMATCH formula into Sheet’s formula bar by pressing Ctrl + V.
- That formula replaces Y with Yes for the cell range B2:B4.
- You’ll need the change the Y and Yes text in the formula to whatever you need for it.
- In addition, you’ll need to change the cell reference in that formula to match your own requirements.
3. Enter the REGEXREPLACE formula
- REGEXREPLACE is another alternative you can try when your SUBSTITUTION formula doesn’t return the expected output.
- Open Sheets in a browser.
- Select a cell for the REGEXREPLACE formula.
- Copy this formula: =ArrayFormula(regexreplace(” “&B2:B4&” “,” Yes | Y | Y “,” Yes”)).
- Click within the formula bar, and press the Ctrl + V hotkey to paste the formula.
- Thereafter, edit the REGEXREPLACE formula’s referenced Y and Yes text to whatever text you need to substitute.
- Change that formula’s cell reference to meet the requirements of your spreadsheet.
So, that’s how you can fix the Array value could not be found error in Google Sheets. The overall resolution is to combine SUBSTITUTE, REGEXREPLACE, or REGEXMATCH with array formulas so that they display replaced (or substituted) text output across a range of cells.
Let us know if you found this tutorial to be useful by leaving us a message in the comments section below.
FAQ: Learn more about Google Sheets
- What is Google Sheets?
Google Sheets is an online spreadsheet client that is part of the Google Docs online suite.
- Are there any alternatives to Google Sheets?
If you want an alternative to Google Sheets that is also free, you could try LibreOffice’s Calc.
- What can I do with Google Sheets?
One fun thing you can do with Google Sheets is transform it into a translator.
Editor's Note: This article was originally published in March 2020 and was revamped and updated in July 2020 for freshness, accuracy, and comprehensiveness.