Case Insensitive Unique in Google Sheets

Published on

The UNIQUE function is case-sensitive, but there are a few good options for case-insensitive unique in Google Sheets.

Usually, users use the functions PROPER, LOWER, or UPPER with UNIQUE for case-insensitive unique in Google Sheets.

It has one minor issue. It converts the returned output to proper, lower, or upper cases.

The syntax is as follows: =ArrayFormula(UNIQUE(PROPER(range)))

In this, you can replace ArrayFormula with INDEX or SORT and PROPER with LOWER or UPPER.

In the below table, I have shown the result of the above combinations by converting a few food & dishes names in the range B2:B5.

Foods & DishesProperLowerUpper
Unagi (eel)Unagi (Eel)unagi (eel)UNAGI (EEL)
TacosTacostacosTACOS
Unagi (eel)
tacos

Here is the case-insensitive formula in use in the second column.

=ArrayFormula(unique(proper(B2:B)))

In the third and fourth columns, the proper got replaced by lower and upper.

Actually, the output should be “Unagi (eel)” and “Tacos,” the first occurrences of the case-insensitive unique values.

If you try =UNIQUE(B2:B5) in Excel, you will get that result because the UNIQUE function in Excel is case-insensitive.

How do we get the same effect in Google Sheets?

Case-Insensitive Unique Formula Options in Google Sheets

Here are my best three suggestions, and in them, the first two formulas use running count to return case-insensitive unique values in Google Sheets.

The third formula is quite interesting as it uses REDUCE function for case-insensitive unique in Google Sheets.

Why is it interesting?

The REDUCE is for reducing an array into a single value. But that single value can even be a reduced array containing multiple values.

You can learn that with this example.

Here are the formulas one by one.

Case Insensitive Unique Formula in Google Sheets

Formula # 1 (D2):

=filter(B2:B,countifs(B2:B,B2:B,row(B2:B),"<="&row(B2:B))=1)

The COUNTIFS part returns the running count of the foods and dishes in the range B2:B.

The role of the FILTER function here is to return the names of foods and dishes wherever the running count is equal to 1.

Formula # 2 (E2):

=filter(B2:B,map(B2:B,lambda(r,countif(indirect("B2:B"&row(r)),r)))=1)

Here also, the logic is the same. The MAP, a Lambda Helper function, returns the running count, and the FILTER does its job as above.

Formula # 3 (C2):

=REDUCE(B2,B2:B,LAMBDA(a,v,if(not(sum(ifna(match(v,a,0)))),flatten(a,v),a)))

Here things are different! Let me explain this REDUCE case-insensitive unique formula in detail below.

Formula Explanation

Syntax: REDUCE(initial_value, array_or_range, lambda)

The initial_value (in the accumulator) is the first cell value (B2) in the array_or_range (B2:B).

Lambda Syntax: LAMBDA(name1, name2, formula_expression)

name1 = a

name2 = v

In REDUCE, as you may know, the accumulator (a) is updated in each step.

The v resolves to the current value in array_or_range (B2:B), row by row.

Now the formula_expression part, which is essential in the case-insensitive unique.

The formula expression is like this. If v doesn’t match in a, flatten a and v, else return a.

The following table will give you a clear picture of how the REDUCE can return case-insensitive unique values in Google Sheets.

Initially, a and v are the same, which is the value in cell B2.

After ProcessingMatches or Notav
B2B2 (v) matches in a so it returns aUnagi (eel)Unagi (eel)
B3B3 (v) doesn’t match in a so it returns flatten(a,v)Unagi (eel)Tacos
B4B4 (v) matches in a so it returns aUnagi (eel)
Tacos
Unagi (eel)
B5B5 (v) matches in a so it returns aUnagi (eel)
Tacos
tacos

That’s all. Thanks for the stay. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.