index match formula not returning what I intended it

Agata Wozniak

Dear All,

Could you please help me with this formula?

=INDEX({Project Plan - Task name}, MATCH([Start Date]@row, {Project Plan Range 1}, 0))

I wanted to show TASK NAME (from project plan) on the RACI file.

What the formula seems to be returning is the name of item in either the Parent or the Tollgate/Phase column.

Thank you in advance!

Paul Newcome

Can you show the date column in the project plan as well?

Agata Wozniak

Hi @Paul Newcome , yes of course!

Agata

Paul Newcome

So the formula is in fact pulling from the Task Name column, and it is actually operating as expected. The MATCH function will stop on the first match. The first match for 01/10/23 is the dark blue row with "Detailed Project Plan" in it.

What you need is an INDEX/COLLECT to include a filter for the level.

=INDEX(COLLECT({Task Name}, {Start Date}, @cell = [Start Date]@row, {Level}, @cell = 1), 1)

Just change that first 1 to a 2 if you need to pull from the next level down.

=INDEX(COLLECT({Task Name}, {Start Date}, @cell = [Start Date]@row, {Level}, @cell = 2), 1)