區分大小寫的查詢：Excel 與 Google Sheets

Which versions this applies to

The results below were measured in Google Sheets on November 12, 2026. EXACT, FILTER, XLOOKUP, MATCH, and COUNTIF exist in current Excel according to Microsoft; XLOOKUP and FILTER are not in Excel 2016 or 2019. I did not re-test this list in desktop Excel today. In this Sheets file, FILTER(B2:B4,EXACT(A2:A4,"sku-103")) spilled without ARRAYFORMULA.

The list I used

SKU-103    0.55
sku-103    9.99
SKU-110    4.1

Those first two rows are not the same product if your catalog is case-sensitive. They are the same product if you are using XLOOKUP the usual way.

XLOOKUP, MATCH, and COUNTIF ignore case

=XLOOKUP("sku-103",A2:A4,B2:B4,"missing")     → 0.55
=XLOOKUP("SKU-103",A2:A4,B2:B4,"missing")     → 0.55
=MATCH("sku-103",A2:A4,0)                     → 1
=COUNTIF(A2:A4,"sku-103")                     → 2
=XLOOKUP("sku-103",A2:A4,B2:B4,"missing",0,-1) → 9.99

Both XLOOKUP calls returned the first row’s price, 0.55, regardless of how I capitalized the lookup value. MATCH pointed at row 1 (SKU-103). COUNTIF counted both SKU rows as hits. The last-match XLOOKUP (search_mode -1) returned 9.99 because the lowercase row is the last case-insensitive hit, not because XLOOKUP suddenly became case-sensitive.

If your sheet only ever has one casing, you will not notice. If both casings can appear, you will get the first one XLOOKUP sees, which may be the wrong price.

FILTER plus EXACT is case-sensitive

EXACT was the function that distinguished the two rows:

=EXACT("sku-103",A2)    → FALSE    (A2 is SKU-103)
=EXACT("sku-103",A3)    → TRUE     (A3 is sku-103)
=FILTER(B2:B4,EXACT(A2:A4,"sku-103"))     → 9.99
=FILTER(B2:B4,EXACT(A2:A4,"SKU-103"))     → 0.55

FILTER with EXACT returned only the price that belonged to that exact casing. I did not wrap it in ARRAYFORMULA; the formula spilled on its own in this file.

Microsoft documents EXACT as a case-sensitive comparison in Excel as well. I did not re-run this FILTER in desktop Excel today.

For a yes/no check on a single pair of cells, EXACT is enough. For a lookup down a column, put EXACT inside FILTER (or in a helper column that you then look up). I did not test an XLOOKUP of a helper column in this run; the FILTER form is the one I actually got 9.99 and 0.55 from.

Excel and Google Sheets differences

XLOOKUP and COUNTIF are documented by Microsoft as not case-sensitive. That matches this Sheets file. EXACT is documented as case-sensitive. Current Excel can use FILTER with EXACT; older Excel would need a helper column and INDEX/MATCH, which I did not test here. FIND is also case-sensitive, but it searches inside text rather than looking up a key; see FIND vs SEARCH.

Common mistakes

• Trusting XLOOKUP("sku-103",...) to miss SKU-103. It returned 0.55, the first row.
• Using COUNTIF to see whether a case-sensitive duplicate exists. It counted both casings and returned 2.
• Assuming a last-match XLOOKUP is case-sensitive because it returned 9.99. That was the last case-insensitive hit in the list.
• Forgetting that EXACT is the check that actually returned FALSE on SKU-103 vs sku-103.

FAQ

How do I do a case-sensitive lookup in Google Sheets?

=FILTER(B2:B4,EXACT(A2:A4,"sku-103")) returned 9.99 from this list, and the uppercase lookup returned 0.55. XLOOKUP on its own did not distinguish them.

Why did XLOOKUP return the uppercase row when I searched for sku-103?

Because it is not case-sensitive here. Both "sku-103" and "SKU-103" returned 0.55, the first row.

Is COUNTIF case-sensitive?

Not in this test. COUNTIF(A2:A4,"sku-103") was 2, one for each casing.

Does this work in Excel 2016?

EXACT does. FILTER and XLOOKUP do not, according to Microsoft. I did not build an Excel 2016 helper-column version as part of this test.

What if both casings should be treated as the same SKU?

Then the default XLOOKUP behavior is what you want. The risk is only when the two strings are supposed to mean different things, as 0.55 and 9.99 did here.

Related tutorials

• XLOOKUP vs INDEX MATCH vs VLOOKUP: Which Should You Use in 2026?
• Compare Two Columns in Excel and Google Sheets and Find Missing Values
• FIND vs SEARCH in Excel and Google Sheets
• Excel & Google Sheets Lookup Errors: The Complete Troubleshooting Guide

Live-test findings

Google Sheets, November 12, 2026. List: SKU-103/0.55, sku-103/9.99, SKU-110/4.1. XLOOKUP of either casing returned 0.55. MATCH("sku-103",...) returned 1. COUNTIF returned 2. Last-match XLOOKUP returned 9.99. EXACT("sku-103",A2) was FALSE, EXACT("sku-103",A3) was TRUE. FILTER+EXACT returned 9.99 for the lowercase key and 0.55 for the uppercase key.

Sources

• Microsoft Support: EXACT function: https://support.microsoft.com/en-us/office/exact-function-d3087698-fc15-4a15-9631-12575cf29926
• Microsoft Support: XLOOKUP function: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
• Microsoft Support: FILTER function: https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
• Microsoft Support: COUNTIF function: https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34
• Google Docs Editors Help: EXACT: https://support.google.com/docs/answer/3094073
• Google Docs Editors Help: FILTER: https://support.google.com/docs/answer/3093197