Repeated inputs create repeated outputs
Deduplicate normalized required keys before filtering.
List required IDs missing from another export
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. List IDs from A2:A9 that do not occur in D2:D11, once each, uppercased and in first required-occurrence order. Ignore genuinely blank or empty-text source cells. Compare printable ASCII text IDs literally with case ignored; spaces and leading zeroes remain significant. Return No missing IDs when the difference is empty, including an empty required list.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:G12. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Build an ordered set difference with exact matches, normalized letter case and one output per missing identifier.
THE QUICK ANSWER01
Build an ordered set difference with exact matches, normalized letter case and one output per missing identifier.
ƒx
=LET(keys,UNIQUE(FILTER(UPPER(A2:A9),A2:A9<>"","")),FILTER(keys,(keys<>"")*ISNA(XMATCH(keys,UPPER(D2:D11),0)),"No missing IDs"))| Missing required ID |
|---|
| BETA |
| GAMMA |
| EPS |
| OMEGA |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
A set difference with order
The original missing keys are BETA, GAMMA, EPS and OMEGA. Repeated amber and beta inputs do not create repeated outputs. The result follows the first required occurrence, rather than sorting.
Keep identifiers as text
The IDs 001 and 1 are distinct. Converting them with VALUE or a double unary merges records that this rule keeps separate. A leading space in an ID is also retained.
Blank rows and empty sources
Either list may contain blank cells or empty text. An empty required list has no missing IDs. An empty observed list makes every distinct nonblank required key missing.
Existence is a different question from amount
A present ID may still have a wrong amount or repeated rows. This task checks membership only. The grouped reconciliation task checks totals, while the invoice task combines repeated payments.
Read the full step-by-step walkthrough
- Declare one case policy
UPPER makes differently cased ASCII IDs share one comparison key. It does not remove spaces or leading zeroes.
- Remove empty source slots
Filter out only truly empty cells and empty text. A leading or trailing space in a nonempty ID remains part of its key.
- Keep one key per required ID
UNIQUE preserves the first required occurrence. A repeated missing ID is returned once.
- Use exact lookup mode
XMATCH mode 0 looks for each key in the observed list. A* can match only A*, not AX.
- Keep only missing keys
ISNA identifies unsuccessful matches. The nonempty-key condition removes the placeholder used for an empty required list.
- Name the empty result
The final FILTER fallback is No missing IDs. The rest of the reserved output must be blank after a longer list shrinks.
Things to watch
Numeric coercion merges 001 with 1
Preserve the original text identity.
A blank list produces a blank key
Exclude the empty placeholder as well as found IDs.
About this example
These original single-anchor tasks use a declared ASCII ID policy, signed numeric cents and bounded source rows. Browser calculations and independent rules are checked separately from native Excel/Sheets recalculation, editing and save/reopen.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.