Get the highest value from the range (no VBA)

[jaryszek]

Hello Guys,

I have 2 columns, one with names and second with attached to them dates.

I want to have the highest value for each name (in workbook there is an example).

Please find in attachment example workbook.

Please help,
Jacek Antek

[chullan88]

=MAX(IF('Source Data'!A2:A14='Output data'!A2,'Source Data'!B2:B14))

Entered as array formula

[Glenn Kennedy]

=MAX(IF('Source Data'!$A$2:$A$14=A2,'Source Data'!$B$2:$B$14))
array enter this formul and copy down.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Glenn Kennedy]

chullan 88's formula will work with this dataset, by good luck. You need the lock the ranges as shown, using the $ signs....

[chullan88]

Thanks Glenn for the correction..

A non-array solution adapted from one of tigeravatar's old posts...

=MAX(INDEX(('Source Data'!$A$2:$A$14='Output data'!A2)*'Source Data'!$B$2:$B$14,))

[avk]

Try also below.
=IF(COUNTIF(Source_Data!$A$2:$A$14,Output_data!$A2),MAX(IF(Source_Data!$A$2:$A$14=Output_data!$A2,Source_Data!$B$2:$B$14)),"")
Ensure Array formula [SHIFT+CTRL+ENTER]

Copy paste down.

[jaryszek]

Hi Guys,

thank you very much for your help!

The best solution for me is from Glenn Kennedy.

Question is how the array formula is working here.

If i remove arrayformula from the first name it works.
But for second name is not working.

Best Wishes,
Jacek Antek

[Glenn Kennedy]

If you don't set the array, Excel evaluates only the first line of the "IF" range (output A2). So Pawel = Pawel and it returns the correct MAX. On the second line (output a3) Pawel not equal to Lukasz, so it falls over and returns a value of zero, which in Excel date terms equals the 0th january 1900. Don't ask about the date, that's another story...

[jaryszek]

Thank you Glenn for youe help and support, you are the best!

Jacek