Home > OS >  Alternative formula for partial lookup within a list
Alternative formula for partial lookup within a list

Time:08-25

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

sample sheet: enter image description here


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)))

enter image description here

  • Related