CODEWITHSUNDEEP
arraysregexgoogle-sheetsvlookuptextjoin
Mr.YJY
Mr.YJY

Reputation: 67

Alternative formula for partial lookup within a list

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: https://docs.google.com/spreadsheets/d/1yBG10G1InFk3tNyg8TpzI-HkAyuMBAsM6amVak2-tpI/edit?usp=sharing

Upvotes: 1

Views: 77

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFERROR(VLOOKUP(REGEXEXTRACT(TRIM(REGEXEXTRACT(LOWER(A2:A), "[a-z ]*")), 
 TEXTJOIN("|", 1, TRIM(REGEXEXTRACT(LOWER(Sheet1!A2:A), "[a-z ]*")))), 
 {TRIM(REGEXEXTRACT(LOWER(Sheet1!A2:A), "[a-z ]*")), Sheet1!B2:D}, {2, 3, 4}, 0)))

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

Upvotes: 2

Related Questions