Location of the first greater than cell

  • Thread starter Thread starter crazyquasar
  • Start date Start date
C

crazyquasar

I have a huge unsorted list of numbers (more than 100,000). I want to locate
the first instance of a number greater than the first number in this list.
any help will be greatly appreciated.

thank you,
 
T Valko's answer in "locate first value greater than" solves the issue. His
answer is

"Another one:

=INDEX(B1:F1,MATCH(TRUE,INDEX(B2:F2>0,0),0))

Format as Date
 
I am looking to do the same thing, except instead of returning a value I would like excel to tell me the cell location of the first value greater than a certain value.

In my case I have cells A2:ZY2 full of data, and I am looking to find the first cell that has a greater value than the value in cell B6.

So far I have entered:

=INDEX(JA2:ZY2,MATCH(TRUE,INDEX(JA2:ZY2>B6,0),0))

but like I said this is returning a value when what I am looking for is a cell location.

Any help would be appreciated!

Thank you!
 
If you reduce the formula to this:

=MATCH(TRUE,INDEX(JA2:ZY2>B6,0),0)

it will return the (relative) column number, but as your range begins
with column A then it is also the absolute column number. Try wrapping
an ADDRESS function around this (check it out in Excel Help).

Hope this helps.

Pete
 
Back
Top