Compare Two Excel Row Sets and List Missing or Different Names
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.

- 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.
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.
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.
Click a cell such as J2 and make sure the cells below it are empty so the results can spill into the column.
In J2, enter =FILTER(D2:D6,IFNA(XLOOKUP(D2:D6,G2:G100,H2:H100)<>E2:E6,TRUE),"") and press Enter.
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.

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. Open the workbook: Open your Excel workbook in WPS Spreadsheets and locate the two sets of names and values.
- 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. 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. Review the names: Select the helper column and use Data > Filter to show nonblank results, or inspect the names displayed in column J.

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.




