Remember Me?

#### Menu

#1
March 13th 17, 07:52 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2017 Posts: 2
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!

#2
March 14th 17, 01:12 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2017 Posts: 2
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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Nacer Excel Discussion (Misc queries) 0 August 13th 09 02:58 PM sltchsyi Setting up and Configuration of Excel 0 May 4th 09 01:32 AM hupjack Excel Programming 0 April 9th 09 11:57 PM tarheelfan Excel Discussion (Misc queries) 0 March 25th 09 08:58 PM [email protected] Excel Programming 0 October 2nd 06 03:54 AM

All times are GMT +1. The time now is 05:55 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright ©2004-2017 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017