2

https://docs.google.com/spreadsheets/d/1Dew-CLxHvMW2BgI9axdJTEI-2CezTx7k6Vt1R48mwgA/edit?usp=sharing

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

enter image description here

2

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:

4
  • 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. – marikamitsos Aug 23 '20 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 '20 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. – marikamitsos Aug 23 '20 at 13:44
  • docs.google.com/spreadsheets/d/… Here is the link with permissions – Drazer Aug 23 '20 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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