Why is my XLOOKUP, with a nested MAXIFS, unable to give me an exact match?

Question

First off, here is my formula:

=XLOOKUP(MAXIFS(D:D,E:E,">0"),(B:B=F2)*(A:A="EFP")*D:D,C:C,,-1)

CAMPAIGN	RECORD PHONE#	REP #	PAYMENT DATE	PAYMENT AMT	LEAD PHONE NUMBER	LASTREP
MOL	4255555666	3608	7/22/2022	$3000	4255555666
EFP	4255555666	3608
BHS	4255555666	1156	5/22/2009	$3000
BHS	4255555666	1156	2/3/2007	$2000

What this is doing is finding the last representative that sold a lead in a specific campaign and had it pay.

My issue is that if there is no match, it is giving me a random representative instead of n/a or blank, and I can't set exact match because it is breaking the formula.

How can I use this code to get an exact match?

Answer 1

So, I actually got the desired results by using a take/sort/filter method.

=IFERROR(TAKE(TAKE(SORT(FILTER(C2:D99,(B2:B99=H2)*(A2:A99="efp")*(D2:D99>0)),2),-1),,1),"")

Worked like a charm, thanks everyone!

Answer 2

Last Max Lookup

=LET(Data,A2:E7,scCol,1,spCol,2,srCol,3,sdCol,4,saCol,5,
        dc,H1,dp,H2,na,NA(),
    sc,INDEX(Data,,scCol),sp,INDEX(Data,,spCol),sd,INDEX(Data,,sdCol),
    sData,HSTACK(INDEX(Data,,srCol),INDEX(Data,,saCol)),
    sf,FILTER(sData,(sc=dc)*(sp=dp)*(sd<>""),na),
    sr,INDEX(sf,,1),sa,INDEX(sf,,2),
    dr,TAKE(FILTER(sr,sa=MAX(sa)),-1),
dr)