Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
thanks Max :-)
Nice one, Frank ! -- Rgds Max xl 97 |
#5
|
|||
|
|||
"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 |
#6
|
|||
|
|||
"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 ---- |
#7
|
|||
|
|||
"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. |
#8
|
|||
|
|||
"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying value of specific cell within a range, with IF function...? | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Function / formula to be used if cell contains a letter. | Excel Worksheet Functions |