CODEWITHSUNDEEP
libreoffice-calc
hiigaran
hiigaran

Reputation: 267

Returning latest date of column range matching cell criterion in spreadsheet

Example spreadsheet data provided below:

Date Item Event
22-01-01 A X
22-02-01 B Y
22-03-01 C X
22-04-01 A X

Using this data, I would like to create a table showing the latest date of each item with event X in this format:

Item Latest X
A 22-04-01
B None
C None

The cells in the Latest X column are where I'd need to put the formulae, while the cells under Item are manually added.

VLOOKUP doesn't work here. If the criteria are that the value of the Item column must be associated with a X event (specific event, manually set), and the search array is the first table above, the expected data won't get returned.

Perhaps some sort of MAX formula, where a range meets the aforementioned criteria? Sounds like I'd need to get into arrays to figure this one out, but I'm not familiar with how they work just yet. So some sort of "If ITEM = A & EVENT = X, return the latest (MAX) date from range A2:A5"?

Upvotes: 0

Views: 423

Answers (1)

JohnSUN
JohnSUN

Reputation: 2539

Replace "Latest X" in header with X and use IF() and MAXIFS() in formula like this:

=IF(MAXIFS($A$2:$A100;$B$2:$B100;$E2;$C$2:$C100;F$1)=0;"None";MAXIFS($A$2:$A100;$B$2:$B100;$E2;$C$2:$C100;F$1))

Example

Or use Pivot Table:

Example2

Upvotes: 1

Related Questions