How do I use INDIRECT inside an Excel array formula?

3
votes

The situation

  • In the sheet "Planning" I have an area that contains pairs of sessions (strings) and hours (numbers) in adjacent cells (e.g. D11 and E11, I12 and J12 etc.) One session can occur multiple times.
    • D11:E11 is | Foo | 8 |
    • I12:J12 is | Foo | 4 |
  • In another sheet, I want to find a session in the Planning sheet and return an array with all the hours booked on that session (to calculate a total)
  • I use an array formula with a conditional and intend to use the SMALL function to retrieve the results from the array

The problem

The following formula returns all the correct references to hours booked on "Foo", so far so good.

=IF(Planning!$D$11:$CV$18="Foo";ADDRESS(ROW(Planning!$D$11:$CV$18);COLUMN(Planning!$D$11:$CV$18)+1;;;"Planning"))

{"Planning!$E$11"\FALSE\FALSE\FALSE\FALSE\"Planning!$J$12"}

However, if I use the INDIRECT function to retrieve the values of those references, they always return the value of the first reference in the array ("Planning!$E$11")

=IF(Planning!$D$11:$CV$18="Foo";INDIRECT(ADDRESS(ROW(Planning!$D$11:$CV$18);COLUMN(Planning!$D$11:$CV$18)+1;;;"Planning")))

{8\FALSE\FALSE\FALSE\FALSE\8}

How do I retrieve the correct values? Or should I tackle the problem in a whole different way?

Screenshots

1
excelarray-formulasexcel-indirect
I'm not sure indirect is what you want to use. Difficult to say though, do you have an example (screenshot or something similar) of the data and expected outcome? - a-burge
The way you are using indirect currently, is finding the value of the first cell in your reference range (e11 being one column to the left of d11). - a-burge
Thanks. I added the screenshots. By the way, wrapping the INDIRECT around the array is not a solution either: all FALSE Booleans are converted to #REF!, which renders the array unusable for SMALL. - krowl
Due to the format of the data, it would not be possible to generate the overview you are aiming to, however, you can use formulas to generate a consolidation table then use a PivotTable to generate the desired summary. - EEM

1 Answers

0
votes

Since I was mainly interested in the total of planned hours, I eventually used the following formula:

=SUM(SUM(INDIRECT(IF(Planning!$D$11:$CV$18="Foo";(ADDRESS(ROW(Planning!$D$11:$CV$18);COLUMN(Planning!$D$11:$CV$18)+1;;;"Planning"));"$U$19"))))

  1. IF: Create the array with references to the Planning sheet if the string is found. If it's not found, add the reference $U$19.
  2. Using INDIRECT, replace all references with the values in the Planning sheet. $U$19 contains the value 0.
  3. Then use SUM twice to sum up all the values. I don't know why, but see