How to extract a list of records with certain criteria met in one table while identifiers extracted from another table?

In Microsoft Excel, I have a table named TableTwo, with fields:

• tName
• dDate
• tStatus

tName is key-identifier of the records.

We extract a list of tName, named tNames, from table TableOne. Then for each tName in tNames, we want to find records from TableTwo that its dDate is the maximum value of corresponding records with this tName, and their tStatus is not Accepted.

TableOne

TableTwo

Final Results:

I've tried to use LET and Filter function, first finding tNames, then inspecting TableTwo to find records that met criterias.

Attempt 1 — Output: #N/A

=LET(
    tNames, FILTER(TableOne[tName],(TableOne[Category]="cat1"),"NoAsset"),
    dDates, TableTwo[dDate],
    tStatuses, TableTwo[tStatus],
    maxDates, MAXIFS(TableTwo[dDate], TableTwo[tName], tNames),
    result, IF((TableTwo[dDate]=maxDates)*(TableTwo[tStatus]<>"Accepted"), TableTwo[tName], ""),
    FILTER(result, result<>"")
)

Attempt 2 — Output: #Value!

=LET(tNames,FILTER(TableOne[tName],(TableOne[Category]="cat1"),"NoAsset"),
cntr, SEQUENCE(ROWS(tNames)),
rngTwo,FILTER(tNames,(TableTwo[tName]=INDEX(tNames,cntr))*(TableTwo[dDate]=MAXIFS(TableTwo[dDate],TableTwo[tName],INDEX(tNames,cntr)))*(TableTwo[tStatus]<>"Accepted")),
r,SEQUENCE(ROWS(rngTwo)),
output,INDEX(rngTwo,r),
output)

To get the desired output, here is what you should do, refer the following formula:

---

=LET(
     a, FILTER(TableOne[tName],TableOne[Category]="cat1","NoAsset"),
     b, MAXIFS(TableTwo[dDate],TableTwo[tName],a),
     c, (1-ISNA(XMATCH(TableTwo[dDate]&TableTwo[tName],b&a))),
     FILTER(TableTwo,c*(TableTwo[tStaus]<>"Accepted")))

---

In place of 1-ISNA( one can also use ISNUMBER(

---

You can also use MMULT() function:

=LET(
     a, FILTER(TableOne[tName],TableOne[Category]="cat1","NoAsset"),
     b, MAXIFS(TableTwo[dDate],TableTwo[tName],a),
     c, MMULT((TableTwo[tName]=TRANSPOSE(a))*
              (TableTwo[dDate]=TRANSPOSE(b))*
              (TableTwo[tStaus]<>"Accepted"),
        {1;1;1}),
     FILTER(TableTwo,c))

---