Volume Pricing Lookup using Array

Hello Everyone. I have read a number of price lookup posts that require Volume pricing lookup. One that was simply elegant ( from my experience was put forth by NBVC). He presented the following general format =LOOKUP(X1, A2:C6)....(it's simply elegant).
Where
X1 = user entered quantity
A = Min Qty Values
B = Max Qty Values
C = Price


However, the data structure I am working with is more complex and I cannot figure out how to adapt the above.

Namely I have 1 to 3 selection criteria.. If there were no Volume Pricing criteria, then the solution is SIMPLE adapting NBVC's method to a general Array format of INDEX(PRICE_ARRAY,MATCH(Selection1&Selection2&Selection3,Array1&Array2&Array3,0)). .......you can see this work in cell F2 of the attached.

But when I have a variable user input for volume, I then need to have the correct volume level price selected. Currently adapting the above formula, provides the FIRST item it encounters within the array that meets the criteria, how could one integrate the User input Quantity (B17) to locate the appropriate Volume Pricing?

In the attachment, the first example demonstrates the above Index process where price volume is not included. The second example, provides data to help show the structure of the data that includes volume pricing.

Can someone shed some light on this?

Try:

Please Login or Register  to view this content.

Note: You don't need to enter this with CTRL+SHIFT+ENTER.... just ENTER will do.