# 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.5The column could contain 100, 101.1, 101.4, 101.6, 102, 103The result should be = 101.4Can you help me with this?

See More: Find max value less than a target value

#1
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 •

#2
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 •

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

Report •

Related Solutions