Looking for Formula To Find Multiple Matching Items

[marcflood]

I have a spreadsheet (see attached example) with four columns:

• Item Name
• SKU
• Related SKU
• Scents (several scents separated by commas)

I am trying to find a formula/function to put in the Related SKU column that would check the Scents column and if any one of the scents (separated by commas) of any other row matched any of scents in the current row it would add that SKU to the related SKUs column. In other words, I am not trying to match the entire cell but rather any word(s) separated by a comma in that cell. Their could be more than one match so I would like it to add as many SKUs that it finds a match for.

Is this possible? I am guessing that I could do it with a VLOOKUP function but I think that is limited to exact matches and can only return one match not several (although maybe I am wrong).

Any help would be greatly appreciated.

[JBeaucaire]

This will probably require VBA. Are you ok with that?

[marcflood]

This will probably require VBA. Are you ok with that?

JB, thank you for replying to my post. I do not know VBA. If that is what is required I would probably need to find someone to do it for me for a fee. For someone who know VBA do you think it would be time consuming to do? I could do this manually but I thought their might be a way to do it via a formula/function.

Thank you again.

[JBeaucaire]

This macro will spit all the scents in a row by the "," delimiter, treating "and" and "&" as another delimiter. Then it will search for each string in the other rows in column D. It makes a list of all the SKUs it finds, then sorts them.

One thing to be aware of, the macro will match shorter strings to longer ones... for instance Bergamot in SKU1 will match to Fresh Bergamot in SKU3, but not the other way around.

Click the button to run the macro...

Please Login or Register  to view this content.