How can I return a value from column C based on a match in column A, and a maximum value within the matched rows from column B?

Question

I have a Numbers sheet for tracking work on podcast editing. The source table includes columns for the episode name, the work start and end times, a work duration calculated from those two, and the final length of the edit (expressed as a duration value) at the end of that session of work. So like this:

Episode	Work start	Work end	Work time	End length
Project 42	08/07/2021 14:43	08/07/2021 15:04	0:21:00	1:06:23
Project 42	08/07/2021 15:59	08/07/2021 16:35	0:36:00	0:58:19
Project 42	08/07/2021 17:34	08/07/2021 18:00	0:26:00	0:59:46

I use another table to compile aggregate data for each individual project, and I want one column to show the final length of each episode once all editing was complete.

As an approximate I used MINIFS to find the shortest end length that matched the episode number. But on many episodes (as in the example above) the final editing task is splicing in additional audio, making the episode longer, so this method is inaccurate.

What I actually need is a column that returns the end length from the row in the source table which both matches the Episode number, and has the maximum (i.e. most recent) work end time.

Is this possible with just the two tables? Or do I need to add in an additional column or interim table to make this work?

Answer 1

You don’t need another table but need to use a formula with a function within a function or at least have an additional column in your aggregates or output table. You can use the MAXIFS() function in Numbers to find the Work end date&time for the latest work on a certain episode, and assuming all date&times in the Work end column are unique, you can use one of the lookup functions such as VLOOKUP() or XLOOKUP() and the result of the MAXIFS() to get the corresponding duration in the End length column.

If this is not helpful enough or if you need more details, I suggest that you prepare an example output table manually, i.e. copy & paste or type the desired or correct values, with at least a few rows and include it in your post.