Microsoft Office excel 2007 - upgrade

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.4Can you help me with this?

Your example shows a list of numbers sorted in ascending order. Will that always be the case?

If so, VLOOKUP with a

Range_lookupvalue of 1 will return an exact match if one exists or the largest value that is less than thelookup_valueif 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?

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.

Glad I could help.

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History