How to find first cell in range greater than X, if there is such a cell?

Howdy Excel Brainiacs!

I need a formula that looks at a range of rows (say, A10:A100) and finds the first cell with a value greater than X. I need to identify the cell address, not the value in the cell! If there is no such cell, then it can return some other value.

Seems like it should be easy, but I've been at it for hours trying to use various combinations of VLOOKUP and MAX and ADDRESS and SEARCH and INDIRECT, and I just can't make it work!

Can anyone help?


Thanks!

=ADDRESS(MATCH(TRUE,INDEX(A10:A100>A1,0),0)+9,1) where a1 has your value X
or
=IF(ISERROR(MATCH(TRUE,INDEX(A10:A100>A3,0),0)),"some message",ADDRESS(MATCH(TRUE,INDEX(A10:A100>A3,0),0)+9,1))

Thanks for your help! But I must be a dummy - I can't quite get either of those formulas to work .

Even when the conditions are true, the address returned is always the same - $A$12. When I try changing the Address function parameters, I just get #N/A... not sure what I'm doing wrong.

Thanks again!


EDIT: I think I've figured out how the MATCH portion of the function works, but I'm still struggling to use ADDRESS to turn its output into a cell reference all within the same formula ...

Here's another one...

A1 = X value

Array entered**:

=IF(COUNTIF(A10:A100,">"&A1),ADDRESS(MATCH(TRUE,A10:A100>A1,0)+ROW(A10)-1,1,4),"NA")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

=ADDRESS(MATCH(TRUE,INDEX(A10:A100>A1,0),0)+9,1) where a1 has your value X
or
=IF(ISERROR(MATCH(TRUE,INDEX(A10:A100>A1,0),0)),"some message",ADDRESS(MATCH(TRUE,INDEX(A10:A100>A1,0),0)+9,1)) typo there by me
you might want
=IF(A1="","",ADDRESS(MATCH(TRUE,INDEX(A10:A100>A1,0),0)+9,1))
or
=IF(A1="","",IF(ISERROR(MATCH(TRUE,INDEX(A10:A100>A1,0),0)),"some message",ADDRESS(MATCH(TRUE,INDEX(A10:A100>A1,0),0)+9,1))) in case you want no return when a1 is blank

Let's assume this is the data:

Data Range

	A	B	C
1	61		3
2
3	12
4	23
5	65
6	10
7	2
8	------	------	------

This formula entered in C1:

=MATCH(TRUE,INDEX(A3:A7>A1,0),0)

The INDEX function generates an array of TRUE or FALSE values from this logical test:

A3:A7>A1

The individual elements of the array are:

A3 > A1 = 12 greater than 61 = FALSE
A4 > A1 = 23 greater than 61 = FALSE
A5 > A1 = 65 greater than 61 = TRUE
A6 > A1 = 10 greater than 61 = FALSE
A7 > A1 = 2 greater than 61 = FALSE

Now we have:

=MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE},0)

The lookup value TRUE is found at the 3rd element of the array {FALSE;FALSE;TRUE;FALSE;FALSE}

So:

=MATCH(TRUE,INDEX(A3:A7>A1,0),0)

=3

You're welcome. Thanks for the feedback!