Formula to reference two values to return a third?

Jeff Jensen (LFS)

Hello everyone,

Hoping someone can help. I am wanting to create a formula that, based on existing criteria in two cells, will dynamically return the value in a third cell.

From my "Aggregator/Calculator" grid (screenshot below), from the "Health" column at the far right, I need to return six symbol indicators each to their own separate dashboard widget:

Each of the six "Health" indicators are based on two criteria: the "Most Recent 'Due Date'" and the "Department". The "Most Recent 'Due Date'" helper column cells determine the most recent "Due Date" column cells via this formula: =MAX(COLLECT([Due Date]:[Due Date], [Due Date]:[Due Date], <=TODAY()))

The "Department" column's cells refer to the corresponding "KPI" column via this formula: =KPI@row

So far, the formula in the six "Health" column cells will return the corresponding "Overall Health" column cell's symbol that is in that same row: =INDEX([Overall Health]@row, MATCH([Most Recent 'Due Date']@row, [Due Date]@row)). This is somewhat close, but of course it will only return the "Overall Health" value from the same row. Instead, I need it to return the corresponding "Overall Health" value from the row that matches BOTH the "Most Recent 'Due Date'" value to the corresponding "Due Date" value AND the "Department" value to the corresponding "KPI" value. So for the PQI "Health" indicator at the end of row 44, since the most recent due date is 06/08/2021, it needs to return the value in the [Overall Health]95 cell instead of what is in [Overall Health] 44 (with a prior due date of 06/01/2021). Then in future quarters as we pass their due dates it needs to return that corresponding future quarter's value.

Since the "Overall Health" values can be "Yes" (green checkmark - indicating that area is meeting 75%+ of its goals) "Hold" (yellow exclamation point = that area is meeting 50-75% of its goals) or "No" (red X = meeting 50% or fewer goals), I don't believe I can use COUNTIFS - although it can match multiple criteria, all it can return is Yes or No.

Any help is greatly appreciated. Thanks in advance.

Genevieve P.

Hi @Jeff Jensen (LFS)

Instead of an INDEX(MATCH which only matches on one criteria, you can use an INDEX(COLLECT to specify your Department as well!

Formula structure:

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

Or in your case:

=INDEX(COLLECT([Overall Health]:[Overall Health], [Due Date]:[Due Date], [Most Recent 'Due Date']@row, KPI:KPI, Department@row), 1)

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Jeff Jensen (LFS)

Instead of an INDEX(MATCH which only matches on one criteria, you can use an INDEX(COLLECT to specify your Department as well!

Formula structure:

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

Or in your case:

=INDEX(COLLECT([Overall Health]:[Overall Health], [Due Date]:[Due Date], [Most Recent 'Due Date']@row, KPI:KPI, Department@row), 1)

Let me know if this works for you!

Cheers,

Genevieve

Jeff Jensen (LFS)

Wow - thanks Genevieve P! This solution works perfectly and is elegant to boot. Thanks so much!

Genevieve P.

No problem at all! I'm glad it works for you 🙂