DigiCruncher

Crunching Digital Bytes

  • Home
  • Gadgets
  • Internet
    • Saas
  • Windows
    • Software
  • Tech
  • Privacy Policy
  • About Us
    • Contact Us
You are here: Home / Software / How To Fix ‘An Array Value Could Not Be Found’ Error On Sheets?

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

February 10, 2022 by Sanmay Chakrabarti Leave a Comment

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?

Contents

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.

Filed Under: Software Tagged With: An Array Value Could Not Be Found

About Sanmay Chakrabarti

Sanmay is a Windows Insider and editor at Digicruncher. He is a Tech enthusiast and has been writing tech blogs for over 2 years now. He loves reading books, traveling to new places and listening to music in his free time.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Digicruncher_Twitter_handle Digicruncher_Facebook_handle Digicruncher_pin_handle Digicruncher_Rss_Feed

Recent Posts

  • How To Fix PS4 Error WS-37398-0?
  • How To Fix PlayStation Error CE-33743-0?
  • FIX: Insignia Fire TV Remote Not Working
  • Fix: Homegroup Icon Won’t Go Away
  • How To Find HomeGroup Password In Windows

20 shares