How to look up a manager's name from the same employee table in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(C2,$A$2:$A$9,$B$2:$B$9,"")Look up each manager ID in the table's own ID column: =XLOOKUP(C2,$A$2:$A$9,$B$2:$B$9,""), filled down.
- Result on the example
- one result per row in
D2, filled down toD9 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in D2, filled down to D9. Change any input and the result updates, or edit the formula to experiment.
How it works
The manager is just another employee, so the lookup searches the same table: it finds the manager ID from column C in the ID column A and returns the name from column B. Grace Kim has no manager, so the lookup finds nothing and returns the "" given as the 4th argument.
=XLOOKUP(C2,$A$2:$A$9,$B$2:$B$9,"")Result""
C2(empty)The value in C2.
$A$2:$A$98 rows × 1 col: "E-100", "E-101", "E-102", "E-103", …A range: 8 cells in one column.
$B$2:$B$98 rows × 1 col: "Grace Kim", "Marcus Lee", "Priya Nair", "Tom Becker", …A range: 8 cells in one column.
XLOOKUP(C2,$A$2:$A$9,$B$2:$B$9,"")""XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
C2(empty) - lookup_array
$A$2:$A$98 rows × 1 col: "E-100", "E-101", "E-102", "E-103", … - return_array
$B$2:$B$98 rows × 1 col: "Grace Kim", "Marcus Lee", "Priya Nair", "Tom Becker", … - if_not_found
""
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(C2,$A$2:$A$9,$B$2:$B$9,""). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IFNA(INDEX($B$2:$B$9,MATCH(C2,$A$2:$A$9,0)),"") | , Grace Kim, Grace Kim… | The same lookup for Excel 2019 and earlier. |
Common mistakes
- Lock the table with
$, but keepC2relative so each row looks up its own manager. - A manager ID that isn't in the table also returns blank. Use a message like "Unknown" if you want to spot those.
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.
Look up a manager's name from the same employee table
D2, return the manager's name (blank for the CEO), then fill down to D9.Questions
How do I get the manager's manager?
Nest the lookup: =XLOOKUP(XLOOKUP(C2,$A$2:$A$9,$C$2:$C$9,""),$A$2:$A$9,$B$2:$B$9,"").
How do I count each manager's direct reports?
Count their ID in the manager column: =COUNTIF($C$2:$C$9,A2).
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.