XLOOKUP and FILTER: Help Sorting the Results

[atypicalguy]

Hey you fabulous excel champs,

Received fantastic help on my last post so I'm back with two more questions I'm hoping this brilliant community could help me with:

Formula 1.)

=XLOOKUP([@[Equip. SN]],Table1[[#All],[Equip. SN]],Table1[[#All],[Latest Due Date]],,,-1)

I'm using this formula to find the most-recent date an xray machine was last tested, linking/searching/referencing by the equipment serial number. The "-1" works fine, sorting it by the most recent entry it pulls from the raw data/source, but not if I'm putting in older data - it seems like the "-1" interprets this as the most recent, instead of actually using the date.

Is there a MAX( or other function that would work in sorting it by the biggest date it can link with that serial number?

Formula 2.)

=FILTER(Table1[#All],((Table1[[#All],[Next QAT Due On]]<L21+40)))

I'm using this formula to highlight the xray machines that are past due/coming up due for quality assurance testing.

Is there a way to group these clinics by name regardless of date - while still keeping the parameter of +40 days past the present day (L21)?

Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.