lookup values from a list of different ranges to determine the right value

[Mehow]

Hi all,

I'm trying to think of a formula to lookup values from a list to decide a right size product for an order.

For example - this is the range

range ​
25mm ​	min​	max ​
400​	300​	394​
425​	395​	419​
450​	420​	444​
475​	445​	469​
500​	470​	494​
525​	495​	519​
550​	520​	544​
575​	545​	569​
600​	570​	594​
625​	595​	619​
650​	620​	644​
675​	645​	669​
700​	670​	694​
725​	695​	719​
750​	720​	744​
775​	745​	769​
800​	770​	794​
825​	795​	819​
850​	820​	844​
875​	845​	869 ​

<tbody>
</tbody>

Client Ordered	25
l	w	l	w
412	750
400	664
502	547
610	751
620	540

<colgroup><col width="64" style="width: 48pt;" span="4"> <tbody> </tbody>

I need a formula to determine that if the number is 412 - the correct size will be 425 (as you can see in the min/max range)
and if it's 625 the value should be 650.. and so on.

Any ideas?

 

[steve the fish]

Try:

=IF(E1>MAX($C$2:$C$21),"Error",IFERROR(LOOKUP(E1,$B$2:$B$21,$A$2:$A$21),"Error"))

With E1 holding the 412, 625 etc.

 

[Mehow]

Works thanks so much for your help