Look Up a Project Owner by ID in Excel Without Approximate Matches
How do I look up a project owner by ID in Excel using an exact match and show "Not found" for missing IDs?
Enter =XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"Not found",0) to find the ID in E2, return its owner from column B, and display Not found if there is no exact match.
Enter this exact-match formula in F2 to return the owner for a project ID. A missing ID returns “Not found”: =XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"Not found",0). Copy the formula down to check the other requests.
How do you use XLOOKUP for an exact match in Excel?
This formula searches for the ID in E2 within the source ID range and returns the owner from the corresponding position in the owner range. Its fourth argument sets the message to show when no ID matches. The fifth argument, 0, specifies an exact match, as Microsoft’s XLOOKUP function documentation explains.
=XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"Not found",0)
Keep one authoritative source row for each project ID. If an ID appears more than once, XLOOKUP returns the first matching row by default. That can hide a disagreement about the owner. The directory does not need to be sorted for an exact match. If you sort it to make it easier to read, sort the ID and owner columns together so every owner remains beside the right project.
Use this formula in Excel for Microsoft 365, Excel 2021, or Excel 2024. The Microsoft function reference says XLOOKUP is not included in Excel 2016 or Excel 2019. In an older version, use INDEX with MATCH set to 0 for an exact match, then wrap the lookup in IFNA to show a message when an ID is missing.
Where should the project directory and requested IDs go?
Place project IDs in column A, owners in column B, requested IDs in column E, and lookup results in column F. Keep each owner’s name next to that project’s ID.
The names below are fictional placeholders.
| Project ID | Project owner |
|---|---|
| PRJ-1001 | Avery Placeholder |
| PRJ-1002 | Blair Example |
| PRJ-1004 | Casey Fiction |
| PRJ-1007 | Devon Sample |
PRJ-1003 is intentionally absent. The formula’s result for that request shows what happens when a project record is missing.
Set up the request area in E1:F5:
| Requested project ID | Project owner result |
|---|---|
| PRJ-1004 | |
| PRJ-1003 | |
| PRJ-1001 | |
| PRJ-1007 |
Use E1 and F1 for the headers. Put the first requested ID in E2 and the first formula in F2. The formula checks source rows through row 100. Leave unused rows empty instead of filling them with placeholder IDs. Keep the requests and formulas outside A2:B100. This protects the source data and prevents a circular reference.
Requests do not need to follow the directory’s ID order. The formula checks the ID on each request row, so the results follow E2, E3, and the rows below, rather than the order of IDs in column A.
How do you set up and copy the exact-match owner formula?
Check the source IDs, put the formula in F2, then copy it down for the other requests. The request reference changes as the formula moves to each row; the dollar signs hold the source ranges in place.
-
Enter the source directory in columns A and B. Put headers in row 1 and start the project records in row 2. If numeric-looking IDs have meaningful leading zeros, format the cells as Text before entering or importing the IDs. Excel will preserve the zeros.
-
Check source IDs for duplicates. Select the populated ID cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Resolve any conflicting ownership and keep one authoritative row for each project ID.
-
Enter requested IDs in column E. Start with PRJ-1004 in E2. Use the same ID format in the source and request cells. For instance, PRJ-1004 and PRJ1004 are different values.
-
Enter
=XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"Not found",0)in F2. Press Enter. With the sample data, the formula should return Casey Fiction. “Not found” is the message for a missing ID, not an Excel error. -
Copy F2 through F5. As you copy the formula down, the request reference changes from E2 to E3. The dollar signs keep the source ranges fixed.
-
Check the missing-ID result separately. PRJ-1003 should return Not found, not the owner of a nearby project.
An owner lookup is useful for recurring assignment work when someone maintains the owner field. I would fix unassigned records rather than let a lookup make the directory appear complete. This guide to flagging CRM contacts missing an owner or next step covers a related spreadsheet check.
Why is VLOOKUP not picking up the correct value?
A lookup can return an unexpected result if the requested ID differs from the source ID, the source ranges do not line up, or duplicate IDs have conflicting owners. XLOOKUP uses separate lookup and return ranges, as Microsoft’s function reference explains.
The sample requests should return these results:
| Cell | Requested project ID | Expected result |
|---|---|---|
| F2 | PRJ-1004 | Casey Fiction |
| F3 | PRJ-1003 | Not found |
| F4 | PRJ-1001 | Avery Placeholder |
| F5 | PRJ-1007 | Devon Sample |
Results follow the order of the requests, not the directory. PRJ-1004 is the third project in the source but the first request. Use that result to check the ranges: PRJ-1004 should return the owner from its own source row.
| Argument | Value | Purpose |
|---|---|---|
| Lookup value | E2 |
The requested project ID |
| Lookup array | $A$2:$A$100 |
The source project IDs |
| Return array | $B$2:$B$100 |
The corresponding owners |
| If not found | "Not found" |
The result when no match exists |
| Match mode | 0 |
Requires an exact match |
The 0 setting requires an exact match. Microsoft describes it in the XLOOKUP reference. Exact match is also the default, but including 0 makes the setting clear. Do not use -1 or 1 when you need an exact project ID. Those modes can return the next smaller or larger value if there is no exact match.
If a result still looks wrong, compare the source and request IDs one character at a time. Then make sure both source ranges start and end on the same rows. If the lookup range contains one set of IDs and the return range covers different rows, Excel can pair a project with the wrong owner. For related assignment work, see how to count open tasks by owner with Google Sheets QUERY.
How do I fix the data type mismatch error in XLOOKUP?
Make sure requested and source IDs use the same characters and storage type, and check that the lookup and return ranges cover the same rows. A text ID and a numeric ID may look identical in the sheet but still fail to match.
An existing project returns “Not found.” Check for extra characters, such as an accidental trailing space. If imports cause this repeatedly, test a helper column:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
This formula replaces nonbreaking spaces and removes ordinary extra spaces. Fill it down and review the cleaned IDs, then paste them back as values. Clean the request IDs as well if needed. Only use this cleanup if spaces are not meaningful parts of the identifier.
For IDs made up of numbers, use the same storage type on both sides. Text is the better choice when leading zeros matter. Changing a cell’s format by itself may not convert values already entered. Re-enter or reimport those values after setting the format.
One project ID has conflicting owners. Filter column A for that ID, choose the authoritative owner, and combine the records into one source row. Changing the search direction would select a different occurrence, but it would not tell you which owner is correct.
A newly added project returns “Not found.” Check whether its row is below row 100. Extend both source ranges to the same final row. If the directory reaches row 500, use:
=XLOOKUP(E2,$A$2:$A$500,$B$2:$B$500,"Not found",0)
Update every result formula. If you extend only one range, the lookup and return ranges will have different sizes, which can produce #VALUE!.
The result is 0 instead of an owner. Check whether the matching ID has an empty owner cell. The missing-ID message appears when the ID is absent; it does not replace an empty owner cell. Enter the owner’s name or a clear value such as Unassigned.
To leave the result blank when E2 is empty, use:
=IF(E2="","",XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"Not found",0))
This wrapper changes the result when the request is blank but keeps the exact-match lookup. It cannot correct a mismatch in the source ID or fill an empty owner cell.
When should you avoid this single-owner XLOOKUP?
Choose another method if a project has multiple owners, ownership depends on a date, or your Excel version does not have XLOOKUP. This formula returns one owner from one matching project row.
An Excel Table removes the fixed row-100 limit as the directory grows. Select the populated directory, choose Insert > Table, and confirm that it has headers. Under Table Design, name the table Projects and set the headers to Project ID and Project owner.
=XLOOKUP(E2,Projects[Project ID],Projects[Project owner],"Not found",0)
Add new records within the table so the structured references include them. A table does not resolve duplicate IDs, so keep one authoritative row for each project.
For projects with multiple owners, use FILTER to return every matching row, with enough empty cells available for the results. For historical ownership, keep effective dates and use a lookup that checks both the project ID and the requested date. If capitalization must distinguish IDs, use a case-sensitive comparison based on EXACT instead of this single lookup.
I would get the source data in order before making the formula more elaborate. An outdated or duplicated directory can return the wrong owner, a harder problem to spot than a clear Not found message. The spreadsheets and data guides cover related ways to check and maintain operational data.