Search code examples
excelarray-formulasexcel-indirect

How do I use INDIRECT inside an Excel array formula?

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

Solution

  • 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