Location of the first greater than cell

crazyquasar

unread,

Jan 18, 2010, 1:22:01 AM1/18/10

to

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,

crazyquasar

unread,

Jan 18, 2010, 2:24:01 AM1/18/10

to

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

--
Biff
Microsoft Excel MVP"

Nikki Scheerer

unread,

Sep 14, 2010, 9:49:56 AM9/14/10

to

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!

> On Monday, January 18, 2010 1:22 AM crazyquasar wrote:

> 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,

>> On Monday, January 18, 2010 2:24 AM crazyquasar wrote:

>> 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
>>
>> --
>> Biff
>> Microsoft Excel MVP"
>>
>>
>> "crazyquasar" wrote:

>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>> MongoDb vs SQL Server Basic Speed Tests
>> http://www.eggheadcafe.com/tutorials/aspnet/6f573869-c8eb-40c3-9946-2f61e0163966/mongodb-vs-sql-server-basic-speed-tests.aspx

Pete_UK

unread,

Sep 14, 2010, 10:17:06 AM9/14/10

to

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

> >>http://www.eggheadcafe.com/tutorials/aspnet/6f573869-c8eb-40c3-9946-2...- Hide quoted text -
>
> - Show quoted text -

Todd Parker

unread,

Sep 1, 2021, 6:24:44 PM9/1/21

to

Just letting you know it is now 9/1/2021 this post from 2010 was EXTREMELY useful. Thx tons!!!!! Dig the spreadsheet genius's out there.

Graeme Erickson

unread,

Apr 2, 2022, 1:51:13 PM4/2/22

to

On Wednesday, September 1, 2021 at 3:24:44 PM UTC-7, Todd Parker wrote:
> Just letting you know it is now 9/1/2021 this post from 2010 was EXTREMELY useful. Thx tons!!!!! Dig the spreadsheet genius's out there.

Hello from 2022. Just helped me, too. Thanks!

Silviu Bîtă

unread,

Jul 20, 2022, 11:42:59 AM7/20/22

to

Great ! Thanks a lot!!

Dixon Romney (The Well Nourished Family)

unread,

Feb 28, 2023, 12:42:14 PM2/28/23

to

Hello from 2023!

Yep...still works and helped me COMPLETE the worksheet I was creating! Thank you! THANK YOU!!!