Two exports can contain the same SKU codes but still disagree in ways that matter. One list may be in a different order, one code may be completely absent, and another may differ only because one system uses lowercase letters.
For excel compare two lists case sensitive differences, do not use one formula for every question. Use COUNTIF to answer “Does this ID appear anywhere in the other unordered list?” and use EXACT to answer “Are these two already matched cells identical, including letter case?”
Quick Fix: choose the question first
The safest approach is to keep two separate helper columns. Use one for list presence and one for exact comparison. That makes the business rule visible to anyone reviewing the reconciliation.
| What you need to know | Use | Result to interpret |
|---|---|---|
| Is this SKU missing from an export whose row order does not match? | COUNTIF |
0 means absent; 1 means one occurrence. |
| Do two already aligned SKU cells match exactly? | EXACT |
TRUE means identical text; FALSE means a text difference. |
Does SKU-17 match sku-17 for a presence check? |
COUNTIF |
Yes. COUNTIF does not use text case to distinguish the criterion. |
| Is the lower-case version a data difference worth flagging? | EXACT |
Yes. EXACT returns FALSE for a case-only mismatch. |
This split prevents two common errors: calling an ID “missing” simply because its row moved, and calling two paired records “identical” when their letter case differs. For other ways to establish which rows belong together, see this guide to compare two Excel lists.
Worked SKU reconciliation walkthrough
Assume columns A and B contain records that have already been paired by row, perhaps after matching an export to a system report. Column D is a separate, unordered export used only to ask whether each source SKU exists anywhere in that list.
Enter SKU-17 in A2 and B2, enter SKU-18 in A3 and sku-18 in B3, enter SKU-19 in A4 and B4, and place sku-17, SKU-20, and SKU-18 in D2:D4.
| Row | Source SKU | Matched system SKU | Unordered system-list value |
|---|---|---|---|
| 2 | SKU-17 |
SKU-17 |
sku-17 |
| 3 | SKU-18 |
sku-18 |
SKU-20 |
| 4 | SKU-19 |
SKU-19 |
SKU-18 |
Follow these steps
- Set up the two purposes. Treat A2:A4 as the source list. Treat B2:B4 as the already aligned comparison values. Treat D2:D4 as the unordered list where row position has no meaning.
- Check unordered presence. Click E2, enter the formula below, and copy it down through E4.
=COUNTIF($D$2:$D$4,A2)- Read the count before changing data. The dollar signs keep D2:D4 fixed as the formula is copied down. A result of
0means the source SKU is absent. A result of1means it occurs once; a larger number needs duplicate review. - Test the paired cells separately. Click F2, enter the formula below, and copy it down through F4.
=EXACT(A2,B2)- Classify the result. A
TRUEin column F means the two paired cells contain identical text, including case. AFALSEmeans they differ and should be checked in the source systems.
In E2, enter =COUNTIF($D$2:$D$4,A2) and fill down to check unordered presence; in F2, enter =EXACT(A2,B2) and fill down to test the aligned pair. The results are 1 and TRUE for SKU-17, 1 and FALSE for the case-only SKU-18 difference, and 0 and TRUE for SKU-19, which is aligned but absent from D2:D4.
Notice the two different decisions on row 3. SKU-18 is present in the unordered list, so it is not missing. But its aligned value is sku-18, so it fails the case-sensitive test. On row 4, the paired values agree exactly, but the source SKU is not present in the separate unordered export. These are different reconciliation outcomes and should not be merged into one label.
Why COUNTIF and EXACT give different answers
COUNTIF(range,criteria) counts the cells in a range that meet one criterion. In this example, the range is $D$2:$D$4 and the criterion is the source SKU in column A. Microsoft states in its COUNTIF function guidance that text criteria are not case-sensitive. Therefore, SKU-17 finds sku-17 and returns 1.
EXACT(text1,text2) is not a list-search formula. It compares the text in two specified cells and returns a logical result. Microsoft’s EXACT function reference says that EXACT is case-sensitive and returns TRUE only for exactly matching text strings; formatting differences are ignored. Therefore, =EXACT(A3,B3) returns FALSE for SKU-18 and sku-18.
Important: EXACT compares only the two cells you give it; it does not search an unordered list for a case-sensitive match. Pair or align the records first, then use EXACT to audit that pair. If records are not yet paired, start with an unordered reconciliation process such as the methods in this two-list reconciliation guide.
Limitations and common reconciliation mistakes
| Issue | What it means | Practical response |
|---|---|---|
COUNTIF returns 1 for different case |
The ID is present, but COUNTIF does not assess case. | Use EXACT after identifying the corresponding record. |
COUNTIF returns 2 or more |
The comparison list contains duplicate occurrences. | Investigate the duplicate before marking the item reconciled. |
EXACT returns FALSE unexpectedly |
Case, spaces, or other text characters may differ. | Inspect the original entries before editing either source. |
| Rows are not paired | EXACT is comparing unrelated records. | Do not use a row-by-row EXACT result as a missing-item check. |
COUNTIF returns 1 when A2 contains SKU-17 and D2:D4 contains sku-17, because COUNTIF text criteria do not distinguish upper- and lowercase letters. EXACT returns FALSE when A3 contains SKU-18 and B3 contains sku-18, even though the characters otherwise match.
If an ID occurs more than once in the other list, COUNTIF returns a number greater than 1; treat that as a duplicate to investigate, not as a clean one-to-one reconciliation. Leading or trailing spaces can create an unexpected EXACT FALSE result, so remove accidental spaces before deciding that a case-only difference is meaningful.
Do not try to make COUNTIF prove an exact, case-sensitive match. Its useful role here is deliberately narrower: it identifies whether a value appears in an unordered range. Likewise, do not let a TRUE from EXACT prove the value was included in a separate export; it only proves that the two aligned cells match.
Compatibility and fallback
COUNTIF and EXACT are documented for Excel for Microsoft 365, Excel 2024, and Excel 2021, including the listed Mac versions in Microsoft’s references. If you use an edition not listed there, use the separate helper-column formulas in a copy of the workbook and verify the results in that version before relying on them.
The practical fallback is not a more complicated combined formula. Keep the two checks in separate columns: one COUNTIF column for unordered presence and one EXACT column for aligned case validation. This layout works well for review because a colleague can see whether a problem is an absence, a duplicate, or a paired-text difference without decoding a long formula.
Sources
Practice with the exact example data
Use a separate blank worksheet for each example. Enter each value at its listed cell address, then type the formula in AA1. Quoted text is shown in JSON notation to preserve spaces: enter the characters inside the quotes, without the quotes, using a leading apostrophe to keep text and spaces literal. An empty quoted string means an explicit empty text value; use an apostrophe entry where appropriate. Compare your result with the expected result below. These are synthetic practice data.
Example 1 — Main case
| Cell | Input value |
|---|---|
| A2 | "SKU-17" |
| B2 | "SKU-17" |
Formula in AA1: =EXACT(A2,B2)
Expected result: true
Example 2 — Boundary case
| Cell | Input value |
|---|---|
| A2 | "SKU-18" |
| B2 | "sku-18" |
Formula in AA1: =EXACT(A2,B2)
Expected result: false
These examples were checked with a limited calculator supporting numeric aggregates, ASCII text functions, exact criteria and exact first-match XLOOKUP. Only the displayed examples and supported semantics were calculated. They were not executed in Microsoft Excel. This check does not verify every claim in the article.
Prepared with automated drafting and review, official references, and a limited example calculator. Synthetic examples are provided for practice; no independent human review or Microsoft Excel execution is claimed.
Download the practice sheet (HTML) or Download the practice workbook (XLSX). The workbook has exact input cells and expected results; enter the provided formula yourself. Available after this article is published.