Lookup for a maximum value in a range

[Mano]

Hi,

I need a combination of lookup and max formulae. In a range, I want to find the maximum value of a particular cell. In the table below, each ID is at a different stage. I want a formula that can return the highest stage number for that ID. This is a simple table, while my data is huge. and the IDs are a combination of text, numbers and symbols. "Max Stage Value" column is the RESULT I am LOOKING for...

would any Ninja, kindly help me on this?
Also attaching the spread sheet...















Thanks for trying!
Regards,

 

[Khalid NGO]

Hi Mano,
Try this with CSE:

=MAX(IF($D$4:$D$36=D4,$C$4:$C$36,0))
copy down.

Regards,

 

[Mano]

thanks ... Is there an easy way to apply Ctrl+Shift +Enter for the rest of the rows in the range? I have some 10000 rows running?

 

[Khalid NGO]

thanks ... Is there an easy way to apply Ctrl+Shift +Enter for the rest of the rows in the range? I have some 10000 rows running?

Yes, you can convert your data with Table, formula will become structured and you'll have to enter it just once.

 

[Mano]

Wow! thats awesome. Thanks Khalid

 

[Khalid NGO]

Wow! thats awesome. Thanks Khalid

You are welcome...
Thanks for the feedback.
Take care.

 

[Deepak]

Some more ways..

{=LARGE(($D$4:$D$36=D4)*($C$4:$C$36),1)}
{=MAX(($D$4:$D$36=D4)*$C$4:$C$36)}


non array
=MAX(INDEX(($D$4:$D$36=D4)*$C$4:$C$36,,))
=LARGE(INDEX(($D$4:$D$36=D4)*$C$4:$C$36,,),1)

 

[Khalid NGO]

Some more ways..

{=LARGE(($D$4:$D$36=D4)*($C$4:$C$36),1)}
{=MAX(($D$4:$D$36=D4)*$C$4:$C$36)}


non array
=MAX(INDEX(($D$4:$D$36=D4)*$C$4:$C$36,,))
=LARGE(INDEX(($D$4:$D$36=D4)*$C$4:$C$36,,),1)

that's beauty
Thanks Deepak Sir for sharing

 

[Deepak]

Beauty of excel.

non array

=SUMPRODUCT(MAX(($D$4:$D$36=D4)*$C$4:$C$36))

=index(MAX(($D$4:$D$36=D4)*$C$4:$C$36),,)