# Match 2 columns (a,b) not exact and return cell in b that contains cell in column b which

1. ## Match 2 columns (a,b) not exact and return cell in b that contains cell in column b which

ok been stuck all morning, time to ask for help...

(columnB is empty = insert formula in B2 and copy down)

columnA columnB columnC
catseat -------- dog
birdsfly --------- cat
haterats

If columnA A2 contains any element from columnC (not exact just includes the phrase = c4 cat is included in a2 so b2 would include the word 'cat') = then the data matching in columnC will be returned to columnB.

Here's where i'm at with the formula:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(Table1[Column3],A2,1)))),"FOUND","")
This returns the following in columnB which is correctly identifying the 'found' rows:
B2 = FOUND
B3 = FOUND
B4 =
B5 = FOUND

BUT would like to show the following in columnB (desired answer to question - how to?)
B2 = CAT
B3 = DOG
B4 =
B5 = RAT

Any advice would be greatly appreciated as been stuck on this all morning and could really use a hand...

2. ## Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

3. ## Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

Book1.xlsx

Thanks for the help! Attached a better example in the excel file.

4. ## Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

Any ideas? friendly bump...

5. ## Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

hi..

your requirement to show all match value,
make it more complicated..

sample : "dog rat "

anyway,

regards,

6. ## Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

Yes it worked! Thank you!

One more quick question:
Have 1000+ entries in column3 which makes it kind of hard to copy/paste the required formula to analyze all the lines. At the moment it looks like it will only work for 4 entries in column3... if I was to enter 1000 lines in column 3 then the formula would also be around 1000 lines = too long

maybe too much work? - but is there anyway to condense this so we could insert 1000 entries in column3 and have the formula still function?

alternatively maybe something that could flag as 'Double' if it matches more than one phrase in column3

Anyways I really appreciate your initial answer to my question... really great stuff!
Have a nice day!

7. ## Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

sorry,

just show how many match value,
and show the 1st match i can do.

regards,

8. ## Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

That works perfectly!

Thanks so much for your time...
Have a nice day!

Problem solved.

9. ## Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

in your after example you just need
=iferror(LOOKUP(10^99,SEARCH(\$C\$18:\$C\$21,A18),\$C\$18:\$C\$21),"")

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1