How to Use INDIRECT with an Array of Cell References in Excel
Question details
The user wants to pass an array or range of text-based cell references to INDIRECT and return the corresponding values without a #VALUE! error.
- Product
- Microsoft Excel and WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has a list of text references such as A1, A2, or Sheet1!A1 and needs INDIRECT to evaluate them all and return multiple cell values.
- Observed behavior
- Passing the array or range directly to INDIRECT returns a #VALUE! error or does not spill the expected values.
Confirm the text references are stored in a worksheet range and verify whether your spreadsheet build supports dynamic array functions such as BYROW, MAP, and LAMBDA before choosing a formula method.
Evaluate each reference with BYROW or MAP in Microsoft 365
Use this method when you have Microsoft 365, Excel on the web, or another build that supports BYROW, MAP, and LAMBDA, and you want a dynamic spilled result.
INDIRECT is designed to accept one text string and return one reference. When you give it an array directly, the formula engine does not always loop through every item, which causes #VALUE! in many versions.
In Microsoft 365, BYROW and MAP can evaluate each text reference through LAMBDA and return the corresponding values as a spilled array.
Click an empty cell where you want the spilled results to start.
Type =BYROW({"A1";"A2"},LAMBDA(ref,INDIRECT(ref))) and press Enter to return the values from A1 and A2.
If the references are in A2:A5, enter =BYROW(A2:A5,LAMBDA(ref,INDIRECT(ref))) and press Enter.
Type =MAP({"A1";"A2"},LAMBDA(ref,INDIRECT(ref))) to map each text reference to its value cell by cell.
Use a helper column with INDIRECT in any Excel or WPS version
Use this method when BYROW or MAP is not available, or when you need a simple, transparent way to evaluate each text reference separately.
Use INDIRECT with Cell References in WPS Office Spreadsheet
Open your workbook in WPS Office Spreadsheet and use the helper-column INDIRECT workflow to evaluate text references one by one, then aggregate the results for your spreadsheet task.
- 1. Open your workbook in WPS Office: Download and install WPS Office, then open the workbook that contains the text cell references.
- 2. Insert a helper column: Right-click the column header next to the references and choose Insert Column.
- 3. Enter the INDIRECT formula: In the first helper cell, type =INDIRECT(A2) or the cell containing the reference text, then press Enter.
- 4. Fill the formula down: Drag the fill handle down to apply INDIRECT to all reference texts.
- 5. Use the returned values: Select the helper column or reference those cells in formulas such as =SUM(B2:B5).
Frequently Asked Questions
Why does INDIRECT return #VALUE! when I pass an array of references?
INDIRECT expects a single text reference. It does not automatically loop through every item in an array in many Excel and WPS builds, so passing an array directly can produce a #VALUE! error.
How do I use BYROW or MAP with INDIRECT?
In Microsoft 365, enter =BYROW({"A1";"A2"},LAMBDA(ref,INDIRECT(ref))) or =MAP({"A1";"A2"},LAMBDA(ref,INDIRECT(ref))) in an empty cell and press Enter.
What can I do if BYROW or MAP is not available in my spreadsheet app?
Insert a helper column, enter =INDIRECT(A2) in the first helper cell, and drag the fill handle down to evaluate each text reference separately.
Does INDIRECT adjust automatically when rows or columns are moved?
No. INDIRECT returns a reference from text and does not have dependency tracking, so it may not adjust if the original referenced cells are moved or deleted.
Can I use this INDIRECT helper-column workflow in WPS Office?
Yes. Open your workbook in WPS Office Spreadsheet, insert a helper column, enter the INDIRECT formula, and fill it down just like in Excel.
