Super Convenient - The *Magic* to Switch Google Sheets XLOOKUP and VLOOKUP Functions to (Relatively) Strict Matching 2

"Google Sheets matching is loose"

It sounds like a light novel title, but isn't this actually a problem for many people?

What exactly does this mean? I will touch on that in the main part, but this time I will introduce a magic to make this troublesome Google Sheets matching "relatively" strict using the XLOOKUP function.

In the first part of the series written in May 2024, I introduced the magic to switch ○○IF(S) series functions like COUNTIF, COUNTIFS, SUMIF, and SUMIFS to relatively strict matching.

This is the second part of the series, and it's funny how the "2" in this title can also be used as a nice foreshadowing payoff lol

The previous note was about how to use and practical examples of the new Google Sheets feature "Multiple Selection Dropdown List".

What is the *magic* 2 to change XLOOKUP and VLOOKUP to relatively strict matching?

Since this is an easy hack you can use right away, I'll write the method (the answer) right away this time as well.

Did you notice?

The XLOOKUP function searches A:A (column A) from the top for something that matches the keyword in cell D2, and returns the number in column B (B:B) of the first matching row , but

The formula in the GIF image above

results in "Sousou no Frieren", "Sousou no Furiiren", and "Sousou no FURIREN" all being judged as matching the "Sousou no Frieren" in the second row, returning the number 1.

As expected, "Sousou no Fuuriren" is judged as something different and returns 9, so XLOOKUP is functioning correctly.

This is caused by the troublesome specification of Google Sheets'

"loose matching that does not distinguish between hiragana/katakana, full-width/half-width, or long vowel marks and vowels"

.

However, as shown in the GIF image below, for the XLOOKUP formula

set the 5th argument (match mode) to 2.

With just this, the matching criteria of the XLOOKUP function becomes (relatively) strict,

The search result for Frieren: Beyond Journey's End is 3
The search result for Frieren: Beyond Journey's End is 4
The search result for Frieren: Beyond Journey's End is 7

and you will be able to get the correct results.

In summary, it looks like this ↓

I will touch on this later, but since uppercase and lowercase letters cannot be distinguished, I am using the term "relatively" strict matching, but the troublesome specification of Google Sheets, "looseness of matching judgment", is resolved all at once with this simple technique.

Searching using wildcards is sometimes referred to as "fuzzy search," but by setting it to "fuzzy search" mode, it actually becomes a "strict search"....

It's just confusing, isn't it? lol

Let's take a closer look at this magic*2 that can be used with XLOOKUP!

Matching Criteria in Google Sheets and Excel

Let's touch upon the looseness of matching in Google Sheets and the differences from Excel once again.

Google Sheets matching is loose

Regarding the loose matching judgment in Google Sheets, if we summarize the equals match judgment:

This is how it is. It's quite loose, isn't it?

"Fern is here" and "It will increase" are considered a match... that's a problem lol

I covered the verification of this matching in detail in the first part of the "Magic to Switch to Strict Matching" series. Please refer to that for more details.

Excel's equal matching is strict even in the web version

Even in the free web version, Excel performs strict matching.

When judging by equal matching, it naturally distinguishes between hiragana and katakana, but it also distinguishes between full-width and half-width characters, as well as large characters like "あいう" and small characters like "ぁぃぅ".

Uppercase and lowercase half-width English letters are considered identical, but this is the same for the installed version of Excel and is a long-standing specification.

You almost never encounter the issues caused by loose matching misjudgments like you do in Google Sheets.

Excel's XLOOKUP and UNIQUE functions have looser matching than equal matching

However, some of the new functions added to Excel in recent years have matching criteria that are looser than equal matching, which can lead to cases where misjudgments cause problems.

One of the representative functions for this is the UNIQUE function, and

As shown here, it cannot distinguish between half-width and full-width characters, so "Frieren: Beyond Journey's End" and "Frieren: Beyond Journey's End (half-width)" are considered a match, resulting in only the "Frieren: Beyond Journey's End" at the top remaining.

Also, in addition to half-width/full-width, uppercase and lowercase letters are considered identical, so

FRIEREN
frieren
F ｒｉｅｒｅｎ
ｆｒｉｅｒｅｎ

All of these are judged as a match, and only the top one, FRIEREN, remains.

And another one, the highly popular XLOOKUP function, which is considered the ultimate search function, actually has loose matching criteria.

"Frieren: Beyond Journey's End (half-width)" when searching, it might hit the "Frieren: Beyond Journey's End" that appears earlier in the search range A2:A14 and return 1, or

frieren
F ｒｉｅｒｅｎ
ｆｒｉｅｒｅｎ

