Excel Help index match or other option

[Mohammed Wahid]

I want to put discount per unit for each qty purchase for its respective category, discount is depend upon category and qty range.

Discount range table

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"]
Category[/TD]
[TD="class: xl67, width: 64"]Min QTY[/TD]
[TD="class: xl67, width: 64"]Max Qty[/TD]
[TD="class: xl65, width: 64"]$ Disc /unit[/TD]
[/TR]
[TR]
[TD="class: xl68"]A[/TD]
[TD="class: xl69, width: 64"]1[/TD]
[TD="class: xl69, width: 64"]5[/TD]
[TD="class: xl70, width: 64"]$1 [/TD]
[/TR]
[TR]
[TD="class: xl71"]A[/TD]
[TD="class: xl72, width: 64"]6[/TD]
[TD="class: xl72, width: 64"]10[/TD]
[TD="class: xl73, width: 64"]$2 [/TD]
[/TR]
[TR]
[TD="class: xl74"]A[/TD]
[TD="class: xl75, width: 64"]11[/TD]
[TD="class: xl75, width: 64"]11 +[/TD]
[TD="class: xl76, width: 64"]$3 [/TD]
[/TR]
[TR]
[TD="class: xl77"]B[/TD]
[TD="class: xl78, width: 64"]1[/TD]
[TD="class: xl78, width: 64"]5[/TD]
[TD="class: xl79, width: 64"]$2 [/TD]
[/TR]
[TR]
[TD="class: xl74"]B[/TD]
[TD="class: xl75, width: 64"]6[/TD]
[TD="class: xl75, width: 64"]10[/TD]
[TD="class: xl76, width: 64"]$3 [/TD]
[/TR]
[TR]
[TD="class: xl71"]B[/TD]
[TD="class: xl72, width: 64"]11[/TD]
[TD="class: xl72, width: 64"]11 +[/TD]
[TD="class: xl73, width: 64"]$4 [/TD]
[/TR]
[TR]
[TD="class: xl68"]C[/TD]
[TD="class: xl69, width: 64"]1[/TD]
[TD="class: xl69, width: 64"]5[/TD]
[TD="class: xl70, width: 64"]$3 [/TD]
[/TR]
[TR]
[TD="class: xl71"]C[/TD]
[TD="class: xl72, width: 64"]6[/TD]
[TD="class: xl72, width: 64"]10[/TD]
[TD="class: xl73, width: 64"]$4 [/TD]
[/TR]
[TR]
[TD="class: xl80"]C[/TD]
[TD="class: xl81, width: 64"]11[/TD]
[TD="class: xl81, width: 64"]11 +[/TD]
[TD="class: xl82, width: 64"]$5

[/TD]
[/TR]
</tbody>[/TABLE]

-------------------------------------------------


Here, in $ Disc / Unit column i want it to read from above table

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Category[/TD]
[TD="class: xl65, width: 64"]QTY[/TD]
[TD="class: xl66, width: 64"]$ Disc /unit[/TD]
[/TR]
[TR]
[TD="class: xl68"]A[/TD]
[TD="class: xl69"]2[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]B[/TD]
[TD="class: xl69"]4[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]C[/TD]
[TD="class: xl69"]2[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]A[/TD]
[TD="class: xl69"]7[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]A[/TD]
[TD="class: xl69"]7[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]A[/TD]
[TD="class: xl69"]16[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]B[/TD]
[TD="class: xl69"]7[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]C[/TD]
[TD="class: xl69"]5[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]A[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]C[/TD]
[TD="class: xl69"]13[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]A[/TD]
[TD="class: xl69"]6[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]C[/TD]
[TD="class: xl69"]9[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]B[/TD]
[TD="class: xl69"]3[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]A[/TD]
[TD="class: xl69"]4[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl68"]C[/TD]
[TD="class: xl69"]3[/TD]
[TD="class: xl67"][/TD]
[/TR]
</tbody>[/TABLE]


Kindly help me out on this.

 

[Peter_SSs]

Welcome to the MrExcel board!

This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D
1	Category	Min QTY	Max Qty	$ Disc /unit
2	A	1	5	1
3	A	6	10	2
4	A	11	11 +	3
5	B	1	5	2
6	B	6	10	3
7	B	11	11 +	4
8	C	1	5	3
9	C	6	10	4
10	C	11	11 +	5
11
12
13
14	Category	QTY	$ Disc /unit
15	A	2	1
16	B	4	2
17	C	2	3
18	A	7	2
19	A	7	2
20	A	16	3
21	B	7	3
22	C	5	3
23	A	1	1
24	C	13	5
25	A	6	2
26	C	9	4
27	B	3	2
28	A	4	1
29	C	3	3
Discount