Excel Formula to Find Missing Numbers for Each Group
Question details
Identify missing numbers from a defined sequence for each unique group and display them in a report.

- 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.
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.
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.
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.
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"))
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.
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.

Create a Missing-Number Report with COUNTIFS
Use this approach when your Excel version does not support the dynamic-array functions in the first solution.
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. 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. List required values: In columns D and E, enter each group with every number it should contain, using one row per combination.
- 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. Filter missing entries: Select the report, choose Data > Filter, and filter column F to display “Missing.”

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.




