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"))
Improve this question
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 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.
    – Makayla Maxwell
    Jan 31 at 1:26
  • Np. Still don't see it flagged in your sample table.
    – Blindspots ♦
    Jan 31 at 1:44

1 Answer 1

Reset to default
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<>""))))
Improve this answer
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?
    – Makayla Maxwell
    Jan 31 at 2:04
  • See updated formula.
    – user290033
    Jan 31 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)
    – Rubén ♦
    Jan 31 at 2:49

Your Answer

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

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