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.

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.

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.

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