Index function to search a string and retain multiple words with reference to another table

[jajatidev]

Hi,
I'm in need of assistance with the Index Function to search for words within a string with a reference from another sheet. match the values and retain them.

Table 1 contains the following words;

RRDC

RRMF

RROM

SCAL

XBOP

EOL2

INCO

Table 2 contains the word strings;

Order Status

BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP

BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP

BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP

BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP

BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP

BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP

BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP

BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL

BLCA BLKD C2AP COM-GLA EOL2 NoMP REL SCAL

BLCA BLKD C2AP COM-GLA EOL2 NoMP REL SCAL

BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL

BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL

BLCA C2AP EOL2 NoMP REL SCAL

BLKD C2AP COM-GLA EOL2 NoMP REL SCAL

BLCA C2AP CFSL COM-GLA NoMP REL XBOP

BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP

C2AP COM-GLA INC-VBEP-LDDAT INC-VBEP-MBDAT INC-VBEP-TDDAT INC-VBEP-WADAT INCO NoMP REL

BLCA BLKD C2AP COM-GLA NoMP REL SCAL

BLCA BLKD C2AP COM-GLA NoMP REL RRDC SCAL

BLCA C2AP CFSL COM-GLA NoMP REL XBOP

BLCA BLKD C2AP COM-GLA NoMP PRSE REL RRDC

BLCA BLKD C2AP COM-GLA EOL1 NoMP REL RRDC

BLCA C2AP COM-GLA EOL1 NoMP REL RRDC

C2AP INC-VBAP-CEPOK INCO MDID NoMP REL RROM SCAL

BLCA C2AP EOL2 INC-VBAP-CEPOK INCO MDID NoMP REL RROM SCAL

BLCA BLKD C2AP COM-GLA NoMP REL RRDC SCAL

INC-VBAP-CEPOK INC-VBAP-NETWR INCO NoMP PRSE REL

BLKD C2AP COM-GLA INC-VBAP-ROUTE INCO NoMP REL

BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL

The formula [=IFERROR(INDEX(CodeList,MATCH(1,COUNTIF(A9,"*"&CodeList&"*"),0)),"")] I'm using is not giving the desired result.

Therefore, I need assistance with the formula where it should search the string and retain all the words recognized from Table 1.

I am looking forward to hearing from you.

Thanks,
Dev

 

[Bosquedeguate]

