How To Fix ‘An Array Value Could Not Be Found’ Error On Sheets?

The error “An array value could not be found” is quite common in Sheets, whether it is Excel or Google Sheets. This error appears when the used formula is incorrect. For Google Sheets, this error tends to appear while referencing another sheet or replacing a specific letter, word, or line using the substitution formula. If you are also facing this issue, try the troubleshooting mentioned here.

How To Fix Error An Array Value Could Not Be Found?

The array value not found error generally shows that the input formula is not correct. Primarily, it is due to using incorrect substitution formula.

Many users tend to use the Substitution formula for replacing letters, words, or lines in a cell, because of convenience. However, if entered incorrectly, it will show the error ‘An array value could not be found’ on both Excel and Google Sheets.

The following solutions should work for both Google Sheets and Excel.

Solution 1: Turn The Substitute Formula Into An Array Formula

The first thing you can do is turn the substitution formula into an array formula. An array formula can return multiple outputs for the selected range of cells. So, if you need to replace text in one range of cells and display its output in another range of cells, you will need to use the array formula.

So, how to turn the Substitution formula into an array formula? Simply add the =ARRAYFORMULA command before the SUBSTITUTE command and put the substitute formula in parentheses.

Example: =ARRAYFORMULA(SUBSTITUTE(C1:C2, “J”, “John”))

After you use this, the text will get replaced with what you won’t get the error an array value could not be found.

Solution 2: Use The REGEXMATCH Formula Instead

Other than the SUBSTITUTE formula, you can also turn the REGEXMATCH formula into the MS Office or Google Docs array formula to get the job done. To do it, follow the steps given below:

1. Open Google Sheets or Microsoft Excel.
2. Select the Cell you want to add the formula.
3. Paste the formula with the following format in the Formula bar:
   ArrayFormula(if(REGEXMATCH(C1:C4,”^Jo|jo|J|j”)=true,”John”))
4. This formula will replace Jo, jo, J, j to John for cell range C1:C4. Change the formula depending on how you want it to use, including the cell range.

Solution 3: Use The REGEXREPLACE Formula

You can also use the REGEXREPLACE formula in the MS Office or Google Docs array formula instead of SUBSTITUTION and REGEXMATCH.

To use it, follow the steps given below:

1. Open Google Sheets or Microsoft Excel.
2. Select the Cell you want to add the formula.
3. Paste the formula with the following format in the Formula bar:
   ArrayFormula(regexreplace(” “&C1:C4&” “,” Jo | J | j “,” John”))
4. This formula will replace Jo, J, j to John for cell range C1:C4. Change the formula depending on how you want it to use, including the cell range.

Wrapping Up

So, there you have it. Now you know how to fix the an array value could not be found error on Google Sheets and Microsoft Excel. If you have any questions regarding this topic, ask them in the comment section below.