Formula to lookup the result in the correct tier according to differnt product

[sheyy88]

Hi,

Can someone teach me how to use excel formula to determine the correct number of Free Gift based on the table below?

For example, if a customer purchase 23 units of Product A, he/she will get 5 units of free gift.

Thank you in advance!

Product	Customer Purchase (unit)	Free Gift (unit)
A	>10	2
A	>20	5
A	>25	10
B	>10	1
B	>20	7
B	>30	18
C	>5	2
C	>10	5
C	>25	15
Example:
Product	Customer Purchase (unit)	Free Gift (unit)
A	23	5
B	14	1
C	30	15

<tbody>
</tbody>

 

[jorismoerings]

Hi and welcome to MrExcel,

try this:

Excel 2016 (Windows) 64 bit
	A	B	C	D
1	Product	Customer Purchase (unit)	Free Gift (unit)
2	A	>10	2
3	A	>20	5
4	A	>25	10
5	B	>10	1
6	B	>20	7
7	B	>30	18
8	C	>5	2
9	C	>10	5
10	C	>25	15
11
12	Example:
13	Product	Customer Purchase (unit)	Free Gift (unit)
14	A	23	5	5
15	B	14	1	1
16	C	30	15	15
Sheet1

Cell Formulas
Range	Formula
D14	{=LOOKUP(B14,IF($A$2:$A$10=A14,VALUE(SUBSTITUTE($B$2:$B$10,">","")),""),IF($A$2:$A$10=A14,$C$2:$C$10,0))}
D15	{=LOOKUP(B15,IF($A$2:$A$10=A15,VALUE(SUBSTITUTE($B$2:$B$10,">","")),""),IF($A$2:$A$10=A15,$C$2:$C$10,0))}
D16	{=LOOKUP(B16,IF($A$2:$A$10=A16,VALUE(SUBSTITUTE($B$2:$B$10,">","")),""),IF($A$2:$A$10=A16,$C$2:$C$10,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sheyy88]

Hi joerismorings, the formula works perfectly! Thanks a lot