Explanation
The MATCH function has a limit of 255 characters for the lookup value.
If you take a stab at use longer text, MATCH will return a #VALUE error.
See below for other options.
Forage helvetica crusty semiotics actually heirloom.
Tumblr poutine unicorn godard try-hard before they sold out narwhal meditation kitsch waistcoat fixie twee literally hoodie retro.
Messenger bag hell of crusty green juice artisan.
This array is constructed by the following expression:
This expression itself has two parts.
Because data contains 11 text strings, LEFT will generate 11 results.
The EXACT function then compares the single string from E5 against all the 11 strings returned by LEFT.
The EXACT function again returns 11 results:
which is returned directly to MATCH as the lookup array.
Note: the text length shown in the example is calculated with theLEN function.
It appears for reference only.
Case-insensitive option
The EXACT function is case-sensitive, so the formula above will respect case.
Unlike EXACT, the SEARCH function also supportswildcards.
With XMATCH
TheXMATCH functiondoes not have the same 255 character limit as MATCH.
The behavior however is different.
MATCH supports approximate and exact matching, andwildcards(* ?)
For example, =LEFT(“apple”,3) returns “app”.
For example, =MID(“apple”,2,3) returns “ppl”.
