Formula for range of values

[normpam]

ITEM QTY AVAILABLE QUANTITIES
A B C D E
Product A 400 2500 1000 500

Is there a formula which will calculate the closest higher value based on the quantity in Col B?

In other words, need to find out if all of the available quantities are lower than 400, then keep 400. If the available quantities have an option that is higher than 400, return the closest highest value - in this case, 500.

 

[Z51]

Perhaps:

=MAX(MINIFS(C1:E1,C1:E1,">"&B1),B1)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)