大文字と小文字を区別する検索：Excel と Google Sheets

要点

XLOOKUP、MATCH、COUNTIF はこのテストでは大文字と小文字を区別しません。リストには SKU-103（価格 0.55）と sku-103（価格 9.99）があります。XLOOKUP("sku-103") も XLOOKUP("SKU-103") も 0.55、つまり先頭行です。COUNTIF は両方を数えて 2、MATCH("sku-103") は 1 行目を指します。

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

確認のポイント

大文字と小文字を区別するには FILTER と EXACT を使います。EXACT("sku-103",A2) は FALSE、A3 では TRUE です。FILTER と EXACT は小文字のキーで 9.99、大文字で 0.55 を返しました。このファイルでは FILTER に ARRAYFORMULA は不要でした。

注意点と互換性

末尾から探す XLOOKUP の 9.99 は、大文字小文字を無視した最後の一致であり、突然区別するようになったわけではありません。両方の表記を同じ SKU とみなすなら既定の XLOOKUP でよく、別の記録であるときだけ EXACT が必要です。

英語の詳細例・数式・著者メモ

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.

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