Find a number that is between two values

[eduzs]

In a sheet like that:

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]3
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

How can I get retrieve the value that is >5 and <=10?

Thanks.

 

[AlanY]

like this?

Excel 2013/2016
	A	B
1	4
2	4
3	7	Y
4	2
5	4
6	8	Y
7	4
8	10	Y
9	7	Y
10	3
Sheet2

Cell Formulas
Range	Formula
B1	=IF(AND(A1>5,A1<=10),"Y","")

 

[JackDanIce]

If those values are in A1:A5, 5 is in C1 and 10 is in C2, try this Array-Formula:

=IF(MAX(IF($A$1:$A$5<=C$2, $A$1:$A$5, 0))>$C$1,MAX(IF($A$1:$A$5<=$C$2,$A$1:$A$5)),"Does not exist")

CTRL+SHIFT+ENTER

 

[Peter_SSs]

How can I get retrieve the value that is >5 and <=10?

Could there be more than 1 value that meets the critieria?
Could there be zero values that meet the criteria?
If the answer to either of these questions is 'yes' please explain what you want returned.

If the answer to both my questions is 'No' (meaning always one and only one value) then try
=SUMIFS(A1:A5,A1:A5,">5",A1:A5,"<=10")

 

[eduzs]

Could there be more than 1 value that meets the critieria?
Could there be zero values that meet the criteria?
If the answer to either of these questions is 'yes' please explain what you want returned.

If the answer to both my questions is 'No' (meaning always one and only one value) then try
=SUMIFS(A1:A5,A1:A5,">5",A1:A5,"<=10")

Works Fine, thanks all!

 

[Peter_SSs]

Works Fine, thanks all!

You are welcome.