Extract 1st,2nd occurrence of keywords in string, from named ranges, into separate columns

I have a column of data (MyData) from which I need to extract multiple keywords/phrases from each row. Please see attached example.

The keywords/phrases from MyDefinedListA may/may not exist in the string, but will only appear once if it does.
The keywords/phrases from MyDefinedListB & MyDefinedListC - may/may not exist in the string, and could appear more than once.

I'm trying to extract the Nth occurrence in a separate column for each word in the MyDefinedListB & MyDefinedListC - there should be no more than 4 occurrences in total.

The attached example is how I would like the result to look (I have limited it to 2 occurrences in this example but it can range up to 4 in either of the MyDefinedListB & MyDefinedListC named ranges):

I have tried to work this out but am having very little success.

Any help would be really appreciated!

I am unclear as to what you want as the result. You appear to have the words already, are you looking for TRUE or FALSE as to whether or not the word is in the text in col A?
if you are looking for that then this will return TRUE or FALSE as to if the word in columns B2 through I2 are in A2... =IFERROR(ISNUMBER(SEARCH(B2,$A2)),"")

If you simply want to find a specific word, then substitute the word like "Day Time" for B2 like this... =IFERROR(ISNUMBER(SEARCH("day time",$A2)),"") and it will either return TRUE or FALSE.

Ok, well then put this in cell E2 =IF(ISNUMBER(SEARCH(B2,$A2))=TRUE,B2,"") and drag toward the right. BUT, you are looking at three columns with B through D while dragging it to the right in 5 columns so it will run out of reference cells when it gets to col H as it will then be pointing at cell E2 which is the result of the first formula.

I'm missing what you are trying to do as to what is different than what I gave you. That array formula you have is simply looking at what is in B2 and if it is in A2 it is pulling it out. Lois is in C2 but not in A2 it is returning a blank when repointed at that table (col C). What I gave you is not an array and will look at what is in B2 and if it matches what is in A2 it will return that, otherwise it will be blank. It will repeat that for C2, if Lois isn't in A2 it will return blank.

Right now we (my formula and yours) are working off the same row. Are you saying that, if the formula goes to the right from E2 into F2 and looks at Lois in C2 but doesn't find it in A2, you want the formula to index down a row staying in C but going to C3 to see if Kevin is in A2 and then returning that?

Are you still using Excel 2010?

Hi, yes I am

That's a shame.

Is the layout of the data a must? There may be easier ways to do what you want if you have some flexibility.

I'm sorry, I got that wrong, I've actually got Excel 2016.

Ah, OK. Could you please update your user profile to reflect this?

Are you able to answer the question in my last post?

The problem is the number of columns - how far across the page do you anticipate going? It is not good to create something in this format that requires horizontal scrolling - the human eye copes better with vertical scrolling.

I was thinking that there might be a PowerQuery solution, buy I have run out of time for testing this morning - sorry.

The named ranges need to be coincident with the non-blank cells. I have adjusted them manually. It can be done automatically, if they're going to change a lot.

If there are NEVER going to be items in list B that are also on list A and similarly, none in C that are also in B, then this will work. If items in B can also occur in A, we'll need a blank column (which can be hidden) to make life easier.

E2, copied down:
=IFERROR(LOOKUP(1000,SEARCH(MyDefinedListA,A2),MyDefinedListA),"")

F2, copied across and down:
=IFERROR(LOOKUP(1000,SEARCH(MyDefinedListB,SUBSTITUTE($A2,E2,"",1)),MyDefinedListB),"")

G2:
=IFERROR(LOOKUP(1000,SEARCH(MyDefinedListC,SUBSTITUTE($A2,G2,"",1)),MyDefinedListC),"")

Note the result in H7. If you're OK with it, replace computer class in your keyword list with computer. It will then find computer and class as separate results. Would that work??

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.