Extracting 2 or 3 words before a match found

[satish78]

Hi Friends,

I tried to extract first 2 words using this =TRIM(RIGHT(A1, FIND(B1",SUBSTITUTE(A1, " ", B1,2)&B1)))
below is the text in ColumnA

A1=Refresh staging database use data pump
B1=Data
C1= =TRIM(RIGHT(A1, FIND(B1",SUBSTITUTE(A1, " ", B1,2)&B1)))
some how did not work

Can anyone let me know where I been wrong?

 

[satish78]

in B1:B10

develop
program
engineer
master
architect
patch
designer
data
pump
manager


I know that "data" and "pump" words are present in same cell. But, here I want the formula to pick first come word "data".

 

[Aladin Akyurek]

Thanks.

Are we looking for the items of B1:B10 in A1:A10 and return the first item that we come across in A1:A10 or what?

 

[satish78]

yes. We are looking in A1:A10

 

[Aladin Akyurek]

yes. We are looking in A1:A10

Are we looking for the items of B1:B10 in A1:A10? If we do so, are we picking out the first B item that we come across in A1:A10?

 

[Rick Rothstein]

Are we looking for the items of B1:B10 in A1:A10? If we do so, are we picking out the first B item that we come across in A1:A10?

I think the OP is asking for the first word in range B1:B10 (looking down from cell B1 toward cell B10) that appears somewhere in cell A1... then the first word in range B1:B10 that appears somewhere in cell A2... and so on down the list of cells in Column A.

 

[Aladin Akyurek]

I think the OP is asking for the first word in range B1:B10 (looking down from cell B1 toward cell B10) that appears somewhere in cell A1... then the first word in range B1:B10 that appears somewhere in cell A2... and so on down the list of cells in Column A.

The best thing the OP could do I think is also to publish what A1:A10 houses along with the the expected results (not talk about formulas, etc.).