Function Problems

Excel Formula to Find Missing Numbers for Each Group

Steven ParkerSteven Parker Nov 18, 2026 869 views

Question details

Identify missing numbers from a defined sequence for each unique group and display them in a report.

Find Missing Numbers for Each Group in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Group names are in column A, their associated sequence numbers are in column B, and each group must be checked against the same required sequence.
Observed behavior
The desired report lists each group with its missing numbers.
Before you start

Check that column A contains group names and column B contains numeric sequence values, with at least one populated group. Decide the required sequence and adjust the data ranges in the formulas to match your sheet.

Solution 1Recommended

Use a Dynamic-Array Formula to List Missing Numbers by Group

Use this formula in a current version of Excel 365 to generate one row per group with missing numbers.

1
Check the source ranges

Confirm that group names are in A2:A100 and their numbers are in B2:B100. Change both ranges in the formula if your data occupies different rows.

2
Enter the formula

Select an empty cell, such as D2, and enter =LET(grp,A2:A100,nums,B2:B100,required,SEQUENCE(50),groups,UNIQUE(FILTER(grp,grp<>"")),missing,MAP(groups,LAMBDA(g,TEXTJOIN(", ",TRUE,FILTER(required,ISNA(XMATCH(required,FILTER(nums,grp=g))),"")))),FILTER(HSTACK(groups,missing),missing<>"","No missing numbers"))

3
Adjust the sequence

In the formula bar, change SEQUENCE(50) to the number of required values. For a sequence that starts at another number, use SEQUENCE(count,1,start), such as SEQUENCE(10,1,5) for 5 through 14.

4
Review the report

Press Enter and leave the cells below and to the right of the formula empty so the results can spill. Each returned row shows a group and its comma-separated missing numbers.

Use a Dynamic-Array Formula to List Missing Numbers by Group
Groups with complete sequences: Groups with no missing numbers are omitted. If every group is complete, the formula returns “No missing numbers.”
Work with group sequences in WPS

Find Missing Group Numbers in WPS Spreadsheets

Use COUNTIFS in WPS Spreadsheets to check each required group-and-number combination, then filter the results into a missing-number report.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select the workbook containing group names in column A and numbers in column B.
  2. 2. List required values: In columns D and E, enter each group with every number it should contain, using one row per combination.
  3. 3. Add the check formula: In F2, enter =IF(COUNTIFS($A$2:$A$100,D2,$B$2:$B$100,E2)=0,"Missing","") and adjust the source ranges as needed. Copy the formula down.
  4. 4. Filter missing entries: Select the report, choose Data > Filter, and filter column F to display “Missing.”
Check missing sequence values for each group with COUNTIFS.Open and save Microsoft Excel XLSX files.Filter the report to show only missing combinations.
microsoft office alternative - wps office

Frequently Asked Questions

How do I check numbers from 1 to 50 for each group?

Use SEQUENCE(50) in the Excel dynamic-array formula. It generates the numbers 1 through 50 for comparison with each group.

Can I check a sequence that starts at 5 instead of 1?

Yes. Replace SEQUENCE(50) with SEQUENCE(count,1,start). For example, SEQUENCE(10,1,5) checks 5 through 14.

Why does the dynamic-array formula show a #SPILL! error?

The output area may be blocked. Clear cells below and to the right of the formula, then check that the report can expand into the empty space.

What can I use if my Excel version does not support MAP or HSTACK?

List each required group-and-number combination in two columns, use COUNTIFS to mark combinations absent from the source data, and filter the results to show missing entries.