Returning specfic value based on customer X spend which is an array

[calvinsheep]

I have a question on returning X customer's discount percentage Y, based on their Z spend amount.Ie: I have a list of 5000 customers, Various min and max spend and its corresponding discount. For customer John, i can locate John by index match among the list of customers. Then when at customer John, John has 3 different discount structure based on John's spend ($0-$1000 returns 10%, $1001 to $2000 returns 20%, $2001 to $3000 returns 30%). John has spend $1500, therefore the output I am after is 20%. How can I write this formula?I have tried using SUMPRODUCT(Customer array 5000 = John)*(Spend1500=> Min amount)*(spend1500=< max amount)*(discount="" array)
if you could kindly shed some light please, it would be much appreciated thanks.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Customer​

[/td][td]

Min​

[/td][td]

Max​

[/td][td]

Discount​

[/td][td][/td][td]

Customer​

[/td][td]

Spend​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Mike​

[/td][td]

0​

[/td][td]

1000​

[/td][td]

10%​

[/td][td][/td][td]

John​

[/td][td]

1500​

[/td][td]

20%​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Mike​

[/td][td]

1001​

[/td][td]

2000​

[/td][td]

20%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Mike​

[/td][td]

2001​

[/td][td]

3000​

[/td][td]

30%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

John​

[/td][td]

0​

[/td][td]

1000​

[/td][td]

10%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

John​

[/td][td]

1001​

[/td][td]

2000​

[/td][td]

20%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

John​

[/td][td]

2001​

[/td][td]

3000​

[/td][td]

30%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in H2
=SUMIFS(D2:D7,A2:A7,F2,B2:B7,"<="&G2,C2:C7,">="&G2)

Hope this helps

M.

 

[calvinsheep]

Thank You so much Marcelo!! works like a charm... i guess i was tying to over complicate things with SUMPRODUCT - as i remember i used it before for something similiar in the past!
Much appreciated..