# Given If any words in column A match any string of text in column B, return column B

1. ## Given If any words in column A match any string of text in column B, return column B

I need help with this issue, I have two columns, A & B. Colum A has strings of text like "cat apple tree", column B has one or two strings of text like " cat" or "cat tree". I am having trouble creating a formula where it checks, lets say A1 where the value of A1 is "cat apple tree" and compares to the entirety of column B. After it compares the cell from column A to the entirety of column B where lets say B12 = cat, then it will return the cell value of column B where the text matched. Column A has multiple words so if any match any of column B, I need the cell value of B to be returned. I know that sounds confusing work sheet is attached.Practice.xlsx

2. ## Re: Given If any words in column A match any string of text in column B, return column B

Welcome to the forum.

Try this:

=IFERROR(LOOKUP(99999,SEARCH(B\$1:B\$4,A1),B\$1:B\$4),"")

3. ## Re: Given If any words in column A match any string of text in column B, return column B

Unfortunately this did not work. it is giving me 0 value for a string that should match to a value

4. ## Re: Given If any words in column A match any string of text in column B, return column B

Please try in C1 and copy down (confirm the formula with CTRL+SHIFT+ENTER):
Formula:

5. ## Re: Given If any words in column A match any string of text in column B, return column B

It does work.

6. ## Re: Given If any words in column A match any string of text in column B, return column B

my 3 cents with Power Query

 Column1 Column1 Custom Custom.1 cat tree apple house earth venus mars moon venus dog blue kid cat Post a small Excel sheet not picture showing realistic & representative sample data picture & data plant tree house fly house tree plant house red orange venus bird owl fly fly yellow fish mom and cat tree apple cat crazy dog jumping & barking data List.Generate() and Looping in PowerQuery and bird owl fly picture crazy dog jumping & barking & screen mouse & dog blue kid table chair lamp yellow fish mom earth venus mars moon red orange List.Generate() and Looping in PowerQuery screen mouse Post a small Excel sheet not a picture showing realistic & representative sample data table chair lamp

7. ## Re: Given If any words in column A match any string of text in column B, return column B

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

8. ## Re: Given If any words in column A match any string of text in column B, return column B

Thanks for the rep and for confirming that the formula I suggested does work.

9. ## Re: Given If any words in column A match any string of text in column B, return column B

You are welcome, thanks for the rep with feedback .

There are currently 1 users browsing this thread. (0 members and 1 guests)