Getting the next lowest value out of a row?

[fireman12]

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.

 

[Scott Huish]

Post deleted.

 

[fairwinds]

Hi,

Try:

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

confirmed with Ctrl + shift + enter.

 

[fireman12]

wonderful! works! thx