Return the Name Next to the Highest Value in Excel

To return the name of the highest value in Excel, you combine three functions: MAX finds the top number, MATCH finds which row it's in, and INDEX returns the name from that row. The formula is =INDEX(A2:A8,MATCH(MAX(C2:C8),C2:C8,0)), and Microsoft 365 users can use a shorter XLOOKUP. This guide shows both, plus the lowest value, ties, the top name per group and a top 3 list.

The sample data#

All the examples use this sales list in A1:C8:

MAX alone tells you the top sales figure is 53,100. The question is who made it.

How to return the name of the highest value with INDEX and MATCH#

Use INDEX and MATCH around MAX. This works in every version of Excel:

=INDEX(A2:A8,MATCH(MAX(C2:C8),C2:C8,0))

Read it from the inside out:

1. MAX(C2:C8) returns the highest sales figure: 53,100. The MAX function guide covers it in detail.
2. MATCH(53100,C2:C8,0) finds where that value sits in the list: position 3. The 0 means exact match. See the MATCH function guide.
3. INDEX(A2:A8,3) returns the 3rd name in the list: Chen. See the INDEX function guide.

The name range and the value range must start on the same row and be the same size. If names are in A2:A8, values must be in C2:C8, not C1:C7, or the formula returns the wrong person. For a full walkthrough of the pattern, read the guide to INDEX MATCH in Excel.

The shorter XLOOKUP version#

In Excel 2021 or Microsoft 365, XLOOKUP does the lookup in one function:

=XLOOKUP(MAX(C2:C8),C2:C8,A2:A8)

XLOOKUP looks for the MAX value in C2:C8 and returns the matching name from A2:A8: Chen. It defaults to an exact match, so you don't need a 0. The guide to the XLOOKUP function covers its other options.

VLOOKUP can't do this with this layout, because the names are to the left of the values and VLOOKUP only looks to the right.

How to return the name of the lowest value#

Swap MAX for MIN and keep everything else:

=INDEX(A2:A8,MATCH(MIN(C2:C8),C2:C8,0))

MIN returns 36,700, which is in position 7, so the result is Gina. The guide to AVERAGE, MIN and MAX explains these summary functions, and the AVERAGE, MIN and MAX lesson lets you practise them.

What happens when two people tie#

INDEX MATCH and XLOOKUP stop at the first match from the top. If Eli also had 53,100, both formulas would still return Chen, because Chen is higher in the list. Nothing tells you there was a tie.

To list every name with the highest value, use FILTER (Excel 2021 or Microsoft 365):

=FILTER(A2:A8,C2:C8=MAX(C2:C8))

FILTER keeps every row where the sales equal the maximum, and the names spill into the cells below. To show them in one cell, wrap it in TEXTJOIN:

=TEXTJOIN(", ",TRUE,FILTER(A2:A8,C2:C8=MAX(C2:C8)))

With a tie this returns Chen, Eli. Without one, it returns just Chen. To check for a tie in any version, =COUNTIF(C2:C8,MAX(C2:C8)) returns how many rows share the top value.

The name of the highest value in one group#

To find the top rep in one region, first get that region's highest value with MAXIFS (Excel 2019 or later):

=MAXIFS(C2:C8,B2:B8,"South")

This returns 50,250. Then look up the name, checking both the region and the value:

=XLOOKUP(1,(B2:B8="South")*(C2:C8=MAXIFS(C2:C8,B2:B8,"South")),A2:A8)

Each comparison returns TRUE or FALSE. Multiplying them gives 1 only in the row where both are true, and XLOOKUP finds that 1. The result is Eli.

Why not just MATCH(MAXIFS(...),C2:C8,0)? Because the same number could appear in another region higher up the list, and MATCH would return that person instead. Checking the region as well avoids the trap. The guide to MAXIFS and MINIFS has more examples of conditional maximums.

The names of the top 3 values#

LARGE returns the 2nd, 3rd or nth highest value, so you can use it in place of MAX:

=INDEX($A$2:$A$8,MATCH(LARGE($C$2:$C$8,E2),$C$2:$C$8,0))

Put 1, 2 and 3 in E2:E4 and fill the formula down. You get Chen, Eli and Ben (53,100, 50,250 and 47,800). With ties, this repeats the first tied name, so check for duplicates. The guide to top N lists with LARGE and SMALL handles that case.

In Microsoft 365, one formula does it:

=TAKE(SORTBY(A2:A8,C2:C8,-1),3)

SORTBY sorts the names by sales from highest to lowest, and TAKE keeps the first 3. TAKE needs Microsoft 365 or Excel 2024.

Common mistakes#

These are the slips that most often return the wrong name:

1. Leaving out the 0 in MATCH. Without it, MATCH uses an approximate match, which needs sorted data and can return the wrong row.
2. Ranges that don't line up. A2:A8 with C3:C9 shifts every result by one row.
3. Numbers stored as text. MAX ignores them, so a text "60,000" can't be the top value even if it's the biggest.
4. Ignoring ties. Check with COUNTIF before you report one winner.
5. Using MAX on a whole column with a total row. If C9 holds the total, MAX returns the total and the name next to it is "Total".

Practise it#

This pattern, a lookup wrapped around MAX or MIN, appears in many Excel tests. Practise INDEX and MATCH in a live spreadsheet: you build lookups and the sheet checks each formula instantly. Then try the XLOOKUP version in the XLOOKUP lesson, and the summary functions in the AVERAGE, MIN and MAX lesson.