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!
|