CODEWITHSUNDEEP
excelcompareexcel-formula
Nick
Nick

Reputation: 547

Excel - Find Highest Value of a Column in all Matching Rows (With Screenshot)

I have a spreadsheet that keeps track of products. In it's simplest form, the important columns are "Name", "Release Date", and "Newest?". There could be multiple rows that have the same value in the "Name" column, but they will have different values in "Release Date". I'm looking for a function that will first look in the "Name" column of the row and find all other rows that have the same value. Then, compare the "Release Date" and find the highest one. The row that has the highest "Release Date" or that doesn't have any other matching "Name" values would return "YES" and all others would return "NO". I have a screenshot of what I would like it to look like: Example

I know that to find if there is another row with the same value in the "Name" column I'd use MATCH and a failure in MATCH would result in my "YES" output. It's the comparing all the "Release Date" values that has me stumped. I would like this to be a formula as I have no experience with VB.

Thanks for the help!

Upvotes: 1

Views: 7928

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

You can use following array formula:

=IF(B2=MAX(IF($A:$A=A2,$B:$B)),"YES","NO")

just select C2, enter formula in formula bar, press CTRL+SHIFT+ENTER to evaluate it, and then drag it down.

P.S. you can change $A:$A and $B:$B to the exact ranges if you know them (i.g. $A$2:$A$100 and $B$2:$B$100)

Upvotes: 1

Related Questions