How can I return a room number for a person when names are not unique?

Because a name that appears more than once does not identify one person. In Excel, XLOOKUP uses an exact match by default but returns the first match it finds. If two records have the same name, a name-only lookup can return the first person’s room, even when you meant the second person. [S1]

Instead, match on a field that uniquely identifies the person in your records, such as an assigned person ID, and return the room from that same record. Check that your proposed key does not occur more than once. Excel’s duplicate-value highlighting can help flag repeated values for review. [S2]

A name can still help someone recognize a record, but it should not be treated as unique just because it looks distinctive.

Yes. You can consider a composite key: a combination of fields intended to distinguish each record. For example, if your sheet contains Name, Department, and Building, you might create a key from all three and use it to find the room. That combination is only an example, not a guarantee of uniqueness.

Review the proposed keys for duplicates before relying on them. If two people still have the same combined values, the key remains ambiguous; add another suitable field or have the person selecting a record choose between the matching entries. Do not assume that adding a department or building automatically makes a name unique. Duplicate highlighting can help identify repeated values, but it does not decide which fields identify a person. [S2]

Assume your data has Person ID in column A, Name in B, and Room in C. The person ID must identify one record in this sheet. Put the ID to find in E2, then use:

=XLOOKUP(E2,A:A,C:C,"No match")

This searches column A for the value in E2 and returns the corresponding value from column C. XLOOKUP returns a corresponding value from a return range and uses exact match by default. [S1] Check that the ID is unique first; if it appears in more than one row, the lookup still cannot determine which record you intended.

For a composite key, create the same key consistently for each data row and for the lookup value, then search the key column and return the room column. Verify that the key is not duplicated. Microsoft lists XLOOKUP as unavailable in Excel 2016 and 2019, so check your version if the formula is not available. [S1]

Treat those as different problems. A duplicated key means it still does not identify one record; do not accept whichever room appears first. Review the matching rows and choose an additional distinguishing field, or have the person selecting the record clarify which entry they mean. A missing match means the lookup did not find a corresponding key; check the entered value and the source records.

For example, if two rows share the same name and department, a key made only from those fields still cannot distinguish them. You could add another appropriate field if it separates the records, then check the resulting keys again. The goal is to select the intended record, not simply to make the formula return a value. Duplicate highlighting helps with review but does not prove that a set of personal fields uniquely identifies someone. [S2]