In 1 sentence, looking for: Excel 2021 1 Cell LET formula + exception handling to prevent duplicate as close match UNIQUE, that maybe an exclusion due to: DATE, WORD, NUMBER and/or PART WORD-NUMBER.

Answers in Excel 2021 please, not V STACK, as only 365. Typical suggestions of LET Formula with edits for exceptions, coherent to read & edit, are useful, not expecting you to examine carefully, cheers.

DataSet: list of exam results, persons retaking at later date, some passing 2nd time, hence duplicate example, hence UNIQUE lists twice, hence how to compile final list of test results (pass + fail final) in LET, more generic editable then MAXIF.

Screenshot of worksheet + dataset LET 1 cell with criteria & exception for LET UNIQUE

Attempt #1: LET (works)

1 cell LET, correctly removes non UNIQUE, MAXIF compares UNIQUE entries date & returns latest result. Looking for more readily customised, if wasn't date order based.

=LET(f, FILTER(DATA, DATE = MAXIFS(DATE, FIRST, FIRST, LAST, LAST)),
     SORT(CHOOSE({1,2},INDEX(f,,1)&" "&INDEX(f,,2),INDEX(f,,4))))

Question: how to edit LET with multiple date criteria, and exclusions, to specify what is right in more generic way. Suggestions are welcome, other possibilities of WORD, NUMBER or PART WORD.

Appreciate answer maybe custom edit depending on dataset, I'm looking for more GENERIC LET formula (An alternative to MAXIF within this thread) to exclude duplicates, hence construct those exceptions, instead of just SPILL those of newest date.

Attempt #2 LET (not accurate - unique duplicates)

1 cell LET with date range, to edit! Currently displays duplicates as same name fail 1st time & passed 2nd time, MAXIF method works, how to make it more generic to include or exclude criteria & what to do list as results for those instances: WORD, NUMBER, DATE, REPITITION & what to list in that occasion.

=SORT(LET(data,DATA,startDate,AA14,endDate,AB14,FILTER(data,(DATE\>=startDate)\*(DATE\<=endDate)\*(MUSIC="PASS")+(MUSIC="FAIL"),"No Data")))

Above is LET question + formulas presented 1st, initial code, are following, explaining separate spills.

Imagine 2 categories NAME & EXAM: FAIL/PASS is there, but is there was no DATE FIELD, just list of PERSONS with FAIL and/or PASS, how to create UNIQUE LET 1 CELL SPILL list based on what to do for those exceptions, that is the QUESTION.

Lack familiarity of LET to add more to its basic construct. Appreciate separate VARIATION 3 the take-away method works, prefer not to have that clutter, VARIATION 1 lacks for me to edit easy multiple exclusions or inclusions, of what to do in exceptions, for other data sets, hence the VARIATION 2 attempt. I will repeat the question being asked, amongst workings, as I recognise the clarity will become vague if lots to read & look at.

Initial method below is of column LIST: B-A=C take-away works via ISNA MATCH. Perhaps there is LET FORMULA 1 cell equivalent without MAXIF, of what is essentially same question, but with different NAME RANGES, CELL REFS, DV COMBOs + exception constraints, ie if duplicates, etc

It feels like there is a simple solution, I just haven't got the knowledge and/or skill in LET FORMULA to construct it from blank fresh on my own.

QUESTION is how to GENERIC edit LET in 1 COLUMN to handle to CRITERIA with common EXCEPTIONS.

My initial approach for dataset is following, with correct result, same conclusion as LET 1 CELL with MAXIF.

VARIATION 3 (Multi Spills, long way, accurate):

End calc is LET merging 2 columns (PASS + FAIL final)

List #1: (pass list)

=SORT(UNIQUE(FILTER(CHOOSE({1,2},
                    FIRST & " " & LAST, MUSIC), (MUSIC = "PASS") * (MUSIC <> "WATEVA")
)), {1, 2}, {TRUE, TRUE})

List #2: (fail list)

=SORT(UNIQUE(FILTER(CHOOSE({1,2},
                    FIRST & " " & LAST, MUSIC), (MUSIC = "FAIL") * (MUSIC <> "WATEVA")
)),{1, 2}, {TRUE, TRUE})

List #3: (fail list correct). Cell Refs, as refers lists single columns! Didn't realise!

=UNIQUE(FILTER(C16:D18,ISNA(MATCH(C16:C18,A16:A20,0)\*0)))

List #4: FINAL Correct Pass + Fail list

=SORT(UNIQUE(LET(a, F16#, b, A16#, ra, ROWS(a), rb, ROWS(b),
                 rsq, SEQUENCE(ra + rb), csq, SEQUENCE(, COLUMNS(a + b)),
                 out, IFS(rsq <= ra, INDEX(a, rsq, csq), 
                 rsq <= ra + rb, INDEX(b, rsq - ra, csq), TRUE,), out)))

Excel 2021, it is without VSTACK hence to MERGE 2 COLUMNS, hence the LET FORMULA MERGES 2 COLUMNS in 1 CELL SPILL, other aforementioned are exclusive to OFFICE 365 XL.

To conclude my END RESULT WANTED is to complete:

VARIATION 2, as is just still an attempt at compiling LET MAXIF alternative of something more generic, comparison operator of TEST = PASS + FAIL, but it lacks the ability to handle exceptions of UNIQUE, which is my real question here.

Unless it requires some bespoke custom FORMULA everytime, but I was hoping to have some easily customised comparison ops style of: WORD, NUMBER, CODE, PART WORD/NUMBER, etc.