Find max Row() number for a value in column

[Guest]

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

 

[Bernie Deitrick]

Vlado,

Array enter (enter using Ctrl-Shift-Enter)

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

HTH,
Bernie
MS Excel MVP

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[Guest]

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

 

[JE McGimpsey]

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

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

 

[Guest]

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

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

XL2002, WinXP

 

[Guest]

Thank you !

Vlado

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

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

 

[Guest]

Great one ! Thank you !

Vlado

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

--
HTH

Bob Phillips

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

 

[Guest]

Regarding:
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

 

[Guest]

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