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...
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.
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
*your assumption was correct with a1:a16