Thread: Find closest match - quantity breaks in pricing speadsheet

Please help. I know I will need to do some kind of look up to solve this problem, but don't know how to do it. Please see attached Excel2010 spreadsheet. I need to match quanties in col E with a quantity break quantity columns. Need to find the closest break qty to value in col E. But I don't want the next smallest or next largest value.
I just want the closest value.
So, if break quantities are 20, 40 , 60, 80, 100 a value in col E of 45, must return match for the 40 value
If value in col E is 75, must return match for the 80 value

Columns in Break1, break2, colmns, will always be in asc order.

In the attached spread sheet, the , the cells in break columns highlighted in yellow, match the values in Col E, and the value I want to use is highlighted in orange.

Can this be done? I will appreciate any help I can get on this.

I think your problem is similar to my friend problem, you will able to find my solution at my free sharing site (Mr. Excel).

To solve the issue I use HLOOKUP and Match functions and also have a few workaround by create few dummy column and row. But this workaround method just to save your workbook site, you can consolidate it into one column or row if you don't want dummy column or row.

Please check it here.

Thanks Mr Excel, I will have a look. I think it can be done using IF and AND function also?

Yes you welcome! If and And functions, will it work? I am not sure about this...