From the sorted list of values, I'm looking for a maximum that is lower than X


  A  B  C
1 1     X=7
2 3
3 5
4 8   ANS=5

Do you have any smart ideas for this problem?

  • Yes, you can use VBa to do this, but not sure if VBa is something you want since you specified worksheet-function? The concept is, take all numbers into an array, sort ascending, filter for results only below the number and take the last item from the array. – Dave Sep 5 '13 at 11:56
  • 1
    I'd prefer not to use VBA – Karusmeister Sep 5 '13 at 11:57

Use array formula:

{=MAX(IF(A1:A4<C1, A1:A4, 0))}
  • 1
    Of course this assumes that C1 is non-negative. – Scott Sep 6 '13 at 19:12
  • @Scott, Good point, do you have anything that will work for negative numbers as well? – jon Sep 9 '13 at 9:33

I'm surprised that the solution is actually so trivial!

  • hmmm.... got a vote down - is there something wrong with my answer? – Karusmeister Sep 5 '13 at 12:13
  • Maybe because you asked for "max but less than", and lookup will give you "max less than or equal". You should use array formula {=MAX(IF(A1:A4<C1, A1:A4, 0))}. Use ctrl+shift+enter to tell excel you've entered an array formula. ps, down vote wasn't me. – jon Sep 5 '13 at 12:19
  • @Jon good remark. Could you please post your comment as an answer and I'll accept it – Karusmeister Sep 5 '13 at 12:40
  • @DaveRook never mind :) – Karusmeister Sep 5 '13 at 12:40

I believe the following will work for positive or negative values of C1.


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.