find data in column that meets parameter

[bergy]

I have a two columns of data that exists in the range A5:B1199. Column A is time stamps and column B is temperatures. I need to find the first occurrence in column B when the temperature rises above a certain point and then display the corresponding time from column A. Ideally, I want to be able to enter a temperature set point in B2 and then have a formula in B3 that would display the time stamp. I know I could write a macro for this, but I was hoping that someone might know of a way of doing this with a formula.

 

[sulakvea]

sort the entire section (A5:B1199) descending by both A and B (first condition A, then B). this is the formula:

=INDEX(A5:A1199,MATCH(B2,B5:B1199,-1))

 

[bergy]

That works and I will use that for now, but is there a way without using the sort?

Thanks

 

[dscg]

Here is one way to do this with a variation - one uses Sumproduct, the other is an array formula confirmed with CSE. Data does not need to be sorted by temp in this case - sulakvea's will work if it is sorted. Expand range to 1199.

Excel Workbook
	A	B	C
1
2		95	9:00:00 PM
3			9:00:00 PM
4
5	8:00:00 AM	65
6	9:00:00 AM	68
7	10:00:00 AM	71
8	11:00:00 AM	74
9	12:00:00 PM	77
10	1:00:00 PM	80
11	2:00:00 PM	83
12	3:00:00 PM	86
13	4:00:00 PM	84
14	5:00:00 PM	82
15	6:00:00 PM	80
16	7:00:00 PM	87
17	8:00:00 PM	90
18	9:00:00 PM	95
19	10:00:00 PM	100
20	11:00:00 PM	98
Sheet4

Excel 2007

Cell Formulas
Range	Formula
C2	=INDEX(A5:B20,SUMPRODUCT(MATCH(0,--(B5:B20),0)),1)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[bergy]

Actually, this does work because my data is initially sorted by time (column A), so I can use this with my 'raw' data. Thank you!