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

[Roccobarocco]

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

 

[Frank Kabel]

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))

 

[Max]

Nice one, Frank !

 

[Frank Kabel]

thanks Max

 

[Harlan Grove]

Nice one, Frank ! ....

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=01bc7dd8$94346180$c8bcaec7@default

 

[Max]

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 ..

 

[Harlan Grove]

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]

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.