An exact lookup confirms that a key matched; it does not establish that only one source row had that key. This matters when a sheet uses a product code, asset tag or job number to select one associated value.

Use XLOOKUP in Microsoft 365 Excel, Excel 2024 or Excel 2021. Microsoft specifically notes that it is unavailable in Excel 2016 and Excel 2019, even though those versions may receive a workbook created elsewhere that contains it.

Make the intended selection visible

XLOOKUP normally searches from the first item and returns the first match. Exact matching is the default. The following example also states that choice explicitly with match mode 0, and gives missing keys an obvious message:

=XLOOKUP(E2,A2:A4,B2:B4,"Not found",0)

In a disposable sheet, enter keys K17, K17 and K24 in A2:A4. Put Shelf A, Shelf B and Shelf C in B2:B4. Enter K17 in E2. The expected result is Shelf A because the first matching row wins; the second K17 has not been reconciled.

Changing the search direction can choose a different matching row, but that is a selection rule. It does not resolve conflicting source records. Decide whether a key should be unique, or whether the real task needs more information than this single-key lookup supplies.

Test all three outcomes

Change E2 to K24 and confirm Shelf C. Then try a key absent from the list and confirm Not found. Finally, return to K17 and inspect both source rows, rather than accepting the first result as proof of uniqueness.

For the real report, ask which record is authoritative when repeated keys occur. Preserve legitimate repeats until their meaning is understood. If the list represents several locations for one asset type, a single-location result may be the wrong report design altogether.

Keep the lookup range and return range aligned to the same records, and recheck their final rows when the list grows. The accepted result should be supported by the source’s key rule and the tested formula, not merely by the absence of an Excel error.

Sources: Microsoft XLOOKUP function.