Selected Answer

I created the following named ranges, at workbook level, on the sheet which corresponds to your lookup table.

```
A2:A10 = Code
B2:B10 = City
C2:C10 = Minimum
D2:D10 = Price
```

The advantages of using names instead of sheet addresses is that (a) "Code" is shorter than *Sheet2!$A$2:$A$10*, (b) "Code" is easier to understand when you read it in a formula and (c) changing the address of a named range can be done in one location whereas cell addresses have to be changed in every formula. With that said, you can replace the names in my formula below with cell addresses. Also observe that the ranges must be of identical size for the formula to work.

Your expression in the *Quantity* column can't be made to work easily. Therefore I have replaced the column contents with a single number and called it "Minimum". There is one price for "no minimum" (meaning "a quantity larger than 0" - cell is blank or 0) and another for a minimum of 50 (meaning "a quantity larger than 50 - cell shows 50). "Minimum" isn't the best word but that is how the formula works. The down-side of this system is that the formula can't return a price if both *Minimum* and *Quantity* are 1 (or no *Minimum* exists which is smaller than *Quantity*). Make sure you have a blank (or 0) *Minimum* for every item in your list.

`=IFERROR(AGGREGATE(15,6,Price/((Code=$F$2)*(City=$G$2)*(Minimum<$H$2)),1),"N/A")`

This formula has the lookup values in row 2. Code = F2, City = G2 and Quantity in H3. The idea is that you should enter the quantity actually being sold and the formula will return the correct price. E.g. if the q'ty is 1-50 the higher price will be returned, if it is 51 or more that lower price will come up. You can have more than 2 brackets in your list. Please change the references (F2, G2 and H2) to match your needs.

The formula will return "N/A" if either *Code* or *City* wasn't found or there is no *Minimum* which is smaller than* Quantity*. You can change the return string in the formula to anything that suits your fancy.

Finally, a word about the function itself. It will return the lowest price in the list which meets all the criteria. This is because (Minimum<Q'ty) can return more than one value. So, the final selection is made based on price. If you want to sell larger quantities at a higher price the function will not work in its present configuration.