Search code examples
if-statementexcel-formulamax

Trying to add a "highest date" criteria in the middle of an IF formula

I am writing a formula right now, and hit a bit of a snag.

Here is the base formula: =IF(B:B=J2,IF(A:A=L2,IF(E:E>0,C:C))))

What my formula is doing is match the phone number(B:B=J2) and the camp(A:A=L2) to find the employee number(C:C) that sold it as long as the payment amount is above zero(E:E>0).

I also want to make sure it is only giving me the employee number that sold it the most recently, so having the highest value in the D:D column.

picture of my workbook

I tried to add an If statement that was MAX(IF(S:S) in the middle of the formula but I knew that wasnt right and it didn't work. What am i missing?

Solution

  • Figured out that I should actually be using an xlookup function for this. BAsed on the data above, this formula helped me achieve my results.

    =XLOOKUP(MAX(D:D),(B:B=J2)*(A:A=L2)*D:D,C:C,,-1)