Your problem description needs more detail. What do you want the =IF(INDEX(.... formula to do? Please explain what is the "desired result".
Also, how is CodeList defined?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jajatidev]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi, I have updated the requested details.

 

[jajatidev]

Your problem description needs more detail. What do you want the =IF(INDEX(.... formula to do? Please explain what is the "desired result".
Also, how is CodeList defined?

I need an INDEX function that looks into the cell string in Table 2 and retains the Words captured in Table 1 (Contents of Table 1 is the CodeList)

Example 1;
From the string
C2AP COM-GLA INC-VBEP-LDDAT INC-VBEP-MBDAT INC-VBEP-TDDAT INC-VBEP-WADAT INCO NoMP REL

The formula should search this string by cross-referencing Table 1 to retain INCO


Example 2;
From the string
BLCA C2AP EOL2 INC-VBAP-CEPOK INCO MDID NoMP REL RROM SCAL

The formula should search and retain EOL2, INCO, RROM, SCAL

 

[Fluff]

Hi, I have updated the requested details.

Thanks for that.
Is this what you want

Fluff.xlsm
	A	B	C	D
1			Order Status
2	RRDC		BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	SCAL XBOP
3	RRMF		BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	SCAL XBOP
4	RROM		BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	SCAL XBOP
5	SCAL		BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	SCAL XBOP
6	XBOP		BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP	XBOP
7	EOL2		BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP	XBOP
8	INCO		BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP	XBOP
9			BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL	SCAL EOL2
10			BLCA BLKD C2AP COM-GLA EOL2 NoMP REL SCAL	SCAL EOL2
11			BLCA BLKD C2AP COM-GLA EOL2 NoMP REL SCAL	SCAL EOL2
12			BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL	SCAL EOL2
13			BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL	SCAL EOL2
14			BLCA C2AP EOL2 NoMP REL SCAL	SCAL EOL2
15			BLKD C2AP COM-GLA EOL2 NoMP REL SCAL	SCAL EOL2
16			BLCA C2AP CFSL COM-GLA NoMP REL XBOP	XBOP
17			BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP	XBOP
18			C2AP COM-GLA INC-VBEP-LDDAT INC-VBEP-MBDAT INC-VBEP-TDDAT INC-VBEP-WADAT INCO NoMP REL	INCO
19			BLCA BLKD C2AP COM-GLA NoMP REL SCAL	SCAL
20			BLCA BLKD C2AP COM-GLA NoMP REL RRDC SCAL	RRDC SCAL
21			BLCA C2AP CFSL COM-GLA NoMP REL XBOP	XBOP
22			BLCA BLKD C2AP COM-GLA NoMP PRSE REL RRDC	RRDC
23			BLCA BLKD C2AP COM-GLA EOL1 NoMP REL RRDC	RRDC
24			BLCA C2AP COM-GLA EOL1 NoMP REL RRDC	RRDC
25			C2AP INC-VBAP-CEPOK INCO MDID NoMP REL RROM SCAL	RROM SCAL INCO
26			BLCA C2AP EOL2 INC-VBAP-CEPOK INCO MDID NoMP REL RROM SCAL	RROM SCAL EOL2 INCO
27			BLCA BLKD C2AP COM-GLA NoMP REL RRDC SCAL	RRDC SCAL
28			INC-VBAP-CEPOK INC-VBAP-NETWR INCO NoMP PRSE REL	INCO
29			BLKD C2AP COM-GLA INC-VBAP-ROUTE INCO NoMP REL	INCO
30			BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL	SCAL EOL2
Sheet6

Cell Formulas
Range	Formula
D2:D30	D2	=LET(x,TEXTSPLIT(C2," "),TEXTJOIN(" ",,XLOOKUP($A$2:$A$8,x,x,"")))

 

[jajatidev]

Thanks for that.
Is this what you want

Fluff.xlsm
	A	B	C	D
1			Order Status
2	RRDC		BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	SCAL XBOP
3	RRMF		BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	SCAL XBOP
4	RROM		BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	SCAL XBOP
5	SCAL		BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	SCAL XBOP
6	XBOP		BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP	XBOP
7	EOL2		BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP	XBOP
8	INCO		BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP	XBOP
9			BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL	SCAL EOL2
10			BLCA BLKD C2AP COM-GLA EOL2 NoMP REL SCAL	SCAL EOL2
11			BLCA BLKD C2AP COM-GLA EOL2 NoMP REL SCAL	SCAL EOL2
12			BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL	SCAL EOL2
13			BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL	SCAL EOL2
14			BLCA C2AP EOL2 NoMP REL SCAL	SCAL EOL2
15			BLKD C2AP COM-GLA EOL2 NoMP REL SCAL	SCAL EOL2
16			BLCA C2AP CFSL COM-GLA NoMP REL XBOP	XBOP
17			BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP	XBOP
18			C2AP COM-GLA INC-VBEP-LDDAT INC-VBEP-MBDAT INC-VBEP-TDDAT INC-VBEP-WADAT INCO NoMP REL	INCO
19			BLCA BLKD C2AP COM-GLA NoMP REL SCAL	SCAL
20			BLCA BLKD C2AP COM-GLA NoMP REL RRDC SCAL	RRDC SCAL
21			BLCA C2AP CFSL COM-GLA NoMP REL XBOP	XBOP
22			BLCA BLKD C2AP COM-GLA NoMP PRSE REL RRDC	RRDC
23			BLCA BLKD C2AP COM-GLA EOL1 NoMP REL RRDC	RRDC
24			BLCA C2AP COM-GLA EOL1 NoMP REL RRDC	RRDC
25			C2AP INC-VBAP-CEPOK INCO MDID NoMP REL RROM SCAL	RROM SCAL INCO
26			BLCA C2AP EOL2 INC-VBAP-CEPOK INCO MDID NoMP REL RROM SCAL	RROM SCAL EOL2 INCO
27			BLCA BLKD C2AP COM-GLA NoMP REL RRDC SCAL	RRDC SCAL
28			INC-VBAP-CEPOK INC-VBAP-NETWR INCO NoMP PRSE REL	INCO
29			BLKD C2AP COM-GLA INC-VBAP-ROUTE INCO NoMP REL	INCO
30			BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL	SCAL EOL2
Sheet6

Cell Formulas
Range	Formula
D2:D30	D2	=LET(x,TEXTSPLIT(C2," "),TEXTJOIN(" ",,XLOOKUP($A$2:$A$8,x,x,"")))

Thanks, Fluff.
Is it possible to have the output in order to its appearance (Left to Right) in the string to provide clarity?

 

[Fluff]

Not sure what you're saying, can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jajatidev]

Not sure what you're saying, can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Row - 15 from the solution.

String:
BLKD C2AP COM-GLA EOL2 NoMP REL SCAL

Output
SCAL EOL2

Is it possible to have the output in the order the two words appear in the string EOL2 SCAL? It's essential to maintain the sequence for MIS.

 

[Fluff]

Ok, how about

=LET(x,TEXTSPLIT(C2," "),TEXTJOIN(" ",,XLOOKUP(x,$A$2:$A$8,$A$2:$A$8,"")))