Above is the sample sheet. I need a formula in K cell

enter image description here

1 Answer 1


You can use the following formulas

Closest minimum value

=MINIFS(A2:E2, A2:E2, ">=" & G2)

Closest maximum value

=MAXIFS(A2:E2, A2:E2, "<=" & G2)

enter image description here

Functions used:

  • Welcome. Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Aug 23, 2020 at 12:02
  • Thank you. It worked. Can you also help me out how I can output the cell reference in another column? I mean in J cell, I need E2 to be output.
    – Drazer
    Aug 23, 2020 at 12:32
  • If I understand you correctly: "...in J cell, I need E2 to be output". In cell J2 use =E2. If not, please read how to and share a test sheet so as you can be easier helped. Aug 23, 2020 at 13:44
  • docs.google.com/spreadsheets/d/… Here is the link with permissions
    – Drazer
    Aug 23, 2020 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.