Fix: The Array Value Could Not be Found Error in Sheets

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.

In addition to optimizing your SUBSTITUTE formulas with ARRAYFORMULA, it’s also important to understand other array methods in JavaScript. Learn more about troubleshooting issues with Array.prototype.map() and arrow functions to enhance your JavaScript coding skills.

You must 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.

1. To do that, open the Sheet spreadsheet you need to add the formula to.
2. Select a cell to include the formula in (it will display output across multiple cells).
3. Then copy this formula with the Ctrl + C hotkey: =ArrayFormula(if(REGEXMATCH(B2:B4,”^Yes|yes|Y|y”)=true,”Yes”)).
4. Click inside Sheets’ formula bar.
5. Paste the REGEXMATCH formula into Sheet’s formula bar by pressing Ctrl + V.
6. 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.
7. In addition, you’ll need to change the cell reference in that formula to match your own requirements.

3. Enter the REGEXREPLACE formula

1. REGEXREPLACE is another alternative you can try when your SUBSTITUTION formula doesn’t return the expected output.
2. Open Sheets in a browser.
3. Select a cell for the REGEXREPLACE formula.
4. Copy this formula: =ArrayFormula(regexreplace(” “&B2:B4&” “,” Yes | Y | Y “,” Yes”)).
5. Click within the formula bar, and press the Ctrl + V hotkey to paste the formula.
   
6. Thereafter, edit the REGEXREPLACE formula’s referenced Y and Yes text to whatever text you need to substitute.
7. 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.