MAX COLLECT where one range has multiple values

Neil Watson

Hi,

I am using MAX COLLECT to extract from a sheet the most recent date of an event, based on a few criteria. One of the columns can have multiple values (as the source is a multiple value drop down).

The current formula is =MAX(COLLECT({Date}, {Blank}, $[Primary Column]$2, {Section}, [S1]1)) where:

{Date} = date in source range from reference sheet

{Blank} = equipment name from ref sheet, equal to $[Equipment Name & Location]$2

{Section} = area where equipment is from ref sheet, equal to [S1]1

The formula works when {Section} is a single value, but not when there are multiple values in {Section}.

I thought about using CONTAINS for the 2nd criteria but cannot figure it out....any ideas?

Paul Newcome

Try this:

=MAX(COLLECT({Date}, {Blank}, $[Primary Column]$2, {Section}, CONTAINS([S1]1, @cell)))

Neil Watson

The reference sheet captures when equipment is replaced, with the date being important. Sometimes only one piece of equipment is replaced, other times multiple (hence the multi-select drop-down list). The key element is to know when the item of equipment was last replaced, at each (of the 20) locations.

Paul Newcome

Try this:

=MAX(COLLECT({Date}, {Blank}, $[Primary Column]$2, {Section}, CONTAINS([S1]1, @cell)))

Neil Watson

@Paul Newcome this works! Thanks for coming to my rescue mate!

Paul Newcome

Happy to help. 👍️