Partial VLookup with Multiple Matches

Dear Excel Experts,

I am new to this forum and am very happy to have discovered it.
This is what i am trying to do and tearing my hair. Hopefully, someone can give me a solution

I have one column (A) with a sentence of multiple words.
I have another table with two columns (C & D) with sentences containing multiple words.

I need to match ANY of the words in column A with ANY of the words in Column C and Return the values in Column D.
Another hing. If there are more than one matches, then it needs to return all the values comma separated

Is this possible?

Apologies Glenn. And i really appreciate your offer to help. Here is the excel file. I have removed my formulae and replaced them with the expected results (manually).

1. I would like to avoid VBA if possible
2. Helper column is absolutely OK
3. Maximum would be 10

I understand. Ignore sack hook please. I will work around this limitation

Ok. Let's try the VBA route

Or with the aid of several helper columns (probably don't need them all and almost definitely can be tidied up).

1. Separate the words into columns
2. Use this array (CSE) formula to find every occurrence of each word

Please Login or Register  to view this content.

3 then finally a textjoin.

see attached.

Hello John .. Need your help again. I tried modifying the code to make this happen and it did not work. I was trying to move B3:C to a different sheet. Basically, keep the reference (B, C) and the lookup & result (G, H) need to be on different sheets. is that possible?

Thanks John.

For john.png

Thank you so much John !!

John .. hi
I get this error whenever i enter a word that is NOT present under lookup (Sheet2, Column G)

Error.png

Everything is OK as long as the words entered under the lookup column (in sheet2) are present in Sheet1

Thanks Again John

Hi John .. One more issue. Hopefully, it is the last . I tried tinkering with the code, but i seem to be breaking things than fixing it. Anyways, right now, it is matching words that are part of other words. It should only match whole words. This picture should explain


Full word matching.png

Understood Here is the excel file with notes. On sheet2. Appreciate it

I had this after editing John's code slightly (hope you don't mind John).

Please Login or Register  to view this content.

John, I did a similar approach to you at first (adding " " to start and end actually, what you did is neater) but then realised it misses some matches from the example, e.g. it does not match "someone" with "someone's", because it is looking for "someone ".

That's perfect John. Thnks again. I am just curious about one thing. If i add a space after the word (lookup), then it picks up all the cells. The word "So" and "So " gives different results.
Thanks Nick and Menem for contributing.

regards

Oops, my code doesn't work at all. Haha. But, question for OP - do you want to match "someone" with "someone's"? This makes it very difficult to only match whole words.

John .. You are right. This gets tricky. Because, there are also words with just two letters like "it". So, "IT" is a match. But, it should not pick "WITH". Of course, "Someone" and "Someone's" should be the same. But, the code can get complicated - i think.

Originally Posted by guna_shekar

That's perfect John. Thnks again. I am just curious about one thing. If i add a space after the word (lookup), then it picks up all the cells. The word "So" and "So " gives different results.
Thanks Nick and Menem for contributing.

regards

The problem here is that a Split of "i " returns "i" and "", the latter string then gets matched with everything. I think you could get round it by replacing:

Split(x, " ")

with

Split(Trim(x), " ")

Originally Posted by JohnTopley

'guna: if "someone" and "someone's" are to be treated as separate words then ALL matches are exact matches.

That is correct John.

Originally Posted by nick.williams

The problem here is that a Split of "i " returns "i" and "", the latter string then gets matched with everything. I think you could get round it by replacing:

Split(x, " ")

with

Split(Trim(x), " ")

Yes. This works. Perfect !!

Ok, so we have something that basically works if there are no special characters. To deal with other cases it depends how you want them to be treated? There's ways to do it but you probably need to deal with all cases separately. The trouble is that I guess it's hard to think of every possible case in advance.

e.g. full stops, do you want to ignore them regardless, or only if they are the last character of the string, or only if they are at the end of a word (not sure why this would happen but e.g. match (test" with "test." but not "te.st"

Similarly with apostrophes, if you just want to ignore everything after the apostrophe that's fine, but would you potentially have them in your lookup words as well?

This is all do-able but needs allowing for and it's hard to know for sure what your desired outcome is.

NB my code above works nicely (apart from special characters) if you replace:

If InStr(1, UCase(u(k)), UCase(t(j))) Then

with

If UCase(u(k)) = UCase(t(j)) Then