Find the nearest fixed price point - Lookup function?

I have a set of fixed Price Points that the company uses.

Example, £9.25, 9.50, 9.75, 9.95, 10.95, 11.95 etc.Up to £40,000

It's all listed in one sheet. On a separate sheet I have the Recommended Retail Price and I need Excel to round up to the nearest highest Price Point. I've been trying all evening but I am not getting anywhere

So assume your price points are listed in ascending order in Prices sheet in A2:A100 then you can use this formula in another sheet assuming you have RRP in B2

=IF(B2<Prices!A$2,Prices!A$2,INDEX(Prices!A$2:A$100,MATCH(B2,Prices!A$2:A$100)+(LOOKUP(B2,Prices!A$2:A$100)<>B2)))

or using an array formula......

=MIN(IF(Prices!A$2:A$100>=B2,Prices!A$2:A$100))

confirmed with CTRL+SHIFT+ENTER

Hello,

applied to your attached file, Daddylongleg's formula needs to be adjusted to your ranges as follows:

Please Login or Register  to view this content.