Sum Product Matrix
 

Hi all,

Trying to get a formula to return the following:

Column A: Title
Column B: Salary 1
Column C: Salary 2
Column D: Salary 3
Column E: Salary 4

Based of of this matrix if Employee n's salary falls in between one of the 4 criteria, then I would like the higher value returned

Example:

A: Manager
B: 1000
C: 5000
D: 10000
E: 15000

My lookup index: Title - Manager / Salary - 8000.

I want this to give me the value in column D. If the salary was increased to 11,000 then column E.

So far I am stuck at:

=sumproduct(--('TitleInput'=$A:$A),--($B:$B<'SalaryInput'),--($C:$C<'SalaryInput'),--($D:$D<'SalaryInput'),($E:$E))

Where I only get a returned value if the Salary input is larger than D and Smaller than E. If I make the value D or smaller I get 0.

Thanks!

Sum Product Matrix
 

I have also tried:

{=INDEX($A:$E,MATCH(1,(A:A=K9)*(IF('SalaryInput'D 2,E2,IF('SalaryInput'C2,D2,IF('SalaryInput'B2,C2 ,B2)))),0),4)}

but get an #N/A error