Forum Discussion
Solved
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...
=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.
=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.