XLOOKUP in Excel: Compare Two Lists and Find Matches

Formulas & functions · Updated November 18, 2026

Yes. In Excel, XLOOKUP can compare a value in one list with another and return the matching item or a message when it is missing. For example, with one list in A2:A100 and another in B2:B100, enter =XLOOKUP(A2,$B$2:$B$100,$B$2:$B$100,"Not found") beside the first list, then fill the formula down.

What XLOOKUP Does When Comparing Lists

XLOOKUP searches a lookup range for a value and returns a corresponding value from a return range. To check whether items in list A also appear in list B, use the value from A as the lookup value and list B as the lookup range. If list B is also the return range, a successful search returns the matching item itself.

The formula above uses four arguments: the value to find, the range to search, the range from which to return a result, and the text to display if no match exists. The dollar signs keep the B range fixed as you copy the formula down, while A2 changes to A3, A4, and so on. XLOOKUP searches for an exact match by default, which is usually the right behavior when comparing names, codes, or IDs.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not available in Excel 2019 or Excel 2016. If someone opens a workbook containing XLOOKUP in an older Excel version, the formula may appear as an unsupported function rather than calculate correctly.

Set Up the Lists and Check for Matches

Put the two lists in separate columns with clear headings, such as “Current” in A1 and “Previous” in B1. Keep the actual values in rows 2 through 100 in this example. In C1, add a heading such as “In previous list,” then enter the lookup formula in C2 and copy it down to C100.

With current-list values in A2:A100 and previous-list values in B2:B100, this formula returns the matching value from column B or “Not found”:

=XLOOKUP(A2,$B$2:$B$100,$B$2:$B$100,"Not found")

For example, if A2 contains “P-104” and that exact code appears in B17, the formula returns “P-104.” If it does not appear anywhere in B2:B100, the formula returns “Not found.” This makes it easy to filter column C for missing items or visually scan for successful matches.

If you prefer a simple status rather than seeing the matched value, use an IF formula around XLOOKUP. With the same list layout and the formula entered in C2, it returns “Present” for a match and “Missing” when no match exists:

=IF(ISNA(XLOOKUP(A2,$B$2:$B$100,$B$2:$B$100)),"Missing","Present")

Copy it down the comparison column. This formula checks for the #N/A result that XLOOKUP produces when it cannot find the lookup value. For a large comparison, convert the data to Excel tables so that formulas can use readable column names and automatically include new rows; select the data and choose Insert > Table.

Find Items That Are Only in One List

A comparison is one-directional unless you repeat it in reverse. Checking every item in A against B tells you which A items are missing from B. It does not tell you which B items are missing from A. To find those, create a second check beside list B and search list A.

With values in A2:A100 and B2:B100, enter this formula in D2 to compare each B value against A, then fill it down alongside list B:

=XLOOKUP(B2,$A$2:$A$100,$A$2:$A$100,"Not in current list")

The two directions answer different practical questions. For example, comparing this month’s customer IDs with last month’s can show which customers are new; reversing the check shows which previous customers are no longer in the current list. Keeping both results makes it less likely that a one-way check will be mistaken for a complete reconciliation.

For each direction, filter the result column to show only the “not found” message. You can then review, copy, or investigate the unmatched records. If the list contains blank cells, consider excluding them from the comparison: a blank lookup value can match a blank cell, making an empty row look like a genuine match.

Return Details Instead of the Matching Value

XLOOKUP can also compare an identifier in one list and return related information from another column. This is useful when one sheet contains a list of product codes and another contains codes plus descriptions, prices, or owners. The lookup range and return range need to align row by row, but they do not have to be the same column.

Suppose product codes are in A2:A100, while a reference table has codes in E2:E100 and descriptions in F2:F100. With the current code in A2, enter this formula in C2 to return its description:

=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100,"Code not found")

If A2 contains a code that appears in E12, Excel returns the description from F12. If the code does not appear in the reference table, the result is “Code not found.” This is more informative than a match flag when the goal is to enrich a list with details from a reference sheet.

When the lookup column contains duplicate values, XLOOKUP returns the first match it encounters, searching from the top by default. It does not report every duplicate or tell you how many there are. If duplicates matter, check them separately rather than assuming a returned result proves the key is unique.

Exact Matches, Duplicates, and Similar-Looking Values

By default, XLOOKUP looks for an exact match and searches from the first item to the last. “Exact” does not mean case-sensitive: ordinary text searches generally treat “North” and “north” as equal. If capitalization is meaningful in your data, a basic XLOOKUP comparison will not distinguish it.

