Match string between cells in two tables and return a value based on condition

[shavar]

Hello Everyone. I've been using this forum for a while which has helped me resolve many issues and develop some decent reports. Thank you for that.

I have a situation I'd like to see if I can resolve programmatically (forumulas / functions) versus the built-in sorting, filtering, text-to-column tools. I'll have to do this each month and it's just one part of an overall monthly task.

What I need to be able to do is locate the value from a cell in Table2 and tag it with a Y or N. Then based on wether it's Y or N, concatenate the values from a column in Table1. The sheet Table2_result is what I'd like to end up with. The cells in Column2 of Table1 contain multiple values separated by a comma and space.

I was able to break this up into multiple tables via text-to-column and sorting etc to get to the end-result but it's a lot of work which I could like to automate as much as possible. If I can't automate everything I'd like to be to at least get some of done via formulas. I tried various functions and can only partially get what I want.

I'm using Excel 2010. The attached has some brief notes explaining what I need with examples. Any input would be greatly appreciated. Even if it's just suggestions on what functions might work for this I'll try to figure out how to make it work. Or other techniques.

excel forum problem sample1.xlsx

[AZ-XL]

hi. Tho both of formulas are array. Thus hit Ctrl+Shift+enter instead of just an enter
Volumn2 formula
=IF(SUM(IF(ISERROR(SEARCH(A2,Table1[Column2])),0,1)),"Y","N")

Column3 formula. Concatenates only up to 3 matches
=IF(Table2[[#This Row],[Column2]]="Y",CONCATENATE(IFERROR(INDEX(Table1[Column1],SMALL(IF(ISERROR(SEARCH(A2,Table1[Column2])),"",ROW(Table1[Column2])-1),1)),""),", ",
IFERROR(INDEX(Table1[Column1],SMALL(IF(ISERROR(SEARCH(A2,Table1[Column2])),"",ROW(Table1[Column2])-1),2)),""),", ",
IFERROR(INDEX(Table1[Column1],SMALL(IF(ISERROR(SEARCH(A2,Table1[Column2])),"",ROW(Table1[Column2])-1),3)),"")),"No Data")

[shavar]

@AZ-XL

These are working. Nice thing about this is seeing how these functions can be used. I can build on this to go more than three deep if needed which may be something I'll need down the road. Thanks for your time and effort.

[AZ-XL]

You are welcome. Thank you for feedback. Also you may click star button (add reputation) below my avatar pic.

Good Luck