Return the cell address for the first instance of a specific value

[fraseringermany]

Hello,

I need a formula that will give the cell address for the cell containing the first instance of a specific value.

So, if I have the below data, I would like a formula that has the cell address of the first instance of a value greater than 0.9 which in this case would be A5

any tips?

	A
1	0.861899
2	0.873341
3	0.885011
4	0.893936
5	0.909039
6	0.916133
7	0.924371
8	0.93238

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[CodeNinja]

If the data is all going to be in one column...

=ADDRESS(AGGREGATE(15,6,ROW(A1:A8)/(A1:A8>0.9),1),1)

 

[Gary's Student]

Consider the array formula:

="A" & MATCH(MIN(IF(A1:A8>0.9,A1:A8)),A1:A8,0)

Array formulas must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key

 

[Rick Rothstein]

Same idea as Gary's Student, but a little more compact, also an array-entered formula...

="A"&MIN(IF(A1:A8>0.9,ROW(A1:A8)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself