# Case-Sensitive and Case-Insensitive Match in Google Sheets

Most lookups in Google Sheets ignore capital letters, which is usually helpful and sometimes a problem. If "abc" and "ABC" are different codes, VLOOKUP will quietly return the wrong one. This page lists which functions ignore case, which respect it, and the formulas that force either behaviour.

## Which functions ignore case

These treat "abc", "Abc" and "ABC" as the same:

- VLOOKUP, HLOOKUP and MATCH
- COUNTIF, COUNTIFS, SUMIF, SUMIFS
- The = operator: ="abc"="ABC" returns TRUE
- SEARCH (but see below for FIND)

## Which functions respect case

- EXACT(a, b) returns TRUE only when the text is identical, including case.
- FIND(find, in) is case-sensitive: =FIND("ab","xAbc") gives #VALUE!, while =FIND("Ab","xAbc") gives 2. SEARCH does the same job ignoring case: =SEARCH("ab","xAbc") gives 2.
- REGEXMATCH is case-sensitive by default: =REGEXMATCH("Abc","abc") is FALSE. Add (?i) at the start of the pattern to ignore case: =REGEXMATCH("Abc","(?i)abc") is TRUE. It also matches part of a cell, so anchor with ^ and $ for a whole-cell match.

## The sample data

Row 1 holds headers. Master list, D2:E4 (code, price):

- abc, 1
- ABC, 2
- xyz, 3

Search values in A2:A4: ABC, abc, Abc. Results go in column B.

## Method 1: the default lookup returns the first match

```
=VLOOKUP(A2,$D$2:$E$4,2,FALSE)
```

All three searches return 1, because the lookup ignores case and takes the first row that matches. For ABC, you wanted 2. It does not warn you.

## Method 2: case-sensitive lookup with INDEX and MATCH

Test each list item with EXACT, and ask MATCH for the first TRUE:

```
=IFNA(INDEX($E$2:$E$4,MATCH(TRUE,ARRAYFORMULA(EXACT($D$2:$D$4,A2)),0)),"Not found")
```

Fill down. The results are 2, 1 and Not found. "Abc" matches nothing exactly, so it ends up as Not found. The ARRAYFORMULA is needed so EXACT checks the whole list at once. IFNA turns a true no-match into text without hiding other errors.

FILTER can do the same without ARRAYFORMULA:

```
=IFNA(INDEX(FILTER($E$2:$E$4,EXACT($D$2:$D$4,A2)),1),"Not found")
```

It returns the same 2, 1, Not found. INDEX with 1 keeps the first row if several are identical.

## Method 3: case-sensitive counts

COUNTIF ignores case. To count exact-case matches use SUMPRODUCT:

```
=COUNTIF($D$2:$D$4,"abc")
=SUMPRODUCT(--EXACT($D$2:$D$4,"abc"))
```

The first gives 2, the second gives 1.

## Method 4: case-sensitive duplicates

Put the values Apple, apple, Apple, APPLE in A2:A5. A running count that respects case, in B2 filled down:

```
=SUMPRODUCT(--EXACT($A$2:A2,A2))
```

Results: 1, 1, 2, 1. A value greater than 1 marks a repeat of the exact same text. The case-ignoring version, =COUNTIF($A$2:A2,A2), gives 1, 2, 3, 4. To keep only the first exact-case copy, filter column B to 1, or use =FILTER(A2:A5,B2:B5=1).

## What about UNIQUE?

UNIQUE is the odd one out: it is case-sensitive. With Apple, apple and APPLE in A2:A4:

```
=COUNTA(UNIQUE(A2:A4))
```

returns 3, because UNIQUE keeps each different capitalisation. COUNTUNIQUE(A2:A4) also returns 3. So a list can look duplicated to COUNTIF (which says Apple appears 3 times) and unique to UNIQUE at the same time. Force the behaviour you want:

- To ignore case: =UNIQUE(LOWER(A2:A4)) returns a single value, apple, in lower case.
- To respect case: UNIQUE already does, or use the Method 4 helper column and keep the rows where it equals 1.

The same thinking applies to Data > Data cleanup > Remove duplicates. Try it on a copy of three differently capitalised rows before trusting it on real data.

## Forcing case-insensitive behaviour

- For FIND, use SEARCH, or wrap both sides: =FIND(LOWER("ab"),LOWER(A2)).
- For EXACT, wrap both sides: =EXACT(LOWER(A2),LOWER(B2)) returns TRUE for "Abc" and "aBC".
- For REGEXMATCH, start the pattern with (?i).

## Common traps

- Matching "abc" with trailing spaces fails in both modes. Use TRIM first.
- Extra spaces inside a value ("ab  c") also break matches in both modes, so clean the data before comparing.
- Case-sensitive formulas test every row of the list, so on very large ranges apply them only to the column you need.

## When an add-on or script pack helps

Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.

If your mismatches are typos or word order rather than capital letters, the Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas is built for that, but for case alone the formulas above are all you need.

Our free add-on Fuzzy Match is now on the Google Workspace Marketplace: https://workspace.google.com/marketplace/app/fuzzy_match_fuzzy_vlookup_for_google_she/250583114112. Duplicate Remover is now on the Marketplace too: https://workspace.google.com/marketplace/app/duplicate_remover_for_google_sheets/396899597509.


Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.


*Originally published at https://www.snakbacks.com/case-insensitive-match-google-sheets. Written with AI assistance; every formula on this page was tested in a real Google Sheet.*

