Two-list comparison template
By the Analistable team · Updated · 2 min read
Paste the old list in List A and the new list in List B, with the key (ID, email or SKU) in column A. COUNTIFS labels every row Both, Only in A or Only in B, INDEX/MATCH brings the other list's name across so changed values show as Different, and the Summary counts each outcome.
Part of our guide: How to compare two spreadsheets for differences and matches
What the template does
Comparing two lists comes up constantly: last month's customers against this month's, a supplier list against the ledger, an old export against a new one. The questions are always the same three: what is in both, what disappeared, what is new, plus whether anything changed for rows in both. The template answers all four with formulas you can read, following the compare spreadsheets guide.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| List A | Key, Name, In List B?, Name in B, Name check | Yes (first two) |
| List B | Key, Name, In List A? | Yes (first two) |
| Summary | Metric, Value | No (formulas) |
How to fill it in
- Paste the older list on List A and the newer list on List B, with the matching key in column A of both.
- Put the field you want to check for changes (name, price, status) in column B.
- Copy the formula columns down to the last row of each list.
- Read the Summary, then filter List A on Only in A and List B on Only in B to see the rows behind each count.
How the formulas work
Presence in the other list:
=IF(COUNTIFS('List B'!$A$2:$A$1000,A2)>0,"Both","Only in A")The value from the other list, and whether it changed:
=IFERROR(INDEX('List B'!$B$2:$B$1000,MATCH(A2,'List B'!$A$2:$A$1000,0)),"")
=IF(D2="","",IF(TRIM(B2)=TRIM(D2),"Same","Different"))The = comparison ignores case. If case matters, use EXACT instead; see case-sensitive matching.
Worked example
Two supplier lists of eight rows each:
| Metric | Value |
|---|---|
| Rows in List A | 8 |
| Rows in List B | 8 |
| In both lists | 6 |
| Only in List A (removed) | 2 |
| Only in List B (new) | 2 |
| In both, name different | 1 |
1002 Birch Co and 1006 Fern & Co dropped off; 1009 Iris Labs and 1010 Juno Ltd are new; 1004 changed from Delta LLC to Delta Ltd. The same idea appears in merging two lists and flagging new and removed rows.
Limits and when to move on
- Keys must match exactly. Numbers stored as text in one list and as numbers in the other will not match.
- If a key appears twice in List B, the Name in B column shows only the first match. Check for duplicates first.
- To compare every column, not just one, see comparing two Excel sheets for differences or use the free compare tool, which runs in your browser.
Frequently asked questions
- Can I compare on two columns, like first and last name?
- Add a helper column joining them (for example =A2&"|"&B2) in both lists and use that as the key.
- Does the order of rows matter?
- No. COUNTIFS and MATCH search the whole other list, so the lists can be in any order and of different lengths.
- Will it work in Google Sheets?
- Yes, unchanged. Quoted sheet names like 'List B' work in both Excel and Google Sheets.