Tricky formula to match data in 3 column or disregard a column if not applicable

[tml2424]

I need assistance with a formula to look up 3 criteria - Job, State and Location (if applicable) otherwise if Location is not applicable, then only pull based on Job and State. Basically, I'm trying to pull in rates for different locations that have a unique rate (ie: Location 1 1) but for all other locations in Arizona (Location 2) it would disregard column L and just use the standard rate in column N. Column L throws me off since I want to say that if the name is not listed in column L and it's blank, then just use the standard rate.

I've already tried a sumproduct formula and an index match array but neither fully work. Although they both seem sound and logical, they don't always pull in the correct data. Please help!!!

=(C$10>=$B11)*SUMPRODUCT(($K$5:$K$18=$A11)*($L$5:$L$18=IF(COUNTIF($L$5:$L$18,$A$5),$A$5,""))*($M$5:$M$18=$A$7)*$N$5:$N$18)

={(IF(C$10>=$B11,INDEX($K$5:$N$18,IFERROR(MATCH($A11&$A$7&$A$5,$K$5:$K$18&$M$5:$M$18&$L$5:$L$18,0),MATCH($A11&$A$7,$K$5:$K$18&$M$5:$M$18,0)),4),"-"))}

[samba_ravi]

try below formula in c11, copy and paste across

Please Login or Register  to view this content.

[tml2424]

Perfect. Thank you!