Excel XLOOKUP Returns Wrong Match With Duplicate Keys: Fix

You use XLOOKUP in Excel expecting the correct match, but it returns the wrong value when your lookup column contains duplicate keys. This happens because XLOOKUP by default returns the first match it finds, and when duplicates exist, the first match may not be the one you intended. In this article, you will learn why XLOOKUP behaves this way with duplicates and how to force it to return the correct match using specific techniques.

Why XLOOKUP Returns the Wrong Match With Duplicate Keys

XLOOKUP searches a lookup array for a specified value and returns a corresponding value from a return array. When the lookup array contains duplicate keys, XLOOKUP stops at the first occurrence it encounters. By default, XLOOKUP uses a search mode of 1, which searches from the first item to the last. If your data has duplicate keys and the intended match is not the first occurrence, XLOOKUP returns the wrong match.

This behavior is by design and matches how VLOOKUP and INDEX/MATCH also handle duplicates. The problem is not a bug in XLOOKUP but a misunderstanding of how the function works with non-unique keys. To fix it, you must either change the search direction, make your keys unique, or use a more advanced formula that filters duplicates before matching.

How XLOOKUP Search Modes Affect Duplicate Matching

XLOOKUP has an optional fifth argument called search_mode. The default value is 1 (search first-to-last). Other options include -1 (search last-to-first), 2 (binary search ascending), and -2 (binary search descending). Binary search modes require sorted data and are not suitable for unsorted duplicates. The last-to-first search mode (-1) is useful when you need the most recent duplicate entry, for example, the latest transaction or the last update.

When Duplicates Are Unavoidable

In many real-world datasets, duplicate keys are legitimate. For instance, a sales report may have multiple rows for the same product ID on different dates. If you want to match the most recent sale, you need to sort the data by date descending and use search_mode 1, or keep the data as-is and use search_mode -1 after sorting by date ascending. The fix depends on which duplicate you need: the first, the last, or a specific one based on another condition.

Steps to Fix XLOOKUP When It Returns the Wrong Match

The following methods will help you force XLOOKUP to return the correct match when your lookup column contains duplicates. Choose the method that matches your data structure and the specific duplicate you need.

Method 1: Change the Search Mode to Return the Last Match

If your data is sorted so that the intended match is the last occurrence (for example, the most recent date), use search_mode -1.

1. Identify your data range
   Confirm that your lookup array contains duplicate keys and that the data is sorted with the intended match as the last occurrence. For example, sort by date ascending so the latest date is at the bottom.
2. Write the XLOOKUP formula with search_mode -1
   Use this syntax: =XLOOKUP(lookup_value, lookup_array, return_array, , , -1). The fifth argument (if_not_found) is omitted by leaving it blank with two commas. The sixth argument sets search_mode to -1.
3. Press Enter and verify the result
   Excel returns the value from the last matching row in the return array. Test with a known duplicate to confirm it picks the correct row.

Method 2: Create a Helper Column to Make Keys Unique

When you need a specific duplicate that is neither the first nor the last, create a unique key by appending an index number to each duplicate value.

1. Add a helper column next to your lookup array
   Insert a new column to the right of your lookup column. In the first data row, enter this formula: =A2&COUNTIF($A$2:A2, A2). Replace A2 with your actual lookup cell reference. This formula combines the original value with its occurrence count so far.
2. Copy the formula down the helper column
   Drag the fill handle to apply the formula to all rows. Each duplicate now has a unique identifier: for example, “ProductA1”, “ProductA2”, “ProductA3”.
3. Build the XLOOKUP formula using the helper column
   Use this syntax: =XLOOKUP(lookup_value & occurrence_number, helper_column, return_array). Replace occurrence_number with the specific duplicate you want. For example, to get the third occurrence: =XLOOKUP("ProductA" & 3, C2:C100, B2:B100).
4. Press Enter and verify
   XLOOKUP now searches on unique keys and returns the correct match for the specified occurrence.

Method 3: Use FILTER to Prefilter the Data

When the correct duplicate is determined by a condition in another column, use FILTER to narrow the lookup array before applying XLOOKUP.

1. Identify the condition that isolates the correct duplicate
   For example, you want the sale record for ProductA where the Status column equals “Completed”.
2. Write a FILTER formula inside XLOOKUP
   Use this syntax: =XLOOKUP(lookup_value, FILTER(lookup_array, condition_array=condition_value), FILTER(return_array, condition_array=condition_value)). For example: =XLOOKUP("ProductA", FILTER(A2:A100, C2:C100="Completed"), FILTER(B2:B100, C2:C100="Completed")).
3. Press Enter and verify
   XLOOKUP now only sees rows that meet the condition, eliminating unwanted duplicates.

If XLOOKUP Still Returns the Wrong Match After the Main Fix

XLOOKUP Returns #N/A Even Though the Value Exists

This often happens when the helper column approach creates keys that do not match the lookup value exactly. Check that the helper column formula uses the correct range references and that the occurrence number in the lookup value matches the helper column format. Also verify there are no extra spaces. Use the TRIM function on both the lookup value and the helper column to remove invisible characters.

XLOOKUP Returns the Wrong Match After Sorting

When you change the data sort order, XLOOKUP with search_mode 1 or -1 may behave differently because the first or last occurrence changes. After sorting, always verify that the search mode matches your intended position. If you need a specific occurrence regardless of sort order, use the helper column method instead.

XLOOKUP Works in One Workbook but Not Another

This typically happens when the second workbook contains different duplicate patterns or the data is not sorted as expected. Copy the exact formula from the working workbook and adjust the ranges. Ensure both workbooks use the same version of Excel that supports XLOOKUP (Excel 2021 or Microsoft 365).

XLOOKUP Search Modes for Duplicate Keys: Comparison

Search Mode	Behavior With Duplicates	Best Use Case
1 (default, first-to-last)	Returns the first matching row from top to bottom	When the first duplicate is the intended match
-1 (last-to-first)	Returns the last matching row from bottom to top	When the last duplicate is the intended match (e.g., most recent date)
2 (binary ascending)	Requires sorted data; returns any match (undefined with unsorted duplicates)	Large sorted datasets with unique keys only
-2 (binary descending)	Requires sorted data; returns any match (undefined with unsorted duplicates)	Large sorted datasets with unique keys only

You can now force XLOOKUP to return the correct match even when your lookup column contains duplicate keys. Start by identifying whether you need the first, last, or a specific duplicate, then apply the matching method. For datasets where duplicates are unavoidable, consider using the helper column with COUNTIF to create unique keys. If you frequently work with duplicate keys, explore the FILTER function to prefilter your data before matching, which gives you full control over which row XLOOKUP evaluates.