Google Sheets – How to Find Max Value for a Name

google sheetsgoogle-sheets-query

I have spreadsheet with 2 columns – names and values, say:

John 2
Doe 8
Mary 17
John 5
Bill 12
Mary 6
John 3

What formula can I use to find the maximum value for each name? (E.g. in column C in each row with John would be 5, with Mary – 17, etc.).

Best Answer

In my opinion simplest by far but offering much more than just what you ask is a pivot table:

You could though try:

=query(I:J,"select I, max(J) group by I")

Formula

=MATCH(MAX(E2:E4), E2:E4, 0)

If you want to include the header, then you have to use this formula:

=MATCH(MAX(E2:E4), E1:E4, 0)

Screenshot

enter image description here

Remark

The last criterium in the MATCH formula, 0 will trigger the function to find the first entry. Use 1 to show the last entry.

Example

I've created an example file for you: Get Row-Number of a MAX(Range) Value?

Google-sheets – Conditional Formatting based on another sheet’s content

Although you mention only A2 in sheet March I'm guessing you may want equivalent formatting throughout ColumnA, so start by selecting ColumnA then:

Format, Conditional formatting..., Custom formula is:

=match(A1,indirect("Roster"&"!D:D"),0)>0

select yellow (Range: should be A:A) and Save rules.

For each colour you need a separate rule (unless one colour is acceptable by default, so standard formatting applied for that) hence for red for example + Add another rule of:

=match(A1,indirect("Roster"&"!E:E"),0)>0

and repeat for blue (B:B in place of D:D in the first formula) and green (C:C).

Best Answer

Related Solutions

Google Sheets – Get Row Number of MAX(Range) Value

Formula

Screenshot

Remark

Example

Google-sheets – Conditional Formatting based on another sheet’s content

Related Topic