Recently I was badly looking for an array formula to convert multiple cell addresses to values in Google Sheets. The Indirect is not capable to do that as you may need to use multiple Indirect for that purpose.

After a few attempts, I could write a formula to do the same! An Indirect alternative formula without across the sheet capability.

We can’t use my formula as an alternative to Indirect in all the cases. But for our purpose, it’s a suitable one.

An example can help you understand the scenario (purpose) better.

Can I replace the Indirect formulas in the range G2:G10 with an array formula in Google Sheets?

Note: In H2:H10, I have used the function FORMULATEXT to show you the formula in the range G2:G10.

The following Indirect formula won’t work as, at present, the Indirect doesn’t support the ArrayFormula function.

=ArrayFormula(indirect(F2:F10))

Indirect Alternative Formula Limitations

We can use an array formula in cell G2 to convert the cell addresses in the range F2:F10 to values in Google Sheets.

I have an array formula to indirect as above in Google Sheets. It has the following ‘immediate’ limitations though.

1. Cell addresses from multiple sheets are not supported.
   • The formula doesn’t have the across sheets capability.
2. Criteria (cell addresses in F2:F10) must be entered in a particular order.
   • I mean, enter the cell addresses from the first row of the range, then from the second row of the range and so on. You can see that I have maintained the same in my range F2:F10.
3. The cell addresses (criteria) and formula must be entered outside the range.
   • See the details below (after point # 4).
4. Slow/no performance in a very large set of data.

I know I must explain the third point in detail. See our above example and the cell addresses in column F.

You can find that all the cell addresses given are from left to column F. That means, if the list in column F contains the cell address H14, then the list must be moved to the column I.

How to Convert Multiple Cell Addresses to Values in Google Sheets

The formula may look complex to you, even though it is not. So let me help you understand the formula by coding it in step by step.

Indirect Alternative Formula Logic

To convert cell addresses to values that without using the Indirect function in Google Sheets, we will follow the below logic.

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

The match will return an array correspond to A1:E with TRUE or FALSE. We will extract the values from the cell corresponding to the TRUE values.

See how I am going to implement this logic in step by step.

Step 1 – Generate Cell Addresses of a Range

In my example, I am just going to use the range A1:E20. So that we can test individual formulas without affecting sheets performance. But in the final formula, we can change A1:E20 to A1:E.

To generate cell addresses of a range we can use the Address formula below.

Syntax: ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])

We must only want to use the first 3 arguments.

The ‘row’ argument must be the first column in the range and the ‘column’ argument must be the first ‘row’ in the range. Then use the ‘absolute_relative_mode’ as 4.

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

Step 2 – Match Cell Addresses Using Regexmatch

In the above cell addresses that returned by the Address formula, we can match our criteria. This is an important step in converting multiple cell addresses to values in Google Sheets.

We are unable to use the Match function here because it’s only to match a criterion or criteria in a single row or column. Ours is a multi-column range.

As an alternative to Match, we can use the more powerful Regexmatch formula.

Syntax: REGEXMATCH(text, regular_expression)

The ‘text’ argument (the range to match) is the above Address formula.

We need to form the ‘regular_expression’ to match by combining the values in the range F2:F10. In the final formula, we can use F2:F instead.

="^"&textjoin("$|^",true,F2:F10)&"$"

I’ll show you the result too.

^A1$|^B1$|^A2$|^B2$|^C7$|^C11$|^E11$|^A13$|^D13$

I have used the Textjoin function to combine the criteria. It inserts three characters as the delimiter – caret, dollar, and pipe.

Other than this, using ampersands, additionally added a caret sign at the beginning and dollar sign at the end of the combined criteria.

Related: Exact Match Using Regular Expression in Google Sheets (caret and dollar use).

We have now the ‘text’ and ‘regular_expression’ to use in the Regexmatch and here is the formula.

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

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

Here is the final step.

Now we can easily convert multiple cell addresses to values using an IF logical statement.

Syntax: =ArrayFormula(if(step_2_formula_output=true,A1:E20,))

It will be like;

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

But the values will be scattered. We can format it by modifying the above formula using Textjoin, Split and Transpose.

Use =ArrayFormula(transpose(split(TEXTJOIN("|",1, before the IF and ,"|"))) at the end of the formula.

Final Formula in cell G2:

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

Note: As mentioned above, if you want, you can replace A1:A20, F2:F10, and A1:E20 with A1:A, F2:F, and A1:E respectively.

Real-life Use Example

Since the Indirect is not useful for array use, we can follow the above method in some real-life scenarios. Here is such one.

• How to Find the Last Value in Each Row in Google Sheets.

That’s all. Enjoy!