Correct formula to return symbol from another sheet based on 2 criteria

Christine B.

Hi,

I am trying to capture a monthly snapshot of project health. I created an automation to copy rows to a "Helper Sheet" 1x per month and record the snapshot date. I even translated the snapshot date to a numbered month to try to make the formula easier (no luck).

I created "Monthly Snapshot" sheet where I am trying to write a formula to pull the status from the helper sheet based on the date & project id. I am either getting an incorrect argument or unparseable error.

Formula and screenshots below.

Helper sheet:

Monthly Snapshot sheet:

Formula:

=IFERROR(INDEX(COLLECT({Schedule Health}, {Project ID}, [Project ID]@row, {Snapshot Month}, 8))))

I've tried a few variations but am having no luck. Help!

RDRGSJ00

Hello Christine B.,

I set up a sheet to mock yours. I wasn't sure if this is what you needed but I have:
Helper Sheet:

Schedule Health is of type Symbols. In the Monthly Snapshot sheet, I have:

In the Schedule Health is of type symbols as well and has the below formula:

=INDEX(COLLECT({Health}, {ID}, [Project ID]@row, {Month}, 8), 1)

{Health} = the Schedule Health from the Helper Sheet
{ID} = the project ID from the Helper Sheet
{Month} = the month of Snapshot from the Helper sheet

Remember that an INDEX needs the row_index to complete the formula

I have both columns [Schedule Health] and [Scheduled Health] of the same type and the formula works. Let me know if this works for you.