Trouble using index/match and xlookup

[introspective]

Hello all - first time on here...
I am working with a set of data where I want to select the cost per month based on the product code and the quantity. Depending on the quantity, which sits between 2 numbers, determines the cost. I have tried using index, match and the xlookups and get part of the solution. How do I allocate the right cost depending on the quantity when it sits between the min and max values for each line item? Many thanks for your help on this.

ProductName	ProductCode	CostPerMonth	MinQuantity	MaxQuantity
Product A	12345​	500.000​	1​	100​
Product A	12345​	600.000​	101​	151​
Product A	12345​	700.000​	152​	251​
Product A	12345​	900.000​	252​	751​
Product A	12345​	1000.000​	752​	1501​
Product A	12345​	1050.000​	1502​	3501​
Product A	12345​	2000.000​	3502​	7501​
Product A	12345​	0.400​	7502​
Product B	67890​	2500.000​	1​	100​
Product B	67890​	2600.000​	101​	151​
Product B	67890​	2900.000​	152​	251​
Product B	67890​	3100.000​	252​	751​
Product B	67890​	3200.000​	752​	1501​
Product B	67890​	4000.000​	1502​	3501​
Product B	67890​	5000.000​	3502​	7501​
Product B	67890​	1.500​	7502​

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	ProductName	ProductCode	CostPerMonth	MinQuantity	MaxQuantity		Product	Qty
2	Product A	12345	500	1	100		12345	135	600
3	Product A	12345	600	101	151
4	Product A	12345	700	152	251
5	Product A	12345	900	252	751
6	Product A	12345	1000	752	1501
7	Product A	12345	1050	1502	3501
8	Product A	12345	2000	3502	7501
9	Product A	12345	0.4	7502
10	Product B	67890	2500	1	100
11	Product B	67890	2600	101	151
12	Product B	67890	2900	152	251
13	Product B	67890	3100	252	751
14	Product B	67890	3200	752	1501
15	Product B	67890	4000	1502	3501
16	Product B	67890	5000	3502	7501
17	Product B	67890	1.5	7502
Data

Cell Formulas
Range	Formula
I2	I2	=TAKE(FILTER(C2:C100,(B2:B100=G2)*(D2:D100<=H2)),-1)

 

[etaf]

use an index/match but with a nearest clause
=INDEX($C$2:$C$24,MATCH(H3,IF($B$2:$B$24=G3,$D$2:$D$24),1))

lookup 2 criteria and 1 nearest.xlsx
	A	B	C	D	E	F	G	H	I
1	ProductName	ProductCode	CostPerMonth	MinQuantity	MaxQuantity
2	Product A	12345	500.000	1	100		productcode	qty	price
3	Product A	12345	600.000	101	151		12345	112	600.000
4	Product A	12345	700.000	152	251
5	Product A	12345	900.000	252	751
6	Product A	12345	1000.000	752	1501
7	Product A	12345	1050.000	1502	3501
8	Product A	12345	2000.000	3502	7501
9	Product A	12345	0.400	7502
10	Product B	67890	2500.000	1	100
11	Product B	67890	2600.000	101	151
12	Product B	67890	2900.000	152	251
13	Product B	67890	3100.000	252	751
14	Product B	67890	3200.000	752	1501
15	Product B	67890	4000.000	1502	3501
16	Product B	67890	5000.000	3502	7501
17	Product B	67890	1.500	7502
Sheet2

Cell Formulas
Range	Formula
I3	I3	=INDEX($C$2:$C$24,MATCH(H3,IF($B$2:$B$24=G3,$D$2:$D$24),1))

edit - reply as i posted

 

[Alex Blakenburg]

Or using Xlookup

=XLOOKUP(1,(B2:B17=G2)*(D2:D17<=H2),C2:C17,"",0,-1)

 

[introspective]

Or using Xlookup

=XLOOKUP(1,(B2:B17=G2)*(D2:D17<=H2),C2:C17,"",0,-1)

Thanks Alex - the 1 in xlookup - how do I find out more about that? This works!

 

[introspective]

use an index/match but with a nearest clause
=INDEX($C$2:$C$24,MATCH(H3,IF($B$2:$B$24=G3,$D$2:$D$24),1))

lookup 2 criteria and 1 nearest.xlsx
	A	B	C	D	E	F	G	H	I
1	ProductName	ProductCode	CostPerMonth	MinQuantity	MaxQuantity
2	Product A	12345	500.000	1	100		productcode	qty	price
3	Product A	12345	600.000	101	151		12345	112	600.000
4	Product A	12345	700.000	152	251
5	Product A	12345	900.000	252	751
6	Product A	12345	1000.000	752	1501
7	Product A	12345	1050.000	1502	3501
8	Product A	12345	2000.000	3502	7501
9	Product A	12345	0.400	7502
10	Product B	67890	2500.000	1	100
11	Product B	67890	2600.000	101	151
12	Product B	67890	2900.000	152	251
13	Product B	67890	3100.000	252	751
14	Product B	67890	3200.000	752	1501
15	Product B	67890	4000.000	1502	3501
16	Product B	67890	5000.000	3502	7501
17	Product B	67890	1.500	7502
Sheet2

Cell Formulas
Range	Formula
I3	I3	=INDEX($C$2:$C$24,MATCH(H3,IF($B$2:$B$24=G3,$D$2:$D$24),1))

edit - reply as i posted

Thank you etaf - this works. I couldn't figure out how to bring in the IF statement because I was thinking about it in a different way Many thanks for your help!

 

[introspective]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	ProductName	ProductCode	CostPerMonth	MinQuantity	MaxQuantity		Product	Qty
2	Product A	12345	500	1	100		12345	135	600
3	Product A	12345	600	101	151
4	Product A	12345	700	152	251
5	Product A	12345	900	252	751
6	Product A	12345	1000	752	1501
7	Product A	12345	1050	1502	3501
8	Product A	12345	2000	3502	7501
9	Product A	12345	0.4	7502
10	Product B	67890	2500	1	100
11	Product B	67890	2600	101	151
12	Product B	67890	2900	152	251
13	Product B	67890	3100	252	751
14	Product B	67890	3200	752	1501
15	Product B	67890	4000	1502	3501
16	Product B	67890	5000	3502	7501
17	Product B	67890	1.5	7502
Data

Cell Formulas
Range	Formula
I2	I2	=TAKE(FILTER(C2:C100,(B2:B100=G2)*(D2:D100<=H2)),-1)

Many thanks Fluff - I haven't used the TAKE or FILTER functions before, so will give them a go.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[etaf]

you are welcome