Problem is to find max Row() number for a value in column. Lets imagine that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

Vlado,

Array enter (enter using Ctrl-Shift-Enter)

=MAX(IF(A1:A7=100,ROW(A1:A7)))

HTH,
Bernie
MS Excel MVP

"Vlado Sveda" wrote in message
...
Problem is to find max Row() number for a value in column. Lets imagine
that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

=MAX(INDEX((A1:A10=100)*ROW(A1:A10),0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Vlado Sveda" wrote in message
...
Problem is to find max Row() number for a value in column. Lets imagine
that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

I see that you've used these newsgroups before, so I'm confident that this
isn't homework (right?).

So....Try one of these:
=LOOKUP(10^99,((A1:A7=100)*ROW(A1:A7))/(A1:A7=100))

or
=SUMPRODUCT(LARGE((A1:A7=100)*ROW(A1:A7),1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vlado Sveda" wrote:

Problem is to find max Row() number for a value in column. Lets imagine that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=MAX((A1:A1000=MAX(A1:A1000))*ROW(A1:A1000))

In article ,
Vlado Sveda wrote:

Problem is to find max Row() number for a value in column. Lets imagine that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

Nice and compact, Bob...(I wish I'd thought of it!)

***********
Regards,
Ron

XL2002, WinXP


"Bob Phillips" wrote:

=MAX(INDEX((A1:A10=100)*ROW(A1:A10),0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Vlado Sveda" wrote in message
...
Problem is to find max Row() number for a value in column. Lets imagine
that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

Thanks a lot Ron, this is what I need - you helped me much.
Vlado

P.S. It is not my homework - I'm pretty old for homeworks :-))

"Ron Coderre" wrote:

I see that you've used these newsgroups before, so I'm confident that this
isn't homework (right?).

So....Try one of these:
=LOOKUP(10^99,((A1:A7=100)*ROW(A1:A7))/(A1:A7=100))

or
=SUMPRODUCT(LARGE((A1:A7=100)*ROW(A1:A7),1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vlado Sveda" wrote:

Problem is to find max Row() number for a value in column. Lets imagine that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

Thanks Bernie, works great !

Vlado

"Bernie Deitrick" wrote:

Vlado,

Array enter (enter using Ctrl-Shift-Enter)

=MAX(IF(A1:A7=100,ROW(A1:A7)))

HTH,
Bernie
MS Excel MVP

"Vlado Sveda" wrote in message
...
Problem is to find max Row() number for a value in column. Lets imagine
that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

Great one ! Thank you !

Vlado

"Bob Phillips" wrote:

=MAX(INDEX((A1:A10=100)*ROW(A1:A10),0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Vlado Sveda" wrote in message
...
Problem is to find max Row() number for a value in column. Lets imagine
that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

Thank you !

Vlado

"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=MAX((A1:A1000=MAX(A1:A1000))*ROW(A1:A1000))

In article ,
Vlado Sveda wrote:

Problem is to find max Row() number for a value in column. Lets imagine that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

Regarding:
P.S. It is not my homework - I'm pretty old for homeworks :-))

One can be "pretty old for homework" and still not be too old for homework.
<vbg

....and thanks for the feedback......it's much appreciated.

***********
Regards,
Ron

XL2002, WinXP


"Vlado Sveda" wrote:

Thanks a lot Ron, this is what I need - you helped me much.
Vlado

P.S. It is not my homework - I'm pretty old for homeworks :-))

"Ron Coderre" wrote:

I see that you've used these newsgroups before, so I'm confident that this
isn't homework (right?).

So....Try one of these:
=LOOKUP(10^99,((A1:A7=100)*ROW(A1:A7))/(A1:A7=100))

or
=SUMPRODUCT(LARGE((A1:A7=100)*ROW(A1:A7),1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vlado Sveda" wrote:

Problem is to find max Row() number for a value in column. Lets imagine that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

Regarding:
P.S. It is not my homework - I'm pretty old for homeworks :-))

One can be "pretty old for homework" and still not be too old for homework.
<vbg

....and thanks for the feedback......it's much appreciated.

***********
Regards,
Ron

XL2002, WinXP


"Vlado Sveda" wrote:

Thanks a lot Ron, this is what I need - you helped me much.
Vlado

P.S. It is not my homework - I'm pretty old for homeworks :-))

"Ron Coderre" wrote:

I see that you've used these newsgroups before, so I'm confident that this
isn't homework (right?).

So....Try one of these:
=LOOKUP(10^99,((A1:A7=100)*ROW(A1:A7))/(A1:A7=100))

or
=SUMPRODUCT(LARGE((A1:A7=100)*ROW(A1:A7),1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vlado Sveda" wrote:

Problem is to find max Row() number for a value in column. Lets imagine that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado

=SUMPRODUCT(MAX((A1:A7=100)*ROW(A1:A7)))


"Vlado Sveda" wrote:

Problem is to find max Row() number for a value in column. Lets imagine that
you have column like this:
A1: 1
A2: 2
A3: 100
A4: 1
A5: 1
A6: 100
A7: 3

What I need is to get max Row() for value (100 in this example) without
using VBA. (So in my example it'll return 6.)

Thanks to all in advance !

Vlado