Getting the next lowest value out of a row?

fireman12

New Member
Joined
Apr 17, 2006
Messages
19
I have a couple hundred rows of data. Each row has 10 columns of data (starting in col C). It is basically the performance data of 10 people (each row is a week, ea. col a person). In column A I have a "benchmark" In col B I need the lowest number that breaks below the benchmark, but is the 1st # to do so. So like if the numbers in the first row are:

benchmark
-3...????...1......5......-2......-10......-5......11......9.....-12......etc. to 10

(here the benchmark (col A) is -3 and the rest of the #'s are of each person's performance) I need col B (the ???) to return the value from the 5th data column, or -5. [-12 would be the =min], but I need col B to show me the first number that breaks below the benchmark, or the closest value that is less than benchmark (so not -2, because it is not below)(another way to say, the highest number that is below the benchmark) I have been playing around with Hlookup but I think that is wrong. How can I do this? Thank you folks for helping out.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

=MAX(IF(C2:L2 < A2,C2:L2))

confirmed with Ctrl + shift + enter.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,448
Members
410,611
Latest member
JB_101920
Top