Find max value less than a target value

Microsoft Office excel 2007 - upgrade
June 25, 2010 at 11:53:04
Specs: Windows XP
I need an Excel formula to find the max value in a column of values that is not greater than a specific fixed target cell value. example...
The target would be 101.5
The column could contain 100, 101.1, 101.4, 101.6, 102, 103
The result should be = 101.4

Can you help me with this?

See More: Find max value less than a target value

June 25, 2010 at 12:11:44
Your example shows a list of numbers sorted in ascending order.

Will that always be the case?

If so, VLOOKUP with a Range_lookup value of 1 will return an exact match if one exists or the largest value that is less than the lookup_value if an exact match isn't found.

Again, in order for this to work, the list must be sorted in ascending order.

=VLOOKUP(B1, $A$1:$A$6, 1, 1)

Does that help?

Report •

June 25, 2010 at 12:26:49
Hey thanks a huge amount.
Yes the values are ascending and I tried the lookup and it worked just as I needed it to.
I hadn't used vlookup for a non-exact match but it does exactly what I needed due to the final parameter being 1 (True). I normally use False and only get an exact match....

Awesome answer!

Thanks again.

Report •

June 25, 2010 at 12:32:21
Glad I could help.

Report •
Related Solutions

Ask Question