Locating position of cell when it is bigger than specified value

Hello guys!

My problem is as follows. I have 2 columns:

Col1 --- Col 2
1 --- 100
2 --- 95
3 --- 80
4 --- 70
5 --- 60
6 --- 115
7 --- 120
8 --- 130
9 --- 10
10 --- 35


I want to have a function that searches the first instance in column 2 where the value is higher than the one in the first cell of column 2 (that is, the first instance where the value is higher than 100) and that returns the corresponding number of column 1.
So in this exercise, I would want the first number in column 2 bigger than 100 - which would be 115 - and then would want the corresponding number from column 1 - which is 6. (I only need the number from column 1, that is, the 6)

Is there a function or combination of functions that does that?


Thank you very much

You could use this array* formula:

=IFERROR(INDEX(A:A,MIN(IF(B$2:B$100>$B$1,ROW(B$2:B$100)))),"")

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual < Enter >.

Change the range (i.e. $100) to suit your data.

Hope this helps.

Pete

Try

=INDEX($A$2:$A$11,MATCH(TRUE,INDEX($B$2:$B$11>B2,0),0))

Or try
=SUMPRODUCT(--(AGGREGATE(15,6,EXP(LN($B$2:$B$11-$E$2)),1)=($B$2:$B$11-$E$2))*$A$2:$A$11)

Worked perfectly, thank you!

You're welcome.