Forum Discussion
Finding a value In a column between two numbers
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...
Thanks Paul but that formula doesn’t make some sense to me since you are using G4 and my array is in a1:A16.
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?