Match Index Multiple Criteria

[LisaMarie1981]

Hi there! Hoping someone can help me. On the attached spreadsheet on the Certainli Revenue tab I need a formula in column P that multiplies column N by the agents splits which are in the 'Splits' Tab if it matches the agents name and the lead channel (column J and O in the Certainli Revenue tab/columns A & B in the 'Splits' tab), BUT if column O says 'Fee Only' it returns a zero value. So for example, row 3 would be $10,000 * 80% because the agents name is Eric and his split is 80% on Self-Gen lead channel. Whereas row 10 would be 0 (zero) because it is a 'Fee Only' lead channel

Thanks in advance!!

[#2 · Accepted solution]

@LisaMarie1981 

=IFERROR(IF([@[Lead Channel]]="Fee Only",0,INDEX(Splits!$C$3:$C$44,MATCH(1,([@[Agent Name]]=Splits!$A$3:$A$44)*([@[Lead Channel]]=Splits!$B$3:$B$44),0)))*[@[Brokerage Total Commission]],"no match")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

Currently the formula returns "no match" if there isn't a match in worksheet "Splits". This can be changed according to your requirement.