How do I find the closest number to a given number in an unsorted.

M

mpsfeldt

I need to find the first occurence of a number in an unsorted list (stock
prices by date). I need to be able to do this from within a formula. The
lookup functions and Match functions don't work because of the inability to
sort. Any help would be appreciated.
 
F

Frank Kabel

Hi
use
=VLOOKUP(lookup_value,A1:A100,1,FALSE)

or
=INDEX(A1:A100,MATCH(lookup_value,A1:A100,0))

if you're looking for an exact match. If you need a closest match try
the following array formula (entered with CTRL+SHIFT+ENTER):+
=INDEX(A1:A100,MATCH(MIN(ABS(A1:A100-lookup_value)),ABS(A1:A100-lookup_
value),0))
 
J

Jerry W. Lewis

Re-read Help for LOOKUP and MATCH. They both have options for exact
matches with unsorted data.

Jerry
 
A

Aladin Akyurek

mpsfeldt said:
I need to find the first occurence of a number in an unsorted lis
(stock
prices by date). I need to be able to do this from within a formula.
The
lookup functions and Match functions don't work because of th
inability to
sort. Any help would be appreciated.
Click to expand...

If you set the match type (range_lookup) to 0 or FALSE in the functio
(VLOOKUP, MATCH,...) you invoke, you would get what you want
 
M

mpsfeldt

Frank,
Thanks for the response. I accidently didn't finish my first sentence. It
should have said "I need to find the first occurence of a number in an
unsorted list
(stock prices by date) that is larger than a given threshold number.
Click to expand...

Your response gives me the closest number to the threshold number but not
the first number. It's an elegant response though. If you have any thoughts
on getting to the first number though, I'd sure appreciate it.

Thanks again.

Mark Schoenfeldt
 
Top