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.

3. Enter the REGEXREPLACE formula

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.

SPONSORED Name * Email * Commenting as . Not you? Save information for future comments
Comment

Δ