Returning data from another sheet matching on two criteria

rlsmith

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

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)