software

This problem involves time-ordered stock market data.
I need to look up the first value in a column that crosses a threshold
value and return the corresponding value from the same row in a
different column.

Example:
Column A has the date
Column B has daily % gain values
Column C has corresponding total dollar values

A B C
0.3%
5/6 1.2% $20,000
5/7 0.5% $20,100
5/8 1.0% $20,301

I want to use the value in A1 (0.3%) and find the first value in column
B that exceeds it (B2 , 1.2%) and return the corresponding value from
column C (C3, $20,000).

From reading this forum it appears to me that the INDEX function
coupled with perhaps the MATCH function should be able to do this. I
just can't seem to get there.

Please advise.
Regards,
Chaz
--
chaz
------------------------------------------------------------------------
chaz's Profile: www.excelforum.com/member.php...oamp;userid=34616
View this thread: www.excelforum.com/showthread...hreadid=543915Hi!

Try this:

Enter this formula as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(C2:C4,MATCH(TRUE,B2:B4gt;A1,0))

Biff

quot;chazquot; gt; wrote in message
...
gt;
gt; This problem involves time-ordered stock market data.
gt; I need to look up the first value in a column that crosses a threshold
gt; value and return the corresponding value from the same row in a
gt; different column.
gt;
gt; Example:
gt; Column A has the date
gt; Column B has daily % gain values
gt; Column C has corresponding total dollar values
gt;
gt; A B C
gt; 0.3%
gt; 5/6 1.2% $20,000
gt; 5/7 0.5% $20,100
gt; 5/8 1.0% $20,301
gt;
gt; I want to use the value in A1 (0.3%) and find the first value in column
gt; B that exceeds it (B2 , 1.2%) and return the corresponding value from
gt; column C (C3, $20,000).
gt;
gt; From reading this forum it appears to me that the INDEX function
gt; coupled with perhaps the MATCH function should be able to do this. I
gt; just can't seem to get there.
gt;
gt; Please advise.
gt; Regards,
gt; Chaz
gt;
gt;
gt;
gt; --
gt; chaz
gt; ------------------------------------------------------------------------
gt; chaz's Profile:
gt; www.excelforum.com/member.php...oamp;userid=34616
gt; View this thread: www.excelforum.com/showthread...hreadid=543915
gt;

Biff,
That works! I can now select data based on threshold values.
Regards,
Chaz--
chaz
------------------------------------------------------------------------
chaz's Profile: www.excelforum.com/member.php...oamp;userid=34616
View this thread: www.excelforum.com/showthread...hreadid=543915You're welcome. Thanks for the feedback!

Biff

quot;chazquot; gt; wrote in message
...
gt;
gt; Biff,
gt; That works! I can now select data based on threshold values.
gt; Regards,
gt; Chaz
gt;
gt;
gt; --
gt; chaz
gt; ------------------------------------------------------------------------
gt; chaz's Profile:
gt; www.excelforum.com/member.php...oamp;userid=34616
gt; View this thread: www.excelforum.com/showthread...hreadid=543915
gt;