Excel · ChatGPT
Compare Two Lists or Columns in Excel
Compare exact IDs, SKUs, or codes without changing either source list. Use a helper status, return only values missing from the other list, or reconcile duplicate occurrences one-for-one with three tested prompts.
Choose your comparison — copy the prompt:
I need to compare two lists of product IDs in Excel without changing either source list. On worksheet Product Check, List A is in A2:A9: A2 P-104 A3 P-107 A4 [blank] A5 P-104 A6 P-112 A7 P-119 A8 P-125 A9 P-130 List B is in D2:D8: D2 P-107 D3 P-104 D4 [blank] D5 P-125 D6 P-140 D7 P-104 D8 P-119 In helper column B, starting in B2, give me a formula to fill through B9 that returns Match when a nonblank List A ID appears anywhere in List B, Missing when it does not, and a blank for a blank List A cell. Use exact matching and preserve both source lists. Use XMATCH for current Excel and provide an exact-match MATCH alternative for versions without XMATCH. Expected B2:B9: Match, Match, [blank], Match, Missing, Match, Match, Missing. Both lists contain duplicate P-104 values. This is membership logic, not one-to-one reconciliation: every P-104 in List A is Match if P-104 occurs at least once in List B. List B order must not matter. All IDs are consistently stored as text. Explain relative/absolute references and version limitations.
Use for a row-by-row exact Match or Missing helper column.
I need a dynamic, non-destructive Excel result showing every SKU from List A that does not appear in List B. On worksheet SKU Audit, List A is in A2:A10: A2 SKU-210 A3 SKU-211 A4 [blank] A5 SKU-214 A6 SKU-215 A7 SKU-211 A8 SKU-219 A9 SKU-220 A10 SKU-225 List B is in D2:D8: D2 SKU-214 D3 SKU-210 D4 SKU-230 D5 [blank] D6 SKU-219 D7 SKU-225 D8 SKU-240 Give me one dynamic-array formula in F2 using FILTER with exact XMATCH membership logic. Exclude blanks, preserve List A order, preserve repeated unmatched values, do not modify/sort/deduplicate either list, and return No unmatched values if every nonblank List A value exists in List B. Expected spill from F2: SKU-211, SKU-215, SKU-211, SKU-220. SKU-211 occurs twice in List A and zero times in List B, so both occurrences must remain. List A order determines output order; List B order does not. All SKUs are consistently stored as text, and cells below F2 are empty so the result can spill. Explain the formula, #SPILL! constraint, and Excel requirements for FILTER, XMATCH, and dynamic arrays.
Use for a live spilled list of values present only in List A.
I need to reconcile duplicate invoice IDs between two Excel lists. A simple does-this-value-exist result is not enough because each List B occurrence may match only one List A occurrence. On worksheet Invoice Reconciliation, List A is in A2:A10: A2 INV-401 A3 INV-402 A4 INV-401 A5 INV-403 A6 INV-404 A7 INV-402 A8 INV-405 A9 [blank] A10 INV-402 List B is in D2:D9: D2 INV-402 D3 INV-401 D4 INV-406 D5 INV-402 D6 INV-404 D7 [blank] D8 INV-401 D9 INV-407 In B2, give me a formula to fill through B10 that returns Matched occurrence while the running count of an ID in List A is less than or equal to its total available count in List B, Extra in List A after List A exceeds that count, and blank for a blank A cell. Treat duplicates separately and preserve both lists. Expected B2:B10: Matched occurrence, Matched occurrence, Matched occurrence, Extra in List A, Matched occurrence, Matched occurrence, Extra in List A, [blank], Extra in List A. Counts: INV-401 A=2/B=2; INV-402 A=3/B=2; INV-403 A=1/B=0; INV-404 A=1/B=1; INV-405 A=1/B=0. Therefore the first two INV-402 occurrences match and the third is extra. List A order determines which duplicate is extra; List B order does not. All IDs are text with no wildcard characters. Prefer functions compatible with non-dynamic-array Excel. Explain expanding and fixed references, duplicates, and version compatibility.
Use when duplicate quantities must reconcile one occurrence at a time.
Before — two lists, no comparison status
| Row | List A | List B |
|---|---|---|
| 2 | P-104 | P-107 |
| 3 | P-107 | P-104 |
| 4 | [blank] | [blank] |
| 5 | P-104 | P-125 |
| 6 | P-112 | P-140 |
| 7 | P-119 | P-104 |
| 8 | P-125 | P-119 |
| 9 | P-130 | — |
List B order differs and both lists contain duplicate P-104.
→
After — exact membership status in B2:B9
| Cell | List A | Status |
|---|---|---|
| B2 | P-104 | Match |
| B3 | P-107 | Match |
| B4 | [blank] | [blank] |
| B5 | P-104 | Match |
| B6 | P-112 | Missing |
| B7 | P-119 | Match |
| B8 | P-125 | Match |
| B9 | P-130 | Missing |
=IF(A2="","",IF(ISNUMBER(XMATCH(A2,$D$2:$D$8,0)),"Match","Missing"))Before — unmatched SKUs mixed into List A
| Row | List A | List B |
|---|---|---|
| 2 | SKU-210 | SKU-214 |
| 3 | SKU-211 | SKU-210 |
| 4 | [blank] | SKU-230 |
| 5 | SKU-214 | [blank] |
| 6 | SKU-215 | SKU-219 |
| 7 | SKU-211 | SKU-225 |
| 8 | SKU-219 | SKU-240 |
| 9 | SKU-220 | — |
| 10 | SKU-225 | — |
The output must exclude blanks but retain both unmatched SKU-211 rows.
→
After — dynamic list only in List A
| Cell | Spilled result |
|---|---|
| F2 | SKU-211 |
| F3 | SKU-215 |
| F4 | SKU-211 |
| F5 | SKU-220 |
=FILTER(A2:A10,(A2:A10<>"")*ISNA(XMATCH(A2:A10,D2:D8,0)),"No unmatched values")Before — membership alone hides excess duplicates
| ID | List A count | List B count |
|---|---|---|
| INV-401 | 2 | 2 |
| INV-402 | 3 | 2 |
| INV-403 | 1 | 0 |
| INV-404 | 1 | 1 |
| INV-405 | 1 | 0 |
A basic membership check would mark all three INV-402 rows as found.
→
After — each available occurrence used once
| Cell | List A | Result |
|---|---|---|
| B2 | INV-401 | Matched occurrence |
| B3 | INV-402 | Matched occurrence |
| B4 | INV-401 | Matched occurrence |
| B5 | INV-403 | Extra in List A |
| B6 | INV-404 | Matched occurrence |
| B7 | INV-402 | Matched occurrence |
| B8 | INV-405 | Extra in List A |
| B9 | [blank] | [blank] |
| B10 | INV-402 | Extra in List A |
=IF(A2="","",IF(SUMPRODUCT(--($A$2:A2=A2))<=SUMPRODUCT(--($D$2:$D$9=A2)),"Matched occurrence","Extra in List A"))Choose the comparison that matches the question
Status beside each row
Does this nonblank List A value occur anywhere in List B?
XMATCH
One-sided output list
Return every List A value absent from List B
FILTER
Duplicate reconciliation
Match only as many occurrences as List B contains
SUMPRODUCT
How to compare two Excel lists safely
A practical five-step workflow
1
Define what “match” means
Decide whether you need simple membership, values only on one side, or one-to-one duplicate reconciliation.
2
Check blanks and data types
Handle blank List A cells explicitly. Confirm IDs are consistently text or numeric and do not contain hidden spaces.
3
Lock lookup ranges
Use absolute references for the comparison list. Keep the current List A row relative when filling a helper formula down.
4
Preserve duplicate intent
A membership formula may legitimately mark repeated values as matches. Use occurrence reconciliation only when each destination occurrence can be consumed once.
5
Verify the output
Compare the helper or spill result with known matches and missing values before using it downstream.
Do not normalize identifiers blindly. Text/number mismatches and hidden spaces can cause exact comparisons to fail, but changing IDs may remove meaningful leading zeros.
Frequently asked questions
How do I compare two lists in Excel for matches?
Use an exact-match XMATCH or MATCH formula in a helper column. Test the List A value against a fixed List B range, return Match or Missing, and handle blank List A cells explicitly.
How do I return values in one Excel list but not another?
In dynamic-array Excel, combine FILTER with exact XMATCH membership logic. FILTER can preserve List A order and repeated unmatched values while excluding blanks.
Does XMATCH treat duplicate values as separate matches?
No. A basic XMATCH membership test only establishes whether a value occurs at least once. To reconcile duplicates one-for-one, compare each value's running occurrence number in List A with its total available count in List B.
Does list order affect Excel comparison formulas?
Exact membership results do not depend on List B order. A FILTER result normally preserves List A order, and duplicate-aware reconciliation uses List A order to determine which excess occurrence is marked extra.
Why do matching IDs sometimes appear missing in Excel?
Exact comparisons can fail when one ID is stored as text and the other as a number, or when values contain hidden spaces or other characters. Normalize the data only after confirming the intended identifier format.