lookup to return larger value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Christobee - 21 Nov 2005 18:06 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. I would like
Lookup to give me the Size passed by 80 percent or less which is 19 and then
80% or more which is 25.

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

Harlan Grove - 21 Nov 2005 18:18 GMT

Christobee wrote...

>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. I would like
>Lookup to give me the Size passed by 80 percent or less which is 19 and then
>80% or more which is 25.

...

If your data values were in A2:B10, with both columns in ascending
order, the size passed by 80 or fewer would be given by regular
formulas

=LOOKUP(80,B2:B10,A2:A10)

or the array formula

=INDEX(A2:A10,MATCH(1,1/(B2:B10<=80)))

and the size passed by 80 or more would be given by the array formula

=INDEX(A2:A10,MATCH(1,1/(B2:B10<=80))+1)

If your range wasn't sorted, you'd need to use array formulas like

=INDEX(A2:A10,MATCH(MAX(IF(B2:B10<=80,B2:B10)),B2:B10,0))

and

=INDEX(A2:A10,MATCH(MIN(IF(B2:B10>=80,B2:B10)),B2:B10,0))

Reply to this Message

Christobee - 21 Nov 2005 19:38 GMT

I get and Error message with both of your formulas for some reason the
formulas contain invalid data

> 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 - 12 lines]
> 19 72.57
> 25 94.51

Reply to this Message

Harlan Grove - 21 Nov 2005 19:42 GMT

Christobee wrote...

>I get and Error message with both of your formulas for some reason the
>formulas contain invalid data

...

Did you enter the *ARRAY* formulas as *ARRAY* formulas, meaning holding
down [Ctrl] and [Shift] keys before pressing [Enter]?

Reply to this Message

Christobee - 22 Nov 2005 06:48 GMT

It works. Very helpful. Thanks Grove!

> Christobee wrote...
> >I get and Error message with both of your formulas for some reason the
[quoted text clipped - 3 lines]
> Did you enter the *ARRAY* formulas as *ARRAY* formulas, meaning holding
> down [Ctrl] and [Shift] keys before pressing [Enter]?

Reply to this Message