wrote...
I was doing wildcard matches [MATCH("*"&ref&"*",range,0)]and
happened to notice that it skipped an obvious correct match.
After attacking that for too long a time, I found that
MATCH() won't handle strings longer than 255 characters.
....

OK. Since really just checking if the value of ref appears in
any cell in range, try the array formula

=MATCH(TRUE,SUBSTITUTE(range,ref,"")<range,0)

which does work for arbitrarily long strings.