Returning data from another sheet matching on two criteria

I am trying to create a report which brings back the actual performance for performance measures for the last month, 2 months ago and three months ago. But am stumped as to how i can do a lookup to the base data sheet using two criteria (MeasureID and where the date is last month etc.)

The base data sheet has a row per measure per month which holds the target and actual reported by the user. I've created a formula column 'Month' to look at today's date and then give a value based on that rows period (1,2 or 3).

For the Month-1 actual column I want a formula to lookup against the MeasureID and if the month=1 then return the actual for that row. For the Month-2 column, lookup against MeasureID and if the month=2 then return the actual and so on. I've mocked up how I want this to look

Is this possible?! I've managed to do similar but only using COUNTIFS, SUMIFS etc and not returning cell specific data.

Thanks in advance!



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something like this...

    =INDEX(COLLECT({Reference Sheet Target Column}, {Reference Sheet Short Name Column}, @cell = [Measure Name]@row, {Reference Sheet Month Column}, @cell = 1), 1)

Help Article Resources