Get most recent instance from multiple sheets

1
votes

I'm working on an excel sheet, and trying to get the most recent occurrence of an event from multiple sheets. I currently have three sheets.

Sheet 1 is called "Recipes", and it lists 9 recipes, each with a unique recipe number:

enter image description here

I have two other sheets, "2017", and "2016", where I list several dates, which recipe was made on that date, and who made it:

enter image description here

enter image description here

Now what I want, is on the Recipes sheet, to list for each recipe how many times it has been made, the last time is was made, and by whom. Like so:

enter image description here

How many times it has been made was pretty easy with a count function. But the most recent made has been trickier. I found this article, which has a function to get the most recent instance of something, which was very helpful. So I used this formula:

=INDEX('2017'!$A$2:$A$10,SUMPRODUCT(MAX(ROW('2017'!$C$2:$C$10)*($A2='2017'!$C$2:$C$10))-1))

To get me when the recipe was made last in 2017, which works quite well:

enter image description here

But as you can see, when it comes to the blueberry muffins, it doesn't work, because that recipe wasn't made in 2017. I need to extend the formula to work on multiple years. I could potentially add more years in the past as well. I thought I could just concatenate multiple of those above functions into a giant MAX function, one for each year, like so:

=MAX(
INDEX('2017'!$A$2:$A$10,SUMPRODUCT(MAX(ROW('2017'!$C$2:$C$10)*($A2='2017'!$C$2:$C$10))-1)),
INDEX('2016'!$A$2:$A$10,SUMPRODUCT(MAX(ROW('2016'!$C$2:$C$10)*($A2='2016'!$C$2:$C$10))-1))
)

But that doesn't work. What's the easiest way to go about this?

2
excelexcel-formula

2 Answers

2
votes

The IFERROR function returns the normal value if it succeeds or a supplied value if it fails. Passing a zero back to MAX should be sufficient.

=MAX(
    IFERROR(INDEX('2017'!$A$2:$A$10,SUMPRODUCT(MAX(ROW('2017'!$C$2:$C$10)*($A2='2017'!$C$2:$C$10))-1)), 0),
    IFERROR(INDEX('2016'!$A$2:$A$10,SUMPRODUCT(MAX(ROW('2016'!$C$2:$C$10)*($A2='2016'!$C$2:$C$10))-1)), 0)
)

FWIW, there may be more efficient pseudo-MAXIF functions. The AGGREGATE function's SMALL subfunction would allow you to get the latest, second latest, third latest, etc.

1
votes

The easiest is to have all data in one range.

Another option could be a chain of IFERROR functions, but that will get a bit messy over time:

= IfError( Index2007, IfError( Index2006, IfError( Index2005, ) ) )

There are also other more advanced non-formula options like Power Query or Microsoft Query that I think are a bit more appropriate, but might be harder to learn.