lookup to return larger value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Christobee - 21 Nov 2005 18:01 GMT

I have the following PSD data. In addition to the largest number less or
equal to the lookup value I would like lookup to also give me the smallest
value larger than or equal the lookup value on another cell

Size (mm)    % Passing
1.7    0.16
2.8    0.47
4.75    1.25
6.7    2.04
8    3.61
10    11.44
13.2    38.09
19    72.57
25    94.51

Reply to this Message

Ron Coderre - 21 Nov 2005 20:52 GMT

Try this:
Using your data in Cells A1:B9

E1: 6.5
F1: =VLOOKUP(E3,A1:A9,1,1)
That finds the closest match in Col A

F2: =INDEX(A1:A9,MATCH(F1,A1:A9,0)+1)
Looks in A1:A9 for the value in Cell F1 and returns the next item listed
after that.

Is that what you're looking to do? If no...there are other approaches.

***********
Regards,
Ron

> I have the following PSD data. In addition to the largest number less or
> equal to the lookup value I would like lookup to also give me the smallest
[quoted text clipped - 10 lines]
> 19 72.57
> 25 94.51

Reply to this Message

Christobee - 22 Nov 2005 06:49 GMT

thanks helpful

> Try this:
> Using your data in Cells A1:B9
[quoted text clipped - 27 lines]
> > 19 72.57
> > 25 94.51

Reply to this Message