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
[TABLE="width: 2"]
<tbody>[TR]
[TD="width: 192, bgcolor: transparent, colspan: 3"]

range
​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

25mm
​

[/TD]
[TD="width: 53, bgcolor: transparent"]

min​

[/TD]
[TD="width: 59, bgcolor: transparent"]

max
​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

400​

[/TD]
[TD="width: 53, bgcolor: transparent"]

300​

[/TD]
[TD="width: 59, bgcolor: transparent"]

394​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

425​

[/TD]
[TD="width: 53, bgcolor: transparent"]

395​

[/TD]
[TD="width: 59, bgcolor: transparent"]

419​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

450​

[/TD]
[TD="width: 53, bgcolor: transparent"]

420​

[/TD]
[TD="width: 59, bgcolor: transparent"]

444​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

475​

[/TD]
[TD="width: 53, bgcolor: transparent"]

445​

[/TD]
[TD="width: 59, bgcolor: transparent"]

469​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

500​

[/TD]
[TD="width: 53, bgcolor: transparent"]

470​

[/TD]
[TD="width: 59, bgcolor: transparent"]

494​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

525​

[/TD]
[TD="width: 53, bgcolor: transparent"]

495​

[/TD]
[TD="width: 59, bgcolor: transparent"]

519​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

550​

[/TD]
[TD="width: 53, bgcolor: transparent"]

520​

[/TD]
[TD="width: 59, bgcolor: transparent"]

544​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

575​

[/TD]
[TD="width: 53, bgcolor: transparent"]

545​

[/TD]
[TD="width: 59, bgcolor: transparent"]

569​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

600​

[/TD]
[TD="width: 53, bgcolor: transparent"]

570​

[/TD]
[TD="width: 59, bgcolor: transparent"]

594​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

625​

[/TD]
[TD="width: 53, bgcolor: transparent"]

595​

[/TD]
[TD="width: 59, bgcolor: transparent"]

619​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

650​

[/TD]
[TD="width: 53, bgcolor: transparent"]

620​

[/TD]
[TD="width: 59, bgcolor: transparent"]

644​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

675​

[/TD]
[TD="width: 53, bgcolor: transparent"]

645​

[/TD]
[TD="width: 59, bgcolor: transparent"]

669​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

700​

[/TD]
[TD="width: 53, bgcolor: transparent"]

670​

[/TD]
[TD="width: 59, bgcolor: transparent"]

694​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

725​

[/TD]
[TD="width: 53, bgcolor: transparent"]

695​

[/TD]
[TD="width: 59, bgcolor: transparent"]

719​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

750​

[/TD]
[TD="width: 53, bgcolor: transparent"]

720​

[/TD]
[TD="width: 59, bgcolor: transparent"]

744​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

775​

[/TD]
[TD="width: 53, bgcolor: transparent"]

745​

[/TD]
[TD="width: 59, bgcolor: transparent"]

769​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

800​

[/TD]
[TD="width: 53, bgcolor: transparent"]

770​

[/TD]
[TD="width: 59, bgcolor: transparent"]

794​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

825​

[/TD]
[TD="width: 53, bgcolor: transparent"]

795​

[/TD]
[TD="width: 59, bgcolor: transparent"]

819​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

850​

[/TD]
[TD="width: 53, bgcolor: transparent"]

820​

[/TD]
[TD="width: 59, bgcolor: transparent"]

844​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"]

875​

[/TD]
[TD="width: 53, bgcolor: transparent"]

845​

[/TD]
[TD="width: 59, bgcolor: transparent"]

869
​

[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 192"]
<colgroup><col width="64" style="width: 48pt;" span="4"> <tbody>[TR]
[TD="width: 128, bgcolor: transparent, colspan: 2"]Client Ordered[/TD]
[TD="width: 128, bgcolor: transparent, colspan: 2"]25
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="bgcolor: transparent"]l[/TD]
[TD="bgcolor: transparent"]w[/TD]
[TD="bgcolor: transparent"]l[/TD]
[TD="bgcolor: transparent"]w[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]412
[/TD]
[TD="bgcolor: transparent"]750 [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]400
[/TD]
[TD="bgcolor: transparent"]664
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]502 [/TD]
[TD="bgcolor: transparent"]547
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]610
[/TD]
[TD="bgcolor: transparent"]751
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]620 [/TD]
[TD="bgcolor: transparent"]540
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

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