0

I have 2 columns of values. I want to create a formula that looks at Column A and finds what Column B text values are within in the Column A items. Then I want what matches to list in column C. I've posted an example below.

Column A Apples List Column B Categories Column C Results
Gala Apples Gala Gala
Pink Lady and Honeycrisp Apples Fuji Honeycrisp
Fuji and Honeycrisp Apples Honeycrisp Fuji, Honeycrisp
Gala and Honeycrisp Apples Golden Delicious Gala, Honeycrisp
Green Apples
Golden Delicious and Fuji Fuji, Golden Delicious

I've tried the following but this does not work and shows no matches.

=TEXTJOIN(", ",TRUE,IF(A2=$B$2:$B$4,$B$2:$B$4,"no matches"))
3
  • This is challenging because you haven't really explained your logic. Why even bother with Column B? =SUBSTITUTE(SUBSTITUTE(A1," Apples","")," and",",")
    – Blindspots
    Jan 31, 2023 at 1:09
  • Hi @BlindSpots apologies, I added one more row to help illustrate; this one has a value that doesn't have a corresponding category that needs to be flagged. Jan 31, 2023 at 1:26
  • Np. Still don't see it flagged in your sample table.
    – Blindspots
    Jan 31, 2023 at 1:44

1 Answer 1

0

Try:

=INDEX(BYROW(IF(COUNTIF(B2:B,SPLIT(A2:A," ")),SPLIT(A2:A," "),),LAMBDA(ζ,TEXTJOIN(", ",1,ζ))))

Update

=LAMBDA(list,categories,MAP(list,LAMBDA(_list,
TEXTJOIN(", ",1,IFNA(FILTER(categories,REGEXMATCH(_list,"(?i)\b"&categories&"\b")))))))
(A2:INDEX(A2:A,MAX(ROW(A2:A)*(A2:A<>""))),B2:INDEX(B2:B,MAX(ROW(A2:A)*(A2:A<>""))))
3
  • Hi @ztiaa, this works great except it's running into issues with Column B values that have a space. I just added another row to share an example (e.g. "Golden Delicious"). In other words, if a category has two words it doesn't add it to Column C even if Column A has that value . I hope this makes sense. What would I need to adjust? Can you explain your formula to me? Jan 31, 2023 at 2:04
  • See updated formula.
    – user290033
    Jan 31, 2023 at 2:23
  • Please add a brief description of the features used and explanation of why this answer is correct. (note: this was flagged as low quality and has one delete vote) Jan 31, 2023 at 2:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.