Index Match Multiple Columns - Exact and Approximate

[carolj92190]

Greetings,
Working on a pricing tool, where the part numbers can have multiple tiers and different number of tiers, based on volume. I've been struggling with trying to index/match the part number in column A (exact) and the volume in column C (approximate based on tier) but either end up with a REF or an incorrect result. In sample data if PN1 is selected and volume quantity is 8000, it would fall into the 2nd tier and the price would be a flat $5375. Sample data only has 3 parts, final tool will have many more so trying to make this as easy as possible. Some attempts at formulas:
=INDEX($A$2:$D$9,MATCH(H2,$A$2:$A$9,0),MATCH(H3,$B$2:$B$9,1)) - resulted in #REF!
=INDEX($D$2:$D$9,MATCH(H2,$A$2:$A$9,0),MATCH(H3,$C$2:$C$9,1)) - resulted in #REF!
=INDEX($D$2:$D$9,MATCH(1,(H2=$A$2:$A$9)*(H3=$C$2:$C$9))) - resulted in $6000 instead of $5375
Appreciate any help you can provide. Thanks!

Part Number	Low Volume	High Volume	Price
PN1	1	2500	$1500
PN1	2501	10000	$5375
PN1	10001	25000	$12000
PN2	1	100	$250
PN2	101	500	$750
PN2	501	1500	$1000
PN2	1501	999999	$18000
PN3	1	999999	$20000

 

[DRSteele]

Let's try this:

MrExcel posts19.xlsx
	B	C	D	E	F	G	H	I
1
2						Lookup Part and Low Boundary
3						PN2	8000	$18,000
4
5	Part Number	Low Volume		Price
6	PN1	1		$1,500
7	PN1	2501		$5,375
8	PN1	10001		$12,000
9	PN2	1		$250
10	PN2	101		$750
11	PN2	501		$1,000
12	PN2	1501		$18,000
13	PN3	1		$20,000
Sheet35

Cell Formulas
Range	Formula
I3	I3	=XLOOKUP(H3,FILTER(C6:C13,G3=B6:B13),FILTER(E6:E13,G3=B6:B13),"not found",-1,-1)

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Part Number	Low Volume	High Volume	Price
2	PN1	1	2500	1500		PN1	8000	5375
3	PN1	2501	10000	5375
4	PN1	10001	25000	12000
5	PN2	1	100	250
6	PN2	101	500	750
7	PN2	501	1500	1000
8	PN2	1501	999999	18000
9	PN3	1	999999	20000
Data

Cell Formulas
Range	Formula
H2	H2	=TAKE(FILTER(D2:D100,(A2:A100=F2)*(B2:B100<=G2),"No match"),-1)

 

[carolj92190]

Works great, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.