SOLVED

# Match Index Multiple Criteria

Brass Contributor

# Match Index Multiple Criteria

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

best response confirmed by LisaMarie1981 (Brass Contributor)
Solution

# Re: Match Index Multiple Criteria

``=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.

1 best response

Accepted Solutions
best response confirmed by LisaMarie1981 (Brass Contributor)
Solution

# Re: Match Index Multiple Criteria

``=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.