Finding a value In a column between two numbers

[Jhardy78]

I am trying to write a formula that is returning the highest matched number between a given range. For example. I want to return the highest match number between 68 to 128 for the following (this is A column. The number to the left before the dash is the row number)
1- 530.4
2- 296.8
3- 187.6
4- 159.3
5- 147.7
6- 74.4
7- 61.1
8- 58.2
9- 50.1
10- 36.9
11- 27.7
12- 26.8
13- 16.2
14- 14.3
15- 10.6
16- 9.1

[#2]

@Jhardy78 

I think I have interpreted that you want to measure the degree of proximity of each of these numbers with the range 68-128, or some other range.
I think it's a very interesting topic; I have applied the following formula:

= IF (AND (G4> = 68; G4 <= 128); 1; IF (G4> 128; G4-128; SI (G4 <68; 68-G4; 0)))

This formula assigns 1 to the values that are within the range.
To the values that are above the range, I attribute the value of the subtraction between the number and the maximum value of the range; and to those who are below the range, I attribute the subtraction between the minimum and the number.
The lower the number, the closer to the range it will be.
In the example, I used 68 and 128; but these values can be written in two cells and you can refer to them.

Hope this helps!

[#3]

@Jhardy78 

Assuming your numbers are in the range A1:A16 then you may try this...

=MAXIFS(A1:A16,A1:A16,">=68",A1:A16,"<=128")

If required, instead of hard coding the criteria, you may mention the criteria in the cells and refer the criteria cells in the formula. So assuming the min criteria is in B1 and max criteria is in C1, the criteria in the above formula would be ">="&B1 and "<="&C1.

If MAXIFS is not available with your version, you may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

=MAX(IF((A1:A16>=68)*(A1:A16<=128),A1:A16))

and confirm with Ctrl+Shift+Enter.

[#4]

Your top formula is returning the lowest number not the highest number but so close. The bottom formula I can’t get to work maybe because I’m using my Excel on my phone.

*your assumption was correct with a1:a16

[#5]

Thanks Paul but that formula doesn’t make some sense to me since you are using G4 and my array is in a1:A16.

[#6]

@Jhardy78 

What do you mean exactly under matched number, matched with what? If that's the maximum number in the range (A1:A16) which is less than 128 and more than 68, when @Subodh_Tiwari_sktneer formula is correct, it returns 74.4. And since it's the only number in the range, it simultaneously the smallest one. Another variant of non-array formula without MAXIFS is

=IFERROR(AGGREGATE(14,6,1/($A$1:$A$16<=128)/($A$1:$A$16>=68)*$A$1:$A$16,1),"no such")

Or you mean something more complex?