Index Using Threshold Value

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@bctonline.com

Hi!

Try this:

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

=INDEX(C2:C4,MATCH(TRUE,B2:B4>A1,0))

Biff

"chaz" <chaz.2838mm_1148098201.1132@excelforum-nospam.com> wrote in message
news:chaz.2838mm_1148098201.1132@excelforum-nospam.com...
>
> 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@bctonline.com
>
>
> --
> chaz
> ------------------------------------------------------------------------
> chaz's Profile:
> http://www.excelforum.com/member.php...o&userid=34616
> View this thread: http://www.excelforum.com/showthread...hreadid=543915
>

Biff,
That works! I can now select data based on threshold values.
Regards,
Chaz

You're welcome. Thanks for the feedback!

Biff

"chaz" <chaz.288cw0_1148337005.1141@excelforum-nospam.com> wrote in message
news:chaz.288cw0_1148337005.1141@excelforum-nospam.com...
>
> Biff,
> That works! I can now select data based on threshold values.
> Regards,
> Chaz
>
>
> --
> chaz
> ------------------------------------------------------------------------
> chaz's Profile:
> http://www.excelforum.com/member.php...o&userid=34616
> View this thread: http://www.excelforum.com/showthread...hreadid=543915
>