which formula or function searches for a value in a range of cell.
 

Hi

Does anyone know which function/formula to use to return a specific value
from a range of cells? For example:

A B C D


1 10
2 20
3 30
4 20
5 22
6 ?


How do I make A6 return the first instance of 20 from range A1:A5?

If this is not clear, just let me know. All help greatly appreciated

Hi
for the closest value in an unsorted list use the following array
formula (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A5,MATCH(MIN(ABS(A1:A5-C1)),ABS(A1:A5-C1),0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Roccobarocco" schrieb im
Newsbeitrag ...
Hi

Does anyone know which function/formula to use to return a specific
value
from a range of cells? For example:

A B C D


1 10
2 20
3 30
4 20
5 22
6 ?


How do I make A6 return the first instance of 20 from range A1:A5?

If this is not clear, just let me know. All help greatly appreciated

Nice one, Frank !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Frank Kabel" wrote in message
...
Hi
for the closest value in an unsorted list use the following array
formula (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A5,MATCH(MIN(ABS(A1:A5-C1)),ABS(A1:A5-C1),0))

--
Regards
Frank Kabel
Frankfurt, Germany

thanks Max :-)

Nice one, Frank !
--
Rgds
Max
xl 97

"Max" wrote...
Nice one, Frank !
....
"Frank Kabel" wrote in message
for the closest value in an unsorted list use the following array
formula (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A5,MATCH(MIN(ABS(A1:A5-C1)),ABS(A1:A5-C1),0))

If this were original, perhaps the fawning wouldn't be quite so nauseating,
but this one is older than the hills. For example,

http://groups.google.com/groups?selm...c7%40defau lt

"Harlan Grove" wrote in message
If this were original, perhaps the fawning
wouldn't be quite so nauseating, ...

LOL ! It's ok, Harlan. Really don't see anything wrong
with expressing appreciation and giving positive strokes
spontaneously where it is felt due. We should have more of this
positive spontaneity, otherwise imho, xl ng "life" would just be a
continuous grind devoid of positive emotions ..

Thanks for the example link to David Hager's post
It's a pity though, that David's post received
no response from the OP ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Max" wrote...
....
Thanks for the example link to David Hager's post
It's a pity though, that David's post received
no response from the OP ..

Why?

Positive feedback is unnecessary. Plenty of negative feedback is forthcoming
when anything is amiss. And as any systems engineer will tell you, negative
feedback provides correction, positive feedback leads to instability.

"Harlan Grove" wrote...
"Max" wrote...
...
Thanks for the example link to David Hager's post
It's a pity though, that David's post received
no response from the OP ..

Why?

Positive feedback is unnecessary. Plenty of negative feedback is
forthcoming
when anything is amiss. And as any systems engineer will tell you, negative
feedback provides correction, positive feedback leads to instability.

Also forgot to mention that I'm aware of at least one Lotus 123 approach to
this that predates David Hager's previously linked newsgroup response by
several years.

Very, very little is original in these newsgroups.