Look Up a Project Owner by ID in Excel Without Approximate Matches

What formula returns a project owner by exact ID in Excel?

Excel’s =XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"Not found",0) returns the owner for an exact project ID match or displays “Not found”.

=XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"Not found",0)

The formula searches column A for the requested ID in E2 and returns the owner from the same row in column B. A missing ID must not borrow a neighboring project’s owner.

Source project IDs must be unique. XLOOKUP returns the first matching row by default without warning about duplicates.

Use Excel for Microsoft 365, Excel 2021, or Excel 2024. XLOOKUP is unavailable in Excel 2016 and Excel 2019.

Where should the project directory and requested IDs go?

Place the project directory in A1:B5 and the requested IDs and results in E1:F5, keeping each source owner beside its project 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, providing a missing-record example.

Create this request area in E1:F5:

Requested project ID	Project owner result
PRJ-1004
PRJ-1003
PRJ-1001
PRJ-1007

E1 and F1 are headers; E2 contains the first request. Put the first formula in F2.

The lookup reserves source rows through row 100. Leave unused source rows empty rather than adding placeholder IDs. Keep requests and formulas outside A2:B100 to avoid overwriting source data or creating circular references.

How do you set up and copy the exact-match owner formula?

Set up the Excel owner lookup by checking source IDs, entering XLOOKUP in F2, and copying the formula down while keeping the source ranges fixed.

1. Enter the source directory in columns A and B. Put headers in row 1 and projects from row 2 onward. For numeric-looking IDs with meaningful leading zeros, format cells as Text before entry or import; otherwise, Excel may remove the zeros.

2. Check source IDs for duplicates. Select populated ID cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Assign distinct IDs to different projects. For duplicate records of one project, resolve ownership and retain one authoritative row.

3. Enter requested IDs in column E. Start with PRJ-1004 in E2. Follow the source ID convention: PRJ-1004 and PRJ1004 are different values.

4. Enter =XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"Not found",0) in F2. Press Enter. The sample result should be Casey Fiction. “Not found” is the chosen missing-record message, not an Excel error.

5. Copy F2 through F5. E2 changes to E3 in the next row because it is relative. Dollar signs keep both source ranges fixed.

6. Check the missing-ID result separately. PRJ-1003 should display Not found, never the owner assigned to PRJ-1002 or PRJ-1004.

What results should the sample project requests return?

The sample exact-match lookup should return Casey Fiction, Not found, Avery Placeholder, and Devon Sample for the four requested IDs.

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 requested IDs, not their source positions. PRJ-1004 is the third source project but the first request.

The source directory need not be sorted. If sorting for readability, sort the entire directory together. Sorting column A alone separates IDs from owners and makes even an exact lookup return incorrect assignments.

Why does XLOOKUP with match mode 0 return the owner for an exact project ID?

XLOOKUP with match mode 0 finds the requested project ID and returns the owner at the corresponding position in the return range. The first argument, E2, supplies the request rather than hard-coding an ID, so copying the formula down lets each row look up its own project. Next, $A$2:$A$100 identifies the source IDs, excluding the header while reserving room for more records. The dollar signs keep this directory range fixed instead of shifting it as the formula is copied. $B$2:$B$100 supplies the owners in the same row order, allowing a match in A4 to return B4, Casey Fiction, for the sample request PRJ-1004. The fourth argument, "Not found", provides a readable missing-record result directly within XLOOKUP, rather than requiring a separate error-handling wrapper. Finally, 0 explicitly selects exact matching. Although exact matching is already the default, writing it out makes the formula’s intent visible instead of relying on an omitted argument.

Which XLOOKUP arguments prevent approximate project matches?

XLOOKUP’s fifth argument, 0, requires an exact project ID match, while its fourth argument supplies the missing-ID message.

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

XLOOKUP defaults to exact matching, but explicit 0 documents the requirement. Do not substitute -1 or 1; those modes can return the next smaller or larger value when an exact match is absent.

Mode 0 treats an asterisk literally rather than enabling wildcard matching. However, standard XLOOKUP is not case-sensitive: PRJ-1004 and prj-1004 compare as equivalent. Capitalization alone cannot distinguish source IDs.

How do you fix missing or incorrect project owner results?

Fix incorrect XLOOKUP owner results by checking ID characters, storage types, duplicates, source boundaries, and empty owner cells.

An existing project returns “Not found”

Likely cause: Source and request IDs contain different characters, such as an accidental trailing space.

Exact fix: Edit the affected cell, remove the ordinary trailing space, and press Enter. For repeated import problems, use a temporary helper column:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

This replaces nonbreaking spaces and removes ordinary extra spaces. Fill down, review the cleaned IDs, and paste them back as values. Clean request IDs too if needed. Use this only when spaces are not meaningful identifier characters.

For numeric-only IDs, normalize both sides to the same storage type, preferably text. Formatting alone may not convert existing values; re-enter or reimport after setting the format.

One project ID has conflicting owners

Cause: Duplicate source IDs make XLOOKUP return only the first match.

Exact fix: Filter column A to the affected ID, determine the authoritative owner, and consolidate into one source row. Reversing search direction merely selects another occurrence; it does not resolve ownership.

A newly added project returns “Not found”

Cause: The new project lies below row 100.

Exact fix: Extend both source ranges equally. For a directory reaching row 500, use:

=XLOOKUP(E2,$A$2:$A$500,$B$2:$B$500,"Not found",0)

Update every result formula. Extending only one range creates mismatched array sizes and can produce #VALUE!.

The result is 0 instead of an owner

Cause: The ID matches, but its owner cell is empty. “Not found” handles missing IDs, not blank owner cells.

Exact fix: Populate the owner cell or enter Unassigned for unresolved ownership.

Avoid looking up empty requests against unused source rows. 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))

The wrapper changes blank-request behavior without allowing approximate matches.

How can an Excel Table include newly added projects automatically?

An Excel Table lets XLOOKUP source references expand when projects are added as table rows.

Select the populated directory, choose Insert > Table, and confirm headers. Under Table Design, name the table Projects. Keep the headers Project ID and Project owner.

=XLOOKUP(E2,Projects[Project ID],Projects[Project owner],"Not found",0)

Add records within the table so structured references include them. A table removes the fixed row-100 boundary but does not prevent duplicate IDs.

When should you avoid this single-owner XLOOKUP?

Avoid this single-owner XLOOKUP when projects have multiple owners, ownership depends on dates, IDs are case-sensitive, or Excel lacks XLOOKUP.

• Multiple owners: Use FILTER to return all matching owner rows, leaving enough empty cells for results to spill.
• Historical ownership: Maintain effective dates and use a lookup evaluating both project ID and requested date.
• Case-sensitive IDs: Use a comparison based on EXACT, or change the identifier convention so capitalization is not the only distinction.
• Excel 2016 or Excel 2019: Use INDEX with MATCH set to 0 for exact matching. Wrap the lookup in IFNA to display "Not found" for an absent ID.