Try...

=INDEX(A1:A7,MATCH(10,A1:A7)+(LOOKUP(10,A1:A7)<10 ))

Hope this helps!

In article ,
Don wrote:

Thanks, that works great for the number closest to less than 10. I also need
the same type solution for the number closest to more than 10. For instance
a1=1.34, a2=19.13, a3=36.64, a4=42.98, a5=76.41, a6=91.22, a7=100. Your
formula only gets the first number under 10. How can I get a formula to give
me the first number above 10. Like in this case I would need it to select
the value in cell a2 (19.13). Keep in mind the values in these cells will
vary depending on other calcs in the spreadsheet. Thanks again!

"Scoops" wrote:


Don wrote:
I'm looking for a function or formula in Excel that will find a number in
a
range of numbers that is closest to a specified number. Example: I have
a
column of 7 cells that contain cummulative percentages from 0 - 100. It
will
always be theses same cells, but will have varying percent values
depending
on other calculations. I need to find the first number that's closest to
and
below 10. I also need to find the first number that's closest to and
above
10. I need this for 3 other values (40, 50, & 90)as well, but if I can
get
an example of a formula, I could probably go from there.

Thanks in advance for the help!

Hi Don

Try this in your result cell:

=VLOOKUP(10,$A$1:$A$7,1,TRUE)

Copy it down three cells and amend the 10 to 40, 50 and 90
respectively.

Note that you can make the "10" a reference to another cell so that you
could change that cell rather than amending the formula in the future.

Regards

Steve