Index Collect Formula

Allison Horn

Hoping for some insight on the Index Collect Formula.

I have 2 sheets I am working with.

1. Monthly Operational Metrics - this houses the monthly production goal by month in columns
2. Daily Revenue Tracker - this has day-by-day detail, however I want to pull the monthly goal into a row to produce the daily production goal.

Monthly Operational Metrics

As you can see, I have a cell called "Production Goal ($)" in my Metric column and each month is referenced in columns. I have the top row with the referenced Month and Year to lookup purposes as this will become a multiyear document.

Daily Revenue Tracker

In the daily Revenue Tracker, I am trying to pull the Production Goal related to that month and drop it into the Production Goal Column and respective month cell on the Daily Revenue Tracker.

I have a formula but it clearly isn't working correctly. I tried the Index Match, however, it doesn't bring back the right value.

=INDEX(COLLECT({Monthly Operational Metrics | 2022 Production Goal}, {Monthly Operational Metrics | Month Lookup}, Month@row, {Monthly Operational Metrics Range 2}, [Production Goal]1), 1))

A couple of notes:

1. On the Index Collect Range - what should I be selecting? Right now, I have Columns Jan - December on the Monthly Operational Metrics in the Production Goal row. See Illustration 1 for the screenshot.
2. Criterion 1 I have selected Month down to production goal, then the criterion is referencing the Month Column on the Daily Revenue Tracker to find it on the Monthly Operational Metrics in row one. See Illustration 2 for the screenshot.
3. Criterion 2 I have selected the Index Match column I made and referenced the "2022 Production Goal ($) cell under the production goal column. See Illustration 3 for screenshot

Illustration 1

Illustration 2

Illustration 3

Paul Newcome

You do actually need an INDEX/MATCH in this case, but it will be structured just a little different than typically. In this case you want the MATCH in the column portion of the INDEX function and reference rows instead of entire columns.

=INDEX({Production Goal ROW}, 1, MATCH(Month@row, {Matching ROW}, 0))

Paul Newcome

You do actually need an INDEX/MATCH in this case, but it will be structured just a little different than typically. In this case you want the MATCH in the column portion of the INDEX function and reference rows instead of entire columns.

=INDEX({Production Goal ROW}, 1, MATCH(Month@row, {Matching ROW}, 0))

Allison Horn

worked perfectly @Paul Newcome. Thanks again for your help.

Paul Newcome

Happy to help. 👍️