Function Problems

Compare Two Excel Row Sets and List Missing or Different Names

Phi Hung VoPhi Hung Vo Nov 18, 2026 869 views

Question details

Compare two sets of names and values and list names from the first set that are missing or have a different value in the second set.

Compare Two Excel Row Sets and List Missing or Different Names
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains two name-and-value sets that need to be compared in another column.
Observed behavior
The desired output includes names from the first set whose matching name is absent or whose value differs, while equal matches are omitted.
Before you start

Identify the name and value columns in each set, and leave an empty column for the results. The XLOOKUP method assumes each name appears only once in the second set and requires a version of Excel that supports XLOOKUP and FILTER.

Solution 1Recommended

Use XLOOKUP and FILTER to list differences in Excel

Use this dynamic formula in Microsoft 365 to return names from the first set that are missing or have a different value in the second set.

This example uses names in D2:D6 and values in E2:E6 for the first set, and names in G2:G100 and values in H2:H100 for the second set. Adjust the ranges to fit your data.

1
Select an empty cell

Click a cell such as J2 and make sure the cells below it are empty so the results can spill into the column.

2
Enter the formula

In J2, enter =FILTER(D2:D6,IFNA(XLOOKUP(D2:D6,G2:G100,H2:H100)<>E2:E6,TRUE),"") and press Enter.

3
Review the results

Check the names listed below J2 against columns G and H. A name appears if XLOOKUP cannot find it or its returned value differs from the value in column E.

Use XLOOKUP and FILTER to list differences in Excel
Check duplicate names and blank values: XLOOKUP compares only the first occurrence of a name in the second set. Blank lookup values may be returned as zero, so review blanks separately if they must be distinguished from zero.
Compare rows in WPS Spreadsheets

List unmatched name-and-value pairs in WPS Spreadsheets

Use a COUNTIFS helper formula in WPS Spreadsheets to identify names whose exact name-and-value pair is not present in the second set.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and locate the two sets of names and values.
  2. 2. Add a helper formula: If the first set is in columns D and E and the second is in columns G and H, click J2 and enter =IF(COUNTIFS($G$2:$G$100,D2,$H$2:$H$100,E2)=0,D2,""). Adjust the ranges to include your data.
  3. 3. Fill the formula down: Drag the fill handle from J2 down alongside the first set. Column J displays a name when its name-and-value pair is not found in the second set.
  4. 4. Review the names: Select the helper column and use Data > Filter to show nonblank results, or inspect the names displayed in column J.
Identify missing or different name-and-value pairs without XLOOKUPOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface and formulasFilter the helper column to focus on unmatched names
QA img-9

Frequently Asked Questions

What happens if a name is missing from the second set?

In the Excel formula, XLOOKUP returns an error for the missing name, and IFNA marks it for inclusion in the FILTER results.

Can I compare the sets if a name appears more than once?

XLOOKUP checks only the first matching name in the second set. To check whether any matching name-and-value pair exists, use the COUNTIFS helper formula described for WPS Spreadsheets.

Why do I see a spill error in Excel?

FILTER needs empty cells below the formula. Clear any contents blocking the output range below J2, then check the formula again.

What if my data extends beyond row 100?

Edit the second-set ranges in the formula, such as G2:G100 and H2:H100, so they include all rows you want to compare.

Does the COUNTIFS method work with wildcard characters in names?

COUNTIFS treats characters such as * and ? as wildcards in criteria. If names contain these characters, escape them or use another comparison method to avoid unintended matches.