Find closest smaller and larger value to a given number

[westend9876]

I have a list of numbers in column A with some duplicates :
2
5
2
7
8
9
7

I need to find the smallest and the lowest value to a given number preferably without using an array. Let's say that number is 4. These two formulas will go into cells B1 and B2 respectively.

 

[azumi]

Pehaps something like this could work?

 

[SirJB7]

Hi, azumi!
In your uploaded workbook data range at column A goes from A1:A7 and formulas use A2:A8.
Output formulas at D4:D5 are array formulas, when both of them could be normal ones.
Besides, you're using "<" and ">" which may be a request interpretation. westend9876 should go for "<=" and ">=" if original intention included equal values.
Regards!

 

[westend9876]

Is there a way to modify the formula to disregard value equal to 0 in column A? I don't want 0 to be considered in the result.

 

[NARAYANK991]

Is there a way to modify the formula to disregard value equal to 0 in column A? I don't want 0 to be considered in the result.

Hi ,

As long as the threshold value (4 in this case) is greater than 0 , the formula does not need to be changed.

What exactly do you mean when you say 0 should be disregarded ? Can you give an example of data which contains zeros , and indicate the result which should be obtained disregarding zeros ?

Narayan

 

[westend9876]

Hi ,

As long as the threshold value (4 in this case) is greater than 0 , the formula does not need to be changed.

What exactly do you mean when you say 0 should be disregarded ? Can you give an example of data which contains zeros , and indicate the result which should be obtained disregarding zeros ?

Narayan

Here is an example in column A with zero as a value. So if the lookup value is 4 the formula returns 0 as the result.

I have a list of numbers in column A with some duplicates :
2
5
2
7
0
8
9
7

I need to find the smallest and the lowest value to a given number preferably without using an array. Let's say that number is 4. These two formulas will go into cells B1 and B2 respectively.

 

[NARAYANK991]

Hi ,

I am confused ; the file and the formula uploaded by Azumi does not return 0 , unless it is the only number which is smaller than the threshold number (4 in this case) ; if there is even one number greater than 0 but less than 4 , it will return that.

Narayan