CODEWITHSUNDEEP
excelexcel-formulaexact-matchxlookupmaxifs
cheese10234
cheese10234

Reputation: 127

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

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?

Upvotes: 1

Views: 279

Answers (2)

VBasic2008
VBasic2008

Reputation: 54757

Last Max Lookup

enter image description here

=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)

Upvotes: 0

cheese10234
cheese10234

Reputation: 127

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!

Upvotes: 0

Related Questions