When several rows share a lookup key, XLOOKUP’s search direction determines which occurrence is returned. Decide what the duplicates mean before changing that direction.
Inspect every matching source row
Find the duplicate key in the source and compare dates, versions, and statuses. A repeated ID may represent valid history, multiple line items, or an accidental duplicate. These situations need different selection policies.
Do not delete duplicate-looking rows just to make the lookup deterministic. Preserve the full records and identify the rule that makes one record appropriate for the report.
Choose first or last deliberately
For ordinary Sheets XLOOKUP, =XLOOKUP(E2,A2:A100,B2:B100,"Not found",0,1) searches from first to last. Changing the final argument to -1 searches from last to first, while 0 keeps exact matching.
Last in the range does not necessarily mean newest by date. That is only true if the source’s ordering policy actually guarantees it. Binary search modes have sorted-range requirements and are not a general duplicate-resolution shortcut.
Test the intended selection policy
Create a short example with two matching keys and different return values. Confirm the formula selects the expected occurrence after a controlled source sort or append. Document whether order is part of the business rule.
If all matching rows are required, use a suitable multirow method such as FILTER instead of forcing one lookup result to represent several records. If a key should be unique, add a duplicate audit and correct the source process. A search-direction argument makes selection predictable, but it cannot determine which conflicting record is factually correct.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.