Compare Two Columns in Excel with XLOOKUP

Formulas & functions · Updated November 19, 2026

To check whether each value in column A appears anywhere in column B, enter =IF(ISNA(XLOOKUP(A2,$B$$2:$B$100,$B$2:$B$100,,0)),"Not found","Match") in C2 and fill it down. This exact-match check works in Microsoft 365 for Windows; it labels every value in A as a match or not found, and you can reverse the lookup to check column B against column A.

What comparing two columns means

Most column comparisons are membership checks: for every entry in one list, ask whether the same value occurs anywhere in another list. This is different from comparing A2 directly with B2. A row-by-row comparison only makes sense when the two lists are already aligned and corresponding rows are meant to be compared. If the order differs, a lookup is the appropriate approach.

For example, a product export might contain IDs in column A, while a second export contains IDs in column B in a different order. XLOOKUP can search the second list for each ID in the first. The result tells you which IDs are shared and which occur only in the first list. You can then perform a second lookup in the opposite direction to find IDs that occur only in the second list.

Arrange the lists and enter the formula

Put the first list in A2:A100 and the list to check against in B2:B100, with headings in row 1. In C1, add a heading such as Status. The formula in C2 searches for A2 in the full comparison range, rather than looking only at B2. That distinction lets the lists be in any order.

With the first list in A2:A100 and the comparison list in B2:B100, enter this in C2:

=IF(ISNA(XLOOKUP(A2,$B$2:$B$100,$B$2:$B$100,,0)),"Not found","Match")

The XLOOKUP arguments specify the value to search for, the search range, the range from which to return a result, an omitted not-found result, and exact match mode. Since the return range is the same as the search range, a successful lookup returns the matching value. ISNA detects the #N/A result when no match exists, and IF converts the outcome into readable labels. The dollar signs keep the B range fixed when you copy the formula down, while A2 adjusts to A3, A4, and so on.

For a worked example, suppose A2:A5 contains P-104, P-108, P-112, and P-120, while B2:B5 contains P-112, P-104, P-120, and P-130. The formula in C2, filled through C5, returns Match, Not found, Match, and Match. P-108 is absent from the comparison list; P-130 is not reported because this pass checks only values in column A.

Read the results and check both directions

A one-way check answers a one-way question. It identifies values in the lookup column that are absent from the search column, but it does not automatically show values exclusive to the other list. To find those, create a second status column and exchange the lookup and search ranges.

With the second list in B2:B100 and the comparison list in A2:A100, enter this in D2 and fill down:

=IF(ISNA(XLOOKUP(B2,$A$2:$A$100,$A$2:$A$100,,0)),"Not found","Match")

For the sample lists above, this reverse check flags P-130 as not found in column A. Together, the two checks show shared values and the values unique to each list. They do not pair duplicates one by one: if an ID appears several times in one list and once in the other, each copy in the first list will still be labelled Match. If frequency matters, compare counts as well as presence.

Return useful information instead of a status label

XLOOKUP can return a related field when a match exists, which is useful when the goal is to compare an ID list with a reference table. For instance, the formula can return a product name from an adjacent column. A missing ID can receive a clear message rather than a worksheet error.

With IDs in A2:A100, reference IDs in B2:B100, and product names in C2:C100, enter this in D2:

=XLOOKUP(A2,$B$2:$B$100,$C$2:$C$100,"No product record",0)

If A2 contains an ID found in B, the formula returns the corresponding product name from C; otherwise, it returns No product record. Ensure the lookup and return ranges have the same number of rows, and that each row in the return range belongs to the ID beside it. The lookup reports the first matching row if an ID is duplicated.

Handle blanks, spaces, duplicates, and text differences

Clean-looking values do not always compare as equal. A leading or trailing space can make a text ID differ from the same ID without that space. Numbers stored as text can also differ from numeric values, even when both display the same digits. Check the source data and cell formats if values you expect to match are marked not found.

  • Remove ordinary extra spaces: With source values in A2 and B2, use =TRIM(A2) in a helper column for the first list and =TRIM(B2) in a helper column for the second. Compare the cleaned helper columns. TRIM removes leading and trailing spaces and reduces repeated ordinary spaces between words.
  • Check for duplicates: XLOOKUP finds a matching value, not the number of occurrences. If duplicate counts matter, investigate each list’s frequency separately; a simple match label cannot establish that both lists contain the same number of copies.
  • Check blanks deliberately: Blank cells in the lookup list can make the result confusing, especially if the comparison range also contains blanks. Decide whether blank entries should be ignored, then remove or filter them before interpreting the results.
  • Consider letter case: Standard XLOOKUP text matching is not case-sensitive, so ABC and abc are treated as a match. If case is significant for your data, a different case-sensitive method is needed.

