# Index Match Multiple Columns - Exact and Approximate



## carolj92190 (Dec 15, 2022)

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 NumberLow VolumeHigh VolumePricePN112500$1500PN1250110000$5375PN11000125000$12000PN21100$250PN2101500$750PN25011500$1000PN21501999999$18000PN31999999$20000


----------



## DRSteele (Dec 15, 2022)

Let's try this:

MrExcel posts19.xlsxBCDEFGHI12Lookup Part and Low Boundary3PN28000$18,00045Part NumberLow VolumePrice6PN11$1,5007PN12501$5,3758PN110001$12,0009PN21$25010PN2101$75011PN2501$1,00012PN21501$18,00013PN31$20,000Sheet35Cell FormulasRangeFormulaI3I3=XLOOKUP(H3,FILTER(C6:C13,G3=B6:B13),FILTER(E6:E13,G3=B6:B13),"not found",-1,-1)


----------



## Fluff (Dec 16, 2022)

Another option
Fluff.xlsmABCDEFGH1Part NumberLow VolumeHigh VolumePrice2PN1125001500PN1800053753PN125011000053754PN11000125000120005PN211002506PN21015007507PN2501150010008PN21501999999180009PN3199999920000DataCell FormulasRangeFormulaH2H2=TAKE(FILTER(D2:D100,(A2:A100=F2)*(B2:B100<=G2),"No match"),-1)


----------



## carolj92190 (Dec 20, 2022)

Works great, thank you!


----------



## Fluff (Dec 20, 2022)

Glad we could help & thanks for the feedback.


----------

