Lookup Code and return Price from Price Band

Hi - I'm a new member with a problem I hope will be simple to most and an easy solution can be offered.

As per the sample attachment, I have a list of data spanning over 3,000 lines. I want to lookup a code value, then look up a quantity and return a relevant price from the list. The price will be either equal to the quantity or the next price above if not an exact match (see criteria explanation in cells F2:G4).

One added complication is that my lookup code may not now exist, as it is cross referencing an old price list. Therefore, if my code is not within the list, I need it to return an error.

I am imagining a formula using INDEX, MATCH and LOOK UP, but I just can not crack it myself.

Any help will be gratefully received! I hope it is clear what I need and a very simple solution is staring me in the face!

Many thanks

Thanks for the quick response - I really need it to return the next quantity below sorry - but can I request a formula for both? This would help with my understanding of how the formulas work. I appreciate it is more complex to look for the quantity below.

Thanks

Apologies! As a "security measure" I amended the "real" values at the last minute!! The value I need to return is £682.63 - ie, the nearest value below. There are no complex calculations necessary for the return value.

I think it is almost there, many thanks for the quick responses and your time (much appreciated).

I have attached an updated spreadsheet (Price Test 2) showing what I am trying to achieve and comments in red. I hope I am not overcomplicating and looking for too much?

Cheers

Excellent! That is exactly what I am after - Thanks!

The only issue I seem to have is for a quantity at the lowest band. For example, for code 16232904, a quantity below 600, which would put the result in the lowest price band of £2,061.45 is returning a #N/A Error..... Is there a fix for this?

Hi - many thanks for the formula above - this works great when the quantity is a match on the price list, but if the quantity is in between a band (ie, for code 16232877 a quantity of 700), the formula returns a blank as opposed to the price below......

By jinks that got it! Many thanks for your help - works great. Glad I stumbled upon this great resource - full of great hints and tips.

Cheers

so which attachment has the correct answer with the latest update? I want to check this out

This attachment has the correct formula inserted. Very useful function (for me anyhow!)