Lookup and Indexing

[Colmans]

Hi

I'm trying to apply a factor (discount) to a pricing calculation that is based on a range of clients across two product ranges. To simplify i've made the example below:

Appreciating that product description, actual qty and the customer will need reference cells, what would be the most efficient way to do this?

Thanks in advance for suggestions

 

[Alex Blakenburg]

See if this works for you

20220527 Lookup Disc Vertical and Horizontal Colmans.xlsx
	A	B	C	D	E	F	G
1		Product A			Product B
2	Qty	0	10	20	0	10	20
3	XYZ Inc	3%	5%	10%	2%	4%	5%
4	ABC Inc	2%	4%	8%	1%	2%	3%
5
6	Discount	2%
7
8	Coy	ABC Inc
9	Qty	11
10	Product	Product B
Sheet1

Cell Formulas
Range	Formula
B6	B6	=INDEX($B$3:$G$4, MATCH($B$8,$A$3:$A$4,0), MATCH(B10,$B$1:$G$1,0)+MATCH(B9,$B$2:$D$2,1)-1)