Urgent.News

What's breaking now, across hundreds of outlets.

Tech

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…

In Google Sheets, lookup functions typically ignore capitalization by default. This means that "abc", "Abc", and "ABC" are treated as the same value. Functions like VLOOKUP, HLOOKUP, and MATCH will return the first matching row regardless of case. However, some functions like EXACT and FIND are case-sensitive, meaning they only return TRUE when the text is identical, including case.

FIND is case-sensitive, while SEARCH is case-insensitive but sometimes behaves similarly. REGEXMATCH can be used for both case-sensitive and case-insensitive matching by adding the (?i) flag at the start of the pattern.

When using these functions, it's important to be aware of specific behaviors. The = operator returns TRUE for "abc"="ABC", while SEARCH("ab","xAbc") returns 2. REGEXMATCH("Abc","abc") returns FALSE by default, but can be made case-insensitive by adding (?i) at the beginning.

COUNTIF and SUMIF ignore case, while SUMPRODUCT allows for case-sensitive counting by multiplying the result of an array of BOOLs generated by EXACT. To find duplicates that respect case, a running count formula using SUMPRODUCT can be used with EXACT. UNIQUE is the only function that is case-sensitive, keeping each variation of a word separate.

When dealing with large datasets or potential typos, it's advisable to clean the data and consider the specific needs of your analysis before choosing the appropriate function. Snakbacks offers scripts and add-ons to assist with these tasks.

Rewritten by urgent.news; may contain errors — check the original.

Read the original at dev.to →

Report a problem with this story

More in Tech

Ghostscript's font bug reached 10 apps through a `.png`

Ghostscript's font bug reached 10 apps through a .png A file named .png that contains PostScript can reach Ghostscript from ten different applications — and a use-after-free in Ghostscript's Type 1…

  • .png file with PostScript code exploited ten apps
  • CVE-2026-125396 vulnerability in Ghostscript's Type 1 font loader
  • RCE exploited via memory manipulation in various applications

GRH proof's geometry is `fun _ _ _ => True`, in 24 files

GRH proof's geometry is fun _ _ _ => True , in 24 files Yesterday the Lean and Mathlib communities certified Alpöge's 17.4-million-line GRH proof as correct.

  • Geometry predicate defined as fun = True in Alpöge's proof
  • Author found actual geometry predicate named geoOf in source code
  • anyGeometry used in 24 files, geoOf in 12 files

I counted my 4,323 listings as companies. The biggest one is GitHub Pages.

I counted my 4,323 listings as companies. The biggest one is GitHub Pages. I run an AI tools directory. The homepage says "4,300+ tools" and the number is real: my database has exactly 4,323 rows in…

  • The database contains 4,323 listings, with 3,317 being companies.
  • GitHub Pages hosts 184 listings as the largest company.
  • 60% of listings end with "Latest AI" in their descriptions.

More from Sunday 22 November →