2 questions

Is it possible to make use a formula to find a certain type of data

EG i have a massive list but im looking for a number that is closest to 1.7400

the list ranges from 0.000 up to 2.0121 all the way back down to 0.000
now all the numbers from 0.000 to 2.0121 are exactly the same to 2.0121 to 0.000

The closest is 1.7425 now i need a formula to do this because some data will change so i need a general formula.

The numbers Before and after it are 1.7362, 1.7394, 1.7425 (witch is the one i need), 1.7456, 1.7488
My second question is in the same list i need a formula that can highlight that row. Is this possible?

My first thought is that you're saying two different things.

In your statement, you're saying you want the closest value. However, in your example, you chose the smallest value larger than your target value.

1.7400 - 1.7394 = 0.0006 <-- Closest
1.7425 - 1.7400 = 0.0025

Anyway, to find the closest, no matter which direction, use the following formula (an array formula):

Please Login or Register  to view this content.

I put the value sought for in B1, and the array in A1:A7... but you can adjust this.

If you're trying to find the closest that is larger than the value you have, you can use the following formula:

Please Login or Register  to view this content.

To highlight this row (or rows), you have to put conditional formatting on the whole section (ie. A1:A7), comparing it to the cell where you have the value. (You can also use the formula directly) For example, if you put this formula in C1, you can just use conditional formatting and say "Cell Value" "is equal to" =$C$1.

Note that if you are using decimal values, you might run into precision errors.

Scott