Match index with multiple match criteria & ranges

[shrops]

Hello all,

Does anyone know how to adapt the match index formula to pull in 3 exact matches, 1 less than match and 1 greater than match?

I am looking for a way to pull in a base price for a product depending on several pieces of criteria along with the quantity amount. I want to pull in the base price so that I can do audits on actual sales price vs. the base price for a contract. How can I change my index match so that it is an exact match for Product Sold, Customer & Shipping Term but a less than match for Maximum Order and a greater than match for Minimum order?

The main issue is that I need to pull in based on 3 pieces of criteria (product, customer & shipping term) on top of the quantity ordered (between minimum and maximum amounts). It is the quantity ordered that I'm having the most difficulty with. If it is between 0 and 20,000, the price will be 1.15 but if it is over 20,000 then the base price is 1.08.

Lookup table (rows 17:60):
B: Product Sold (value for lookup in V)
D: Customer (value for lookup in U)
E: Shipping Term (value for lookup in W)
F: Minimum order amount (value for lookup in AC)
G: Maximum order amount (value for lookup in AC)
H: Base Price (This is the value I want to pull in from the lookup table)

 

[Gerald Higgins]

Hi, welcome to the board.

Can you please post a small sample of your data, making sure that it includes at least the 3 exact matches plus at least two other different entries that you want to exclude as matches ?
Explain which 3 entries are the exact matches, and why.