TRIM does not remove every unusual whitespace character that can arrive in copied or imported data. If the source may contain nonbreaking spaces or other invisible characters, inspect or clean the data before treating the lookup result as definitive. Avoid changing IDs by converting them blindly: leading zeroes may be meaningful in codes such as 0042.

Highlight missing values visually

You can color unmatched entries instead of adding status text. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the selected range starting at A2 and the comparison list in B2:B100, use:

=ISNA(XLOOKUP(A2,$B$2:$B$100,$B$2:$B$100,,0))

Choose a format and confirm the rule. The relative reference A2 changes for each selected row; the dollar signs hold the lookup range steady. This highlights values from A that are not found in B. To highlight values from B that are missing from A, create a corresponding rule for B2:B100 and use A2:A100 as the lookup range. Conditional formatting changes the appearance of cells, not their contents, so retain a status column if you need to sort or filter the results.

Common errors and practical checks

If Excel displays #NAME? for XLOOKUP, the installed version may not support the function, or the function name may have been mistyped. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Older Excel versions may require a different lookup method. If a formula displays as text instead of calculating, check whether the cell is formatted as Text, then change it to General and re-enter the formula.

  • Unexpected matches or misses: Confirm that the correct columns and row limits are used, and check for spaces, inconsistent data types, and leading zeroes.
  • #N/A remains visible: The sample formula handles a normal not-found result, but an error already present in a source cell can affect the lookup. Inspect the source data rather than suppressing every error automatically.
  • Only some rows have results: Make sure the formula was filled down through the full list. In Windows, double-clicking the fill handle can copy a formula down beside a continuous neighboring range; on Mac, the same fill handle is available, or copy and paste the formula.
  • Results seem slow: Use realistic bounded ranges such as B2:B10000 rather than whole-column references in many thousands of formulas, particularly in large workbooks.

In Excel for the web, XLOOKUP is supported in Microsoft 365, though ribbon placement and the editing interface can differ from Windows desktop Excel. Mac Excel also supports XLOOKUP in Microsoft 365; the formula logic is the same. If argument separators in your regional Excel settings differ, follow the separators Excel inserts when you build a formula.

Alternatives when XLOOKUP is not the right fit

For Excel 2021 or Microsoft 365, XMATCH can test for a position in a list, and COUNTIF can count occurrences. XLOOKUP is often convenient here because it can both test membership and return a related field. Older Excel releases without XLOOKUP can use a compatible lookup approach, but the exact formula depends on the workbook and whether the user needs a status, a returned value, or duplicate counts.

In Microsoft 365 or Excel 2021, FILTER can produce a separate spill range containing only unmatched items. With values to check in A2:A100 and the comparison list in B2:B100, enter this in an empty cell:

=FILTER(A2:A100,ISNA(XLOOKUP(A2:A100,B2:B100,B2:B100,,0)),"No differences")

The result is the values from A that have no exact match in B; for the example lists, it returns P-108. Leave room below the formula for the results to spill into neighboring cells. This is useful for a clean exception list, while the filled-down IF formula is more useful when each original row needs a visible status.

Frequently Asked Questions

Does XLOOKUP compare two columns row by row?

No. The formulas above search the entire comparison range for each value, regardless of its position. For aligned rows where A2 should equal B2, a direct equality check is a different task.

How do I find values in one column that are missing from another?

Look up each value in the first column against the full second-column range and label the not-found results. Reverse the ranges and repeat if you also need values missing in the other direction.

Does XLOOKUP find every duplicate?

No. XLOOKUP returns the first matching result. For a presence check, that is usually enough, but it does not count duplicates or prove that repeated values occur equally often in both columns.

Is XLOOKUP case-sensitive?

No. Its normal text match does not distinguish uppercase from lowercase. If case differences matter, use a specifically case-sensitive comparison method instead of relying on the standard XLOOKUP match.

Why does XLOOKUP say not found when the values look identical?

Common causes include hidden spaces, numbers stored as text, leading zeroes, or a range that omits the matching row. Inspect the underlying values and data formats, and clean the source consistently before comparing again.

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.