I am looking for a alternative formula for this one:
index(Categories!$B$2:$B,match(1,search("*"&Categories!$B$2:$B&"*",$D2),0)))
This is a partial lookup formula where categories is in a range, though it is working, it is kinda slow, I am looking for some alternative that will not slow down my spreadsheet
update:
=ARRAYFORMULA(IFERROR(VLOOKUP(REGEXEXTRACT(
TRIM(REGEXEXTRACT(LOWER(REGEXREPLACE(Sheet2!A2:A, "\(|\)|\*|\.|""|\-|\:", )),
"[a-z &@0-9]*")), TEXTJOIN("|", 1,
TRIM(REGEXEXTRACT(LOWER(REGEXREPLACE(Sheet1!A2:A, "\(|\)|\*|\.|""|\-|\:", )),
"[a-z &@0-9]*")))), {
TRIM(REGEXEXTRACT(LOWER(REGEXREPLACE(Sheet1!A2:A, "\(|\)|\*|\.|""|\-|\:", )),
"[a-z &@0-9]*")), Sheet1!B2:D}, {2, 3, 4}, 0)))