# Help with a big formula using and, xlookup, if and sumifs

Hello all! I'm working on a report (per company policy can't share) on which I would like to optimize with formulas. I made a dummy example as close as I could so I could explain myself better for a specific case I'm having trouble with.

Looking to build a formula to do the following:

Search for the ID number on the master list ONLY IF:

On column [Repeated?] it has FALSE, AND on column [MODE] it has anything other than "COLLECT" / Otherwise return a 0.

-If found, retrieve the COST **(if the number repeats then sum all costs for matching ID)**

-If the ID is not on the master list then the cost must be calculated:

Sum the weight on all matching IDs and multiply that for the corresponding RATE matching the MODE

I'm showing what I have built so far, and the cost calculation works but cannot make the highlighted part work/not sure how to add it.

=IF(AND(E2=FALSE,C2<>"collect"),XLOOKUP(B2,L:L,M:M,XLOOKUP(C2,I:I,(J:J)*((SUMIF(B:B,B2,D:D))),FALSE,0,1),0,1),0)

Also, I would like to implement a minimum for below cases:

For GROUND IDs total cost may not be below 5.28, if the calculation falls below that it must return 5.28

For LTL IDs total cost may not fall below 86.11 so it must return 86.11 if it does.

I'm open to all suggestions as well as questions if something is not clear enough.

Thanks in advance!!

If I understood the logic correctly that could be

`=LET( getSum, IF( ISNA( XMATCH([@ID],MasterList[Master list]) ), XLOOKUP([@MODE], Rates[MODE], Rates[RATE]*SUMIF([ID],[@ID],[WEIGHT]), FALSE,0,1), SUM( FILTER( MasterList[Cost], MasterList[Master list] = [@ID]) ) ), adjustedSum, IFS( [@MODE] = "Ground", MAX( 5.28, getSum), [@MODE] = "LTL", MAX( 86.11, getSum), TRUE, getSum), IF( AND( [@[Repeated?]]=FALSE, [@MODE]<>"collect"), adjustedSum, 0) )`