Array Formula to Convert Cell Addresses to Values in Google Sheets

GiGiKing·March 11, 2024·0 comments

Recently, I stumbled upon a predicament. I needed to find an array formula to convert multiple cell addresses to values in Google Sheets. Unfortunately, the Indirect function couldn’t fulfill this requirement as it would require multiple Indirect functions to achieve the desired outcome. But after a few attempts, I managed to crack the code! I came up with an Indirect alternative formula that doesn’t support across the sheet capability, but it’s perfect for our purpose.

Let me walk you through an example to help you better understand the scenario. Imagine you have a range of cell addresses in the G2:G10 range, and you want to replace the Indirect formulas with an array formula in Google Sheets. Check out the example below:

Indirect Multiple Cells - Non-Array

In column H2:H10, I used the FORMULATEXT function to display the formulas present in the G2:G10 range. You’ll notice that the following Indirect formula won’t work, as Indirect doesn’t currently support the ArrayFormula function:

=ArrayFormula(indirect(F2:F10))

Now, moving forward, let’s discuss the limitations of the Indirect alternative formula.

Indirect Alternative Formula Limitations

While the array formula in cell G2 can effectively convert cell addresses in the F2:F10 range to values in Google Sheets, it does have a few limitations:

  1. Cell addresses from multiple sheets are not supported.

    • The formula lacks the ability to work across different sheets.

  2. Criteria (cell addresses in F2:F10) must be entered in a particular order.

    • The cell addresses should be entered from the first row of the range, followed by the second row, and so on. You can observe this order in the F2:F10 range.

  3. The cell addresses (criteria) and formula must be entered outside the range.

    • For more insights, refer to the details explained below (after point #4).

  4. The formula might experience slow or no performance with a very large set of data.

To provide a thorough explanation, let’s take a closer look at the third point. Consider the example above and the cell addresses in column F. You’ll notice that all the cell addresses provided are from left to column F. This means that if the list in column F contains the cell address H14, the list must be moved to column I.

Now, let’s dive into the process of converting multiple cell addresses to values in Google Sheets.

How to Convert Multiple Cell Addresses to Values in Google Sheets

Although the formula may appear complex at first glance, I assure you it’s simpler than it seems. Allow me to break down the formula step by step.

Indirect Alternative Formula Logic

To convert cell addresses to values without using the Indirect function, we will follow the logic outlined below:

  1. We will match the cell addresses (criteria) to the corresponding ‘range’. In our example, we will match F2:F10 with the cell addresses in the range A1:E.

  2. The match will return an array corresponding to A1:E with TRUE or FALSE values. From there, we will extract the values from the cells that correspond to the TRUE values.

Now, let’s go through the step-by-step implementation of this logic.

Step 1 – Generate Cell Addresses of a Range

In our example, I will use the range A1:E20 for testing individual formulas without affecting sheet performance. However, in the final formula, we can change A1:E20 to A1:E.

To generate cell addresses of a range, we can utilize the following Address formula:

=ArrayFormula(address(row(A1:A20),column(A1:E1),4))

Convert Cell Addresses to Values - Step # 1

Step 2 – Match Cell Addresses Using Regexmatch

In the cell addresses obtained from the Address formula mentioned above, we can match our criteria. This step is crucial in converting multiple cell addresses to values in Google Sheets.

Unfortunately, we cannot use the Match function in this case since it’s designed to match a criterion or criteria in a single row or column. Our situation involves a multi-column range.

As an alternative to Match, we can employ the powerful Regexmatch formula:

=ArrayFormula(regexmatch(address(row(A1:A20),column(A1:E1),4),"^"&textjoin("$|^",true,F2:F10)&"$"))

Convert Cell Addresses to Values - Step # 2

Step 3 – Formula to Convert Multiple Cell Addresses to Values in Google Sheets

Now, here’s the final step. We can easily convert multiple cell addresses to values using an IF logical statement:

=ArrayFormula(transpose(split(TEXTJOIN("|",1,if(regexmatch(address(row(A1:A20),column(A1:E1),4),"^"&textjoin("$|^",true,F2:F10)&"$")=true,A1:E20,)),"|")))

By adding =ArrayFormula(transpose(split(TEXTJOIN("|",1, before the IF and ,"|")))) at the end of the formula, we can format the scattered values using Textjoin, Split, and Transpose.

The formula will be:

=ArrayFormula(transpose(split(TEXTJOIN("|",1,if(regexmatch(address(row(A1:A20),column(A1:E1),4),"^"&textjoin("$|^",true,F2:F10)&"$")=true,A1:E20,)),"|")))

Please note that you can replace “A1:A20,” “F2:F10,” and “A1:E20” with “A1:A,” “F2:F,” and “A1:E,” respectively, as mentioned earlier.

Real-life Use Example

Although the Indirect function can’t be used for array purposes, we can apply the above method in various real-life scenarios. One such example is finding the last value in each row in Google Sheets.

And there you have it! Follow the steps outlined above, and you’ll be able to convert multiple cell addresses to values in Google Sheets. Enjoy exploring the possibilities!

This article was written exclusively for Crawlan.com.

GiGiKing

Related posts