An event occurs where these are determined to match FRIEREN which is higher up, and return 10.

In Google Sheets, for some reason, there was a mysterious specification where only full-width katakana long vowel marks and half-width katakana long vowel marks were distinguished, but in Excel's XLOOKUP and UNIQUE functions, they are treated as a match without distinguishing between full-width and half-width, including the long vowel marks.

Just like the UNIQUE function, Excel's XLOOKUP match determination is also designed so that it cannot distinguish between half-width/full-width and uppercase/lowercase letters.

Of course, if you compare them with equals they are determined to be different and return FALSE, and

VLOOKUP-san is the same as the match determination with equals, where it cannot distinguish between uppercase and lowercase half-width alphabet letters, but it distinguishes and determines matches strictly for everything else.

It can be said that this is a unique specification only for new Excel functions such as the UNIQUE function and XLOOKUP function..

It is an unexpected weakness of the strongest search function, XLOOKUP.

By the way, Excel's XMATCH function also treats full-width and half-width characters as the same.

Note that the Google Sheets XLOOKUP function seems to use the same equals determination as the VLOOKUP function, so there is no difference in search results. (It's loose to begin with, so I don't really mind lol)

The magic to make Google Sheets match determination relatively strict

As shown, even in Excel, when using the UNIQUE or XLOOKUP functions, the determination can be a bit loose, but it is still Google Sheets' loose match determination that is overwhelmingly more troublesome than Excel's.

I believe that resolving this will lead to promoting the use of Google Sheets in Japan, so mir has also been doing various verifications and sharing information in my own small way lol

The magic to make XLOOKUP and VLOOKUP strict had a hint in the behavior of conditional formatting

For COUNTIF, SUMIFS, and other XXIF and XXIFS functions, you can make the match determination relatively strict by adding { } curly braces to the range to create an array.

This is the hack {magic} introduced in the first part of the "Magic to change to strict match determination" series.

At the time of the previous note, I hadn't found a good way to make XLOOKUP or VLOOKUP determinations strict, but there was actually a hint that leads to it.

That is the method to make conditional formatting determinations strict.

When you set conditional formatting to "is exactly," it actually results in a loose match

When using the conditions provided in conditional formatting, you would normally choose "Text is exactly" for matching, but this results in a loose match that does not distinguish between hiragana/katakana or half-width/full-width characters.

However, if you select "Text contains", for some reason, it becomes a strict match that distinguishes between hiragana/katakana and half-width/full-width characters.

Taking it one step further here

"Could the LOOKUP family be using this logic too?"

I wish I had thought of that, but I overlooked it. My apologies.

I did try using wildcards with the COUNTIF and SUMIFS functions, but

The loose matching of COUNTIF and SUMIFS bugs out when combined with wildcards

In IF and IFS functions like COUNTIF and SUMIFS, wildcards do not perform strict matching but rather have a mysterious bug-prone specification, so I ended my verification there.

Verifying strict matching with the XLOOKUP function's wildcard mode

The XLOOKUP function, imported from Excel and available in Google Sheets since September 2022.

In the note I wrote at the time introducing it ↓,

XLOOKUP cannot perform searches with "strict matching."

I wrote that, but by using the hack introduced here—the magic of specifying 2 in the 5th argument (match mode)—you can distinguish and match (search) "hiragana, katakana, full-width, half-width, and long vowel marks".

The point is that even though you set the 5th argument match mode to 2, which is the mode where wildcards can be used, you don't actually use wildcards.

In the case of numbers, numeric 1 and half-width string 1 are distinguished even in a standard XLOOKUP with the match mode omitted.

However, full-width '1' and half-width '1' cannot be distinguished.

By setting this to match mode 2, you can see that it is now possible to distinguish between full-width '1' and half-width '1'.

However, even using this magic, you cannot perform matching that distinguishes between uppercase and lowercase half-width letters.

By setting the match mode to 2, you can distinguish between full-width uppercase and lowercase letters, but just like in Excel, half-width letters will match regardless of case.

It is the same level of strictness (matching) as the {magic} of array conversion used in COUNTIF and SUMIFS.

That is why I describe it as "relatively" strict matching.

What happens if you set the 6th argument (search mode) of XLOOKUP to -1?

When the search mode is set to -1, it searches from the end (bottom in the case of vertical direction).

When using a normal XLOOKUP without specifying a match mode, many cells return 8 because the mixed string "Frieren: Beyond Journey's End" (with typos) is judged to match "Frieren: Beyond Journey's End" (correct).

On the other hand, the one with match mode 2 specified is searching without any problems.

It seems that the strictness of the match judgment is also effective with search mode -1 (search from the opposite direction).

The magic of strict judgment can also be used with the XMATCH function

