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]

How about

 =take(sort(filter(hstack(Table1[Equip. SN],Tabke1[Latest Due Date]),Table1[Equip. SN]=[@[Equip. SN]]),2,-1))

and

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

 

[atypicalguy]

Fluff! Good to see your name pop up in the thread again!

Formula 1.) It's not taking it, giving the generic message saying there's a problem with the formula.

Formula 2.) Works like a charm! Are we able to sort it once more by most oldest date first, and then grouped by clinic or is that too many qualifiers?

 

[atypicalguy]

**correction** on Formula 1.) There was a typo that I corrected, now it's saying "You've entered too few arguments for this function"

 

[Fluff]

Missed the last bit,

 =take(sort(filter(hstack(Table1[Equip. SN],Tabke1[Latest Due Date]),Table1[Equip. SN]=[@[Equip. SN]]),2,-1),1,1)

Are we able to sort it once more by most oldest date first

What column is the date in?

 

[atypicalguy]

For the first formula:

=take(sort(filter(hstack(Table1[Equip. SN],Table1[Latest Due Date]),Table1[Equip. SN]=[@[Equip. SN]]),2,-1),1,1)

... it works, but is spitting out the serial numbers instead of the latest date!

For the second formula:
The date column starts in L12 with the header, but will be using the finalized formula above to determine the date they're actually due, based on the "most recent/latest" date

 

[Fluff]

In that case change the 1,1 to 1,2
and

=sort(FILTER(Table1[#All],((Table1[[#All],[Next QAT Due On]]<L21+40))){1,12})

 

[atypicalguy]

=take(sort(filter(hstack(Table1[Equip. SN],Table1[Latest Due Date]),Table1[Equip. SN]=[@[Equip. SN]]),2,-1),1,2)

... is coming back with a #SPILL error, is there a quick formatting fix I can make or is there something else I can try to resolve that error?

=sort(FILTER(Table1[#All],((Table1[[#All],[Next QAT Due On]]<L21+40))){1,12})

... is coming back with "There was a problem with the formula"

 

[Fluff]

The 1st one should be 1,-1 not 1,2

for the 2nd one, can you post some data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[atypicalguy]

That first one is signed, sealed and delivered! Thanks for that!

For the second one, I'm not able to download much of anything on my work computer, so I'll probably have to settle with what I've got from your last suggestion in this thread - but the "settle" is loads better than it was so thanks all the same!