lookup to return larger value
 

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

lookup to return larger value
 

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))

lookup to return larger value
 

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

"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.

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

lookup to return larger value
 

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]?

lookup to return larger value
 

It works. Very helpful. Thanks Grove!

"Harlan Grove" wrote:

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]?