The XMATCH function, which was added at the same time as XLOOKUP, also has a match mode as its 3rd argument, and you can specify the use of wildcards with 2.

By specifying the match mode as 2 here, just like with XLOOKUP, you can switch to a relatively strict match judgment even with the XMATCH function.

*Magic* 2 worked!

Can the *magic* also be used with VLOOKUP, HLOOKUP, and MATCH?

So, what about the VLOOKUP function, HLOOKUP function, and MATCH function where match mode 2 cannot be specified?

While VLOOKUP, HLOOKUP, and MATCH functions do not allow you to specify wildcard usage via a match mode, using * within the first argument will automatically treat it as a wildcard and allow for a "contains" search.

In the case of VLOOKUP

In the case of HLOOKUP

In the case of MATCH

In all cases for VLOOKUP, HLOOKUP, and MATCH, by adding * to perform a search using wildcards, I was able to change it to a relatively strict match that distinguishes between hiragana/katakana, full-width/half-width characters, and long vowel marks versus vowels.

By the way, the formulas have * at both the beginning and end, but it is fine to have it at just the beginning or just the end (either one).

However, since * is a wildcard representing any string of 0 or more characters,

it will perform a search that "contains" the keyword (if * is added to both sides), so searching for "Frieren: Beyond Journey's End" will also hit "Frieren: Beyond Journey's End Season 2 Production Decided".

This can be used if you don't need to consider such partial matches, but I cannot recommend it for practical business use.

It seems better to use the XLOOKUP and XMATCH functions, which allow you to search without using wildcards even when switching to wildcard mode..

Performance when using XLOOKUP with match mode 2

The fact that XLOOKUP switches to a relatively strict match judgment by setting the match mode to 2 suggests that the search logic is different from the normal mode.

Given that, you might be concerned that setting the match mode to 2 will slow down the search speed.

I tested it with 100,000 rows and 2 columns of data, and the result display speed for both omitting the match mode and specifying match mode 2 was about the same, and I felt no difference.result display speed was about the same and I felt no difference

In this case, it might be fine to just memorize "the 5th argument is 2" for everyday use.

The XLOOKUP Magic You Can Also Use in Excel

Actually, this hack to make Google Sheets' XLOOKUP perform relatively strict matching wasn't something I, mir, discovered on my own; it was originally discovered in the Excel community.

Excel's XLOOKUP can distinguish between full-width and half-width characters using the 5th argument (match mode) 2

As I wrote earlier, for some reason, the new UNIQUE and XLOOKUP functions in Excel treat full-width and half-width characters as matches without distinction.

For XLOOKUP, you can set the 5th argument (match mode) to 2 (wildcard match) to achieve this:

Oh my goodness~

It is now able to distinguish between half-width and full-width characters for matching, yielding the same results as the VLOOKUP function.

It cannot distinguish between uppercase and lowercase letters, but it is as strict as the matching performed by VLOOKUP or the equals sign.

It doesn't have as much impact as it does in Google Sheets, but this discovery is the basis for it.

The original source is from Excel-related posts on X (formerly Twitter).

There might be sites that have introduced this trick, but I, mir, learned about it from posts by hardcore Excel users on X.

A mix of gems and stones as X may be, it really does have timely and valuable information.
Especially the Excel community, which has a deep and passionate user base.

So, I was taught that applying this mechanism to Google Sheets would be even more convenient, and I am summarizing it here on note.

To everyone whose information I referenced,
thank you very much. <(_ _)>

Summary of the magic to make Google Sheets' loose matching relatively strict

Here is the summary.

Regarding the troublesome specification of Google Sheets,

"matching that does not distinguish between hiragana/katakana, full-width/half-width, or long vowels and vowels"

To avoid this,

For XXIF functions and XXIFS functions such as COUNTIF, COUNTIFS, SUMIF, and SUMIFS, use the {magic} of enclosing the range in { } to create an array.

For LOOKUP-related functions and MATCH, use XLOOKUP or XMATCH and use the *magic* 2 of specifying match mode as 2 to enable wildcard mode.

The 2 in the title, which is the second in the series, actually also refers to the 2 for the match mode specification method in this magic. (It also happens to refer to the 2 for the announcement of Frieren season 2, lol.)

Both are hack tips that you can easily use if you keep them in the back of your mind.

If there is such a loophole for matching, I wish they would just use the same matching as Excel from the start... but since it can't be helped, for now, let's get by using this workaround (magic) for the time being!

The topic for the next note is undecided.

It's an Excel topic, but I wanted to cover the new functions GROUPBY and PIVOTBY because they are just too powerful, but they suddenly became unavailable...

When will they come back!!