How to find the name of the second highest value in Excel
Excel formula
=INDEX(SORTBY(A2:A9,B2:B9,-1),E1)Sort the names by value and pick the one at that rank: =INDEX(SORTBY(A2:A9,B2:B9,-1),E1), with 2 in E1.
- Result on the example
- Ben in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
SORTBY puts the names in order of score, highest first: Finn, Ben, Dan, and so on. INDEX picks the name at the rank in E1. Because each name keeps its own position, two students with the same score both appear, one after the other.
Google Sheets has no SORTBY; SORT the two columns by column 2 and take the first column.
=INDEX(SORTBY(A2:A9,B2:B9,-1),E1)Result"Ben"
A2:A98 rows × 1 col: "Ava", "Ben", "Chloe", "Dan", …A range: 8 cells in one column.
B2:B98 rows × 1 col: 88, 94, 79, 91, …A range: 8 cells in one column.
-1-1Makes the value negative.
SORTBY(A2:A9,B2:B9,-1){8 rows × 1 col} "Finn", "Ben", "Dan", "Ava", …SORTBY: Sorts a range by other ranges. Spills.- array
A2:A98 rows × 1 col: "Ava", "Ben", "Chloe", "Dan", … - by_array1
B2:B98 rows × 1 col: 88, 94, 79, 91, … - sort_order1
-1-1
- array
E12The value in E1.
INDEX(SORTBY(A2:A9,B2:B9,-1),E1)"Ben"INDEX: Value at a given row and column of a range.- array
SORTBY(A2:A9,B2:B9,-1){8 rows × 1 col} "Finn", "Ben", "Dan", "Ava", … - row_num
E12
- array
In Google Sheets
Google Sheets needs a different formula: =INDEX(SORT(A2:B9,2,FALSE),E1,1). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A9,MATCH(LARGE(B2:B9,E1),B2:B9,0)) | Ben | Every Excel version: LARGE finds the 2nd-highest score and MATCH finds its row. With tied scores it returns the same name twice. |
| =XLOOKUP(LARGE(B2:B9,E1),B2:B9,A2:A9) | Ben | The XLOOKUP form of the same idea, with the same weakness on ties. |
Common mistakes
- With tied scores, LARGE returns the same score for ranks 2 and 3, and a lookup returns the first student both times. SORTBY gives each student a place.
- A rank larger than the number of students returns #REF!.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the name of the second highest value
E2, return the name of the student with the score ranked E1 from the top (2 means second highest).Questions
How do I list the top 3 names?
Take the first three: =TAKE(SORTBY(A2:A9,B2:B9,-1),3).
How do I find the second lowest?
Sort ascending: =INDEX(SORTBY(A2:A9,B2:B9,1),2).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.