Thread: max in list that <=input value

i am in need of a formula for finding out the max that meets a criteria.

i've attached the spreadsheet, for an example. what i'm looking to do is have the highlighted cell as an input. based on that input, i'd like to find out the highest number than is associated with that number (sounds confusing to say it this way... but, i need the maximum number that is less than or equal to the input number). from there, i can figure out how and what to use for the vlookup and/or index, match part of things.

i looked a little at DMAX, which seems like it might work, but, can't get it to do what i want.

[in the excel attachment, i filled in the values that i want it to say, after the formula is inserted into the cell. in particular, cell A4 is the real concern. i can figure the rest out.]

thank you, in advance, for any assistance

How about?

=LOOKUP(units,$C$2:$C$31,$B$2:$B$31)

=MAX(IF($C$2:$C$31<units,$C$2:$C$31))

Array entered with Ctrl-Shift-Enter rather than just Enter.

It will be confirmed with curly brackets, thus:

{=MAX(IF($C$2:$C$31<units,$C$2:$C$31))}


and:

=INDEX($B$2:$B$31,MATCH(MAX(IF($C$2:$C$31<units,$C$2:$C$31)),$C$2:$C$31,0))

Again, array entered with Ctrl-Shift-Enter rather than just Enter.



Regards

nice! gimme a few to see which one fits the application the best

=INDEX($B$2:$C$31,MATCH(units,$C$2:$C$31,1),1)

ok... TMShucks... I'm afraid that is a little over my head at the moment, as to how that works, and therefore how to apply it.

NBVC: that works perfectly, and extremely easy for my wee mind to get. thanks to both of you, though, since i will be looking into Shuck's way too.

AND will look into davegugg's way as well lol... thanks to all !!!

{it seems as though i was really close to davegugg's way, but i missed the match type 1, and put type 0 instead. so, was getting an error}

Fair comment ... good to have choices though

See the attached example for a comparison.


Thanks for the rep.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

oops... forgot to mark solved... sorry bout that :/