Table Searching

[Joe Miller]

HELP!

I have been unsuccessfully trying to search column C in the followin
table for the first occurance of a value which is equal to or highe
than a given value – the returned value to be the corresponding date i
column A.

I also need to know how to do almost the same thing except searchin
column D for the first occurrence of a value which is equal to or lowe
than a given value – once again being presented with the correspondin
date in column A.

I have experimented with various Lookup and Reference functions to n
avail so far.
the first 3 rows of data in the table are:
***A************B*******C******D******E
DATE**********OPEN***HIGH****LOW***CLOSE
7/23/2005 1.206 1.2068 1.2055 1.2056
7/24/2005 1.2056 1.2087 1.2026 1.2059
7/25/2005 1.2059 1.2068 1.1979 1.201

 

[Niek Otten]

Hi Joe,

You'll have to sort your table, otherwise " the first occurrence of a value which is equal to or higher" is too difficult to
interpret and not compatible with Excel's search mechanisms.
For your first question, sort on column C, Descending, and use a formula like this:
=INDEX(A1:A3,MATCH(F1,C1:C3,-1))

For your second question, sort on D Ascending (or use a copy of the data) and a formula like
=INDEX(A6:A8,MATCH(F6,D68,1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| HELP!
|
| I have been unsuccessfully trying to search column C in the following
| table for the first occurance of a value which is equal to or higher
| than a given value - the returned value to be the corresponding date in
| column A.
|
| I also need to know how to do almost the same thing except searching
| column D for the first occurrence of a value which is equal to or lower
| than a given value - once again being presented with the corresponding
| date in column A.
|
| I have experimented with various Lookup and Reference functions to no
| avail so far.
| the first 3 rows of data in the table are:
| ***A************B*******C******D******E
| DATE**********OPEN***HIGH****LOW***CLOSE
| 7/23/2005 1.206 1.2068 1.2055 1.2056
| 7/24/2005 1.2056 1.2087 1.2026 1.2059
| 7/25/2005 1.2059 1.2068 1.1979 1.2012
|
|
| --
| Joe Miller
| ------------------------------------------------------------------------
| Joe Miller's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29900
| View this thread: http://www.excelforum.com/showthread.php?threadid=569412
|

 

[Bernie Deitrick]

Joe,

Array enter, using Ctrl-Shift-Enter

=INDEX(A:A,MIN(IF(C1:C20>=given value,ROW(C1:C20),100000)))
=INDEX(A:A,MIN(IF(D120<=given value,ROW(D120),100000)))

Expand C1:C20 and D120 to match your table, and subsitute either the value of interest or a cell
reference for the "given value" in the formulas.

HTH,
Bernie
MS Excel MVP

 

[Joe Miller]

Hi Niek,
Thanks for your help. However, I forgot to mention a small detail - I
don't think I can use a sorted table because I need the dates in column
A to remain in ascending order.
Joe Miller

 

[Joe Miller]

Hi Niek,
Thanks for your help. However, I forgot to mention a small detail - I
don't think I can use a sorted table because I need the dates in column
A to remain in ascending order. Maybe Bernie's solution will work?
Joe Miller

 

[Bernie Deitrick]

Joe,

Maybe??? Of course it will, otherwise, I wouldn't have posted it ;-)

HTH,
Bernie
MS Excel MVP

 

[Joe Miller]

Hi Bernie,
Thanks for your help.

I tried
=INDEX(A:A,MIN(IF(C1:C20>=1.2402,ROW(C1:C20),100000)))

and then
=INDEX(A2:A21,MIN(IF(C2:C21>=1.2402,ROW(C2:C21),100000)))
where the header is row 1 and the table is rows 2 thru 27.
However I get a #REF! error. Do you see what I am doing wrong?

I forgot to mention in my initial post that I need to keep
the dates unsorted so your method looks like the one I need.
Joe Miller

A B C D E

DATE OPEN HIGH LOW CLOSE
7/23/2005 1.206 1.2068 1.2055 1.2056
7/24/2005 1.2056 1.2087 1.2026 1.2059
7/25/2005 1.2059 1.2068 1.1979 1.2012
7/26/2005 1.2012 1.2083 1.1962 1.2067
7/27/2005 1.2067 1.2148 1.2035 1.2137
7/28/2005 1.2137 1.2159 1.2074 1.2123
7/30/2005 1.2123 1.2137 1.2121 1.2122
7/31/2005 1.2122 1.2248 1.2118 1.2179
8/1/2005 1.2179 1.225 1.2171 1.2188
8/2/2005 1.2188 1.2343 1.2148 1.2334
8/3/2005 1.2334 1.2402 1.2298 1.2383
8/4/2005 1.2383 1.2395 1.231 1.2350
8/6/2005 1.235 1.2363 1.2344 1.2346
8/7/2005 1.2346 1.2389 1.2314 1.2349
8/8/2005 1.2349 1.2414 1.2329 1.2369
8/9/2005 1.2369 1.2425 1.2333 1.2381
8/10/2005 1.2381 1.2474 1.2375 1.2469
8/11/2005 1.2469 1.2485 1.2381 1.2435
8/13/2005 1.2435 1.2461 1.2435 1.2458
8/14/2005 1.2458 1.2463 1.2343 1.2366

 

[Joe Miller]

Bernie,
I meant to say row 2 thru 21, not row 2 thru 27 in that last reply.
Joe

 

[Bernie Deitrick]

Joe,

Are you entering it using Ctrl-Shift-Enter instead of just Enter? USe tis version:

=INDEX(A:A,MIN(IF(C2:C21>=1.2402,ROW(C2:C21),100000)))


HTH,
Bernie
MS Excel MVP

 

[Joe Miller]

It works. Thanks a heap Bernie. I would never have been able to do tha
in half an eternity.

What does Ctrl-Shift-Enter do? Also what does the 100000 parameter do?
I have looked in 'help' for an explanation of the use o
Ctrl-Shift-Enter.

Thanks again. I am now saving for your Christmas present.

Joe Miller

 

[Bernie Deitrick]

Joe,

It works. Thanks a heap Bernie.

You're quite welcome.

What does Ctrl-Shift-Enter do? Also what does the 100000 parameter do?

The 100000 is just a number that is bigger than ROW() can ever return - at
least for versions of Excel currently in use. For your example, we could
have used 22..

Ctrl-Alt-Delete tells Excel to make the formula an array-formula, where it
treats each cell in the range separately first, rather than all at once.

Thanks again. I am now saving for your Christmas present.

Keep saving..... I have extravagent tastes ;-)

Bernie