# Getting the next lowest value out of a row?

#### fireman12

##### New Member
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.

### Excel Facts

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

Post deleted.

#### fairwinds

##### MrExcel MVP
Hi,

Try:

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

confirmed with Ctrl + shift + enter.

#### fireman12

##### New Member
wonderful! works! thx

Replies
1
Views
35
Replies
8
Views
268
Replies
7
Views
49
Replies
3
Views
45
Replies
1
Views
207

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