CODEWITHSUNDEEP
excelexcel-formula
Anthony
Anthony

Reputation: 11

formula that matches against multiple dates and grabs the most recent

I have a data table with two columns. The first column has a list of Project IDs, and the second column has a bunch of dates associated with those projects. A project can have multiple dates associated with it.

I would like to create a separate summary table of two columns. The first column will be a list of unique Project IDs (I've been able to do this with an index/match function). I want the second column to search the dates column and identify the most recent date associated with each project.

Is it possible to create this second column of my summary table using standard excel formulas and without using any VBA? After an hour or two, I'm not convinced that this is possible.

I was hoping that, for a given project ID, there might be a way to do the following:

--> identify the row numbers for all rows that contain a given project ID;

--> use this row number information to grab the corresponding cell values from the dates column (presumably by first constructing a list of cell references)

--> display the max date out of those that are returned.

What my spreadsheet looks like

Upvotes: 1

Views: 1151

Answers (1)

user4039065
user4039065

Reputation:

The AGGREGATE¹ function can quickly calculate a pseudo-MAXIF function.

In E2 as a standard formula,

=AGGREGATE(14, 6, (B$1:INDEX(B:B, MATCH(1E+99,B:B)))/(A$1:INDEX(A:A, MATCH(1E+99,B:B ))=D2), 1)

Fill down as necessary.

Like the SUMPRODUCT function, AGGREGATE benefits from referencing the minimum number of rows necessary. The MATCH(1E+99,B:B) truncates each column referenced by the INDEX function at the extents of the daes in column B.

¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.

Upvotes: 1

Related Questions