Table Searching

[Joe Miller]

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

[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,D6:D8,1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Joe Miller" <[email protected]> wrote in message
news:[email protected]...
|
| 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...o&userid=29900
| View this thread: http://www.excelforum.com/showthread...hreadid=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(D1:D20<=given value,ROW(D1:D20),100000)))

Expand C1:C20 and D1:D20 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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...o&userid=29900
> View this thread: http://www.excelforum.com/showthread...hreadid=569412
>

[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,D6:D8,1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Joe Miller" <[email protected]> wrote in message
news:[email protected]...
|
| 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...o&userid=29900
| View this thread: http://www.excelforum.com/showthread...hreadid=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(D1:D20<=given value,ROW(D1:D20),100000)))

Expand C1:C20 and D1:D20 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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...o&userid=29900
> View this thread: http://www.excelforum.com/showthread...hreadid=569412
>

[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

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,D6:D8,1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Joe Miller" <[email protected]> wrote in message
news:[email protected]...
|
| 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...o&userid=29900
| View this thread: http://www.excelforum.com/showthread...hreadid=569412
|

[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

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,D6:D8,1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Joe Miller" <[email protected]> wrote in message
news:[email protected]...
|
| 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...o&userid=29900
| View this thread: http://www.excelforum.com/showthread...hreadid=569412
|

[Bernie Deitrick]

Joe,

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

HTH,
Bernie
MS Excel MVP

> Maybe Bernie's solution will work?
> Joe Miller

[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,

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

HTH,
Bernie
MS Excel MVP

> Maybe Bernie's solution will work?
> Joe Miller

[Joe Miller]

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

Joe,

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

HTH,
Bernie
MS Excel MVP

> Maybe Bernie's solution will work?
> Joe Miller

[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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
>
> Bernie Deitrick Wrote:
>> Joe,
>>
>> Maybe??? Of course it will, otherwise, I wouldn't have posted it
>> ;-)
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> > Maybe Bernie's solution will work?
>> > Joe Miller
>
>
> --
> Joe Miller
> ------------------------------------------------------------------------
> Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900
> View this thread: http://www.excelforum.com/showthread...hreadid=569412
>

[Joe Miller]

It works. Thanks a heap Bernie. I would never have been able to do that 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 of Ctrl-Shift-Enter.

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

Joe Miller

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
>
> Bernie Deitrick Wrote:
>> Joe,
>>
>> Maybe??? Of course it will, otherwise, I wouldn't have posted it
>> ;-)
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> > Maybe Bernie's solution will work?
>> > Joe Miller
>
>
> --
> Joe Miller
> ------------------------------------------------------------------------
> Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900
> View this thread: http://www.excelforum.com/showthread...hreadid=569412
>

[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