Using Microsoft Excel enables us to organize ours data an ext efficiently. V Google Sheets, friend can immediately save the file on the cloud and accessibility it at any time on any type of device.

You are watching: An array value could not be found

However, some individuals encounter the “An variety Value might not it is in Found” error message once they try to modify the cells on a Microsoft Excel or Google Sheets spreadsheet. If you suffer the same issue, there are several means on just how you can fix it.

How to solve ‘An variety Value could Not be Found’ Error in Microsoft Excel or Google Sheets

*
Screenshot: windows Dispatch

An selection consists of several rows and also columns v values. Girlfriend can group your cells utilizing arrays. To settle the concern with the variety value on windows 10 or Mac computer or any type of browser, you deserve to use any kind of of the formulas proclaimed below.

Method#1 – include the ARRAYFORMULA word to your Substitute Formula

To perform this method, friend will need to add ARRAYFORMULA in ~ the beginning of her Substitute Formula.

On her Microsoft Excel or Google Sheets, look for the cell with the substitute formula.Click ~ above it and also go come the Formula bar.After the same Sign, add the word, ARRAYFORMULA. Because that instance, =ARRAYFORMULA(SUBSTITUTE(A1:A2, “N”, “No”))

Method #2 – use the REGEXMATCH Formula

Another method you deserve to use on your Microsoft Office Excel or Google Sheets is the REGEXMATCH formula.

Look because that the cell the you want to edit and highlight it.Go come the Formula bar.Enter the following: =if(REGEXMATCH(E2,”^Yes|yes|Y|y”)=true,”Yes”)

Method #3 – exploit REGEXREPLACE

If friend don’t desire to usage REGEXMATCH, friend can also use REGEXREPLACE. The method is the same.

First, to mark the cell that you want to change.On the Formula bar, form the following: =ArrayFormula(regexreplace(” “&B2:B4&” “,” yes | Y | Y “,” Yes”))

Method #4 – integrate ARRAYFORMULA and also REGEXMATCH

There is an additional formula that provides both ARRAYFORMULA and REGEXMATCH.

Click the cell the you want to edit.On the Formula bar, get in the following: =ArrayFormula(if(REGEXMATCH(E2:E50,”^Yes|yes|Y|y”)=true,”Yes”))

Note the you can always edit the formula based upon your preferences.

See more: Oh My Love My Darlin I Hunger For Your Touch, Ghost: Unchained Melody

Which of the methods above work because that you best? You have the right to let us know by writing united state a comment down below.