Finding MAX value using VLOOKUP with many duplicate "IDs"

Question

Using an Excel formula, I'm trying to pull the MAX value for a NAME that has a certain LETTER next to it.

Eg: Highest # for a specific % for each unique Name

So Jeff's Q value would be 7.

(I'm trying to over explain because it makes sense in my mind but it might not make sense to others..)

Name    %   #
Jeff    O   4
Jeff    D   3
Jeff    Q   4
Jeff    O   1
Jeff    D   9
Jeff    Q   7
Tom     O   6
Tom     D   7
Tom     Q   8
Tom     O   2
Tom     D   8
Tom     Q   3
Peter   O   3
Peter   D   8
Peter   Q   7
Peter   O   4
Peter   D   10
Peter   Q   3
Bob     O   2
Bob     D   6
Bob     Q   10
Bob     O   6
Bob     D   10
Bob     Q   9
Mark    O   4
Mark    D   7
Mark    Q   4
Mark    O   7
Mark    D   8
Mark    Q   1

I can't think of a way to run this without having dedicated worksheets for each person and running MAX on the specific column.

I've tried IF, VLOOKUP and MAX in various configurations but I get nothing.

Has anyone got any experience with this and could please point me in the right direction?

Answer 1

The MAXIFS function should be what you want. For example, assuming that you have your data in columns A:C the formula

=MAXIFS(C:C,A:A,"Jeff",B:B,"Q")

will give you the max number in column C where the value in row A is "Jeff" and the value in row B is "Q".