Many apparent lookup failures come from values that look alike but are stored differently. A code such as 0042 may be stored as text in one list and as the number 42 in another; dates may also be text in one place and real Excel dates in the other. Leading or trailing spaces can cause a text value not to match. Check the source values and their cell formats before changing the formula, because converting IDs to numbers can remove meaningful leading zeroes.

For repeatable comparisons, standardize both lists before looking them up. Make sure identifiers use a consistent format, remove accidental extra spaces, and decide whether leading zeroes are part of the identifier. Excel’s TRIM function removes ordinary extra spaces, but unusual spaces copied from web pages or other systems may require additional cleanup. Apply the same cleanup rule to both lists, not just one side.

Wildcard matching is available if the data genuinely calls for it, but it changes the question from “is this exact item present?” to “does a value fit this pattern?” In XLOOKUP, wildcard matching uses a match-mode argument of 2. Avoid it for IDs where an exact comparison is important: a partial or pattern match can make a near-match look like a valid match.

Common Problems and How to Diagnose Them

  • Every result says “Not found.” Confirm that the formula points to the correct lookup range and that the values are actually equivalent. Check for text-versus-number differences, hidden spaces, and inconsistent leading zeroes.
  • The formula changes its search range when copied. Use absolute references such as $B$2:$B$100 for the fixed lookup and return ranges. Keep the changing lookup cell, such as A2, relative.
  • A blank row appears to match. Remove blank rows from the comparison or add a blank check if empty cells are not valid list items. Otherwise, a blank in the lookup list can be treated as a value to find.
  • A duplicate seems to be ignored. XLOOKUP returns one matching result, not every occurrence. Use a count or a separate duplicate check if repeated records must be identified.
  • Excel shows #NAME? or does not recognize XLOOKUP. The workbook may be open in an Excel version that does not support the function. Use Microsoft 365 or Excel 2021 or later, or choose an older-compatible alternative.

For a quick count of how often an item from A appears in B, use COUNTIF. With values in A2:A100 and B2:B100, enter this in C2 and copy down:

=COUNTIF($B$2:$B$100,A2)

A result of 0 means no matching value was counted; 1 means one occurrence; and a result above 1 indicates repeated occurrences. This count complements XLOOKUP because a successful XLOOKUP alone cannot tell you whether the searched item appears once or several times.

When to Use XLOOKUP or Another Method

Choose XLOOKUP when you need a readable formula that checks for an item and can return a match, a related field, or a custom not-found message. It is particularly convenient for two-way list reconciliation because you can reverse the lookup and use the same pattern. You can also return a value from a column to the left of the lookup column, unlike VLOOKUP’s usual left-to-right arrangement.

COUNTIF is a straightforward choice when the main question is “how many times does this value appear?” It returns a count rather than a matched record. MATCH or XMATCH can locate a position in a range, but they do not by themselves return a related field. FILTER can return multiple matching rows in supported versions, which is useful when one key legitimately has several related records; XLOOKUP returns only one match.

VLOOKUP remains useful in older Excel versions that lack XLOOKUP, provided the lookup column is positioned to the left of the result column. For an older-version match check, with A2:A100 and B2:B100, enter this formula in C2 and fill down:

=IF(COUNTIF($B$2:$B$100,A2)>0,"Present","Missing")

That formula is valid in older Excel releases, but it only reports presence and does not return a corresponding field. When sharing workbooks, consider the recipient’s Excel version before replacing a compatible formula with XLOOKUP.

Frequently Asked Questions

Can XLOOKUP compare two columns and show what is missing?
Yes. Search each value in the first column against the second and supply a not-found message, then fill the formula down. Repeat the comparison in reverse if you also need items that appear only in the second column.

Does XLOOKUP match exactly by default?
Yes. Its default match mode is exact match, so it is appropriate for comparing IDs and full names. It is not case-sensitive by default, and differences in spaces or stored data types can still affect whether values match.

Can XLOOKUP find duplicates?
It can find a matching value, but it returns only one result, normally the first match. To identify or count repeated occurrences, use a separate duplicate check, such as COUNTIF, rather than relying on XLOOKUP alone.

Why does XLOOKUP return “Not found” for a value I can see?
Look for extra spaces, text-versus-number differences, leading zeroes, and cells that only appear identical because of formatting. Check that the formula’s lookup range includes the visible value and that both lists use a consistent representation.

Can I use XLOOKUP in Excel 2019?
No. XLOOKUP requires Microsoft 365 or Excel 2021 or later. In Excel 2019 or earlier, use an option such as COUNTIF for a presence check, or VLOOKUP when you need to retrieve a related value and its layout supports that function.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.