I've tried to do it using something like:

=UNIQUE(query(J2:L,"select J, K, MAX(L) where K matches 'Pending' or K matches 'Finished' group by J, K, L"))

but it doesn't get the unique values, as the expected result shows:

Here is a test

CodePudding user response：

With one QUERY you can't limit to one result per item in J. One option is to use a formula like this:

=filter({unique(J3:J),byrow(unique(J3:J),LAMBDA(each,query(J3:L,"Select K,L where J = "&each&" order by L desc limit 1",0)))},unique(J3:J)<>"")