Google-sheets – Match and lookup function on Google Sheets match different Japanese Kanji

google sheetslocalizationworksheet-function

Checkout this google sheet:

Dafuq   箇   策     Match       1
                   Lookup   Dafuq
                   Exact    FALSE

Despite the fact that those two kanji characters are completely different, the MATCH() and LOOKUP() function treat them as though they are the same. The EXACT() function properly returns FALSE though.

Why are MATCH() and LOOKUP() treating them as the same even though they're different and can I do anything to make them treat them differently as expected?

Best Answer

MATCH has three search types:

  • 1, the default, causes MATCH to assume that the range is sorted in ascending order and return the largest value less than or equal to search_key

To make that MATCH treat the two Kankjis as different characters use

  • 0 indicates exact match, and is required in situations where range is not sorted.

Regarding Lookup

The LOOKUP function will only work properly if data in search_range or search_result_array is sorted. Use VLOOKUP, HLOOKUP, or other related functions if data is not sorted.

If search_key is not found, the item used in the lookup will be the value that’s immediately smaller in the range provided. For example, if the data set contains the numbers 1, 3, 5 and search_key is 2, then 1 will be used for the lookup.

Related Topic