Find an employee’s department from an ID in Excel with INDEX and MATCH
Type an employee ID, get the person's department. The lookup reads the Name and Department columns by name rather than by column number, so next month's export can gain a column without quietly shifting the answers.
Written 12 November 2026
Result
| Position of E-106 in the list | 6 |
| Name for E-106 | Omar Farouk |
| Department for E-106 | Warehouse |
| E-999 returns | No such ID |
Download the spreadsheet: employee-department-by-id.xlsx (5,470 bytes)
It contains the staff list and both lookup blocks — one for an ID that is in the list, and one for an ID that is not.
1What you need
- Excel, Google Sheets, or LibreOffice. INDEX, MATCH, and IFNA work the same in all three for this tutorial.
- The file above, or a blank workbook if you want to type the formulas in yourself.
2The staff list
The list is the shape an HR export usually arrives in: one row per person, keyed by employee ID. Eight staff, rows 3 to 10:
| Employee ID | Name | Department | Location |
|---|---|---|---|
| E-101 | Priya Nair | Finance | Toronto |
| E-102 | Tom Becker | Warehouse | Calgary |
| E-103 | Ana Ruiz | Sales | Vancouver |
| E-104 | Ken Watanabe | Support | Toronto |
| E-105 | Lucy Hart | Finance | Montreal |
| E-106 | Omar Farouk | Warehouse | Edmonton |
| E-107 | Ingrid Solberg | People | Vancouver |
| E-108 | Marc Dubois | Sales | Montreal |
The ID to look up goes in G2 — in the file it holds E-106. Everything below reads that one cell, so changing the ID re-runs the whole lookup.
3MATCH finds the row
MATCH searches for a value in a range and returns its position — a row number, not the value itself (Look up values with VLOOKUP, INDEX, or MATCH — Microsoft Support):
G4 =MATCH(G2,A3:A10,0)For E-106 the position is 6: the sixth row of the list. The 0 asks for an exact match, which an ID lookup always wants — an approximate match on IDs would happily return the row above the right one.
4INDEX returns the name and department
INDEX takes a range and a position and returns the value sitting there. Nest the MATCH inside it, once per column you want back:
G5 =IFNA(INDEX(B3:B10,MATCH(G2,A3:A10,0)),"No such ID")
G6 =IFNA(INDEX(C3:C10,MATCH(G2,A3:A10,0)),"No such ID")For E-106: name Omar Farouk, department Warehouse. A third formula of the same shape returns the location from column D — Edmonton. Each return range is named outright, which is the difference from VLOOKUP: VLOOKUP counts columns from the ID column, so a column inserted into next month's export silently shifts what it returns, while these ranges still point at Name and Department.
5An ID that is not in the list
When MATCH finds no exact match it returns #N/A, and INDEX passes that error through. IFNA catches it and returns text instead. The file's second block looks up E-999, an ID nobody holds:
G10 =IFNA(INDEX(B3:B10,MATCH(G9,A3:A10,0)),"No such ID")Both the name and the department cells read “No such ID” — a sentence a person can act on, where #N/A just looks like a broken sheet.
Traps
Forgetting the 0 in MATCH. Without a match type, MATCH does an approximate match that expects sorted data. An unsorted staff list then returns a position that has nothing to do with the ID you asked for.
Ranges of different heights. The return range and the lookup range must cover the same rows. INDEX over B3:B10 with MATCH over A3:A9 is off by one at the bottom, and the last person's row returns the details of the person above.
IDs with stray spaces. An ID typed as “E-106 ” with a trailing space is a different text value from “E-106”, and the exact match fails even though the two look identical on screen. Clean an imported ID column once, before the lookups go on top of it.
Sources
- Look up values with VLOOKUP, INDEX, or MATCH — Microsoft Support — the INDEX and MATCH pattern, and how VLOOKUP returns a value by column number.
- VLOOKUP function — Microsoft Support — exact versus approximate match, and what the match type changes.