Get the PN from one cell with information if this PN appear in the list in another tab

[vinzent]

Hello Team
Can you help me with this please?
I don't know how to get this information in easy mode, due I have a lot of rows and at this moments I am doing manually.
I have one list with a lot of PN in tab PN list and I have in Sheet1 tab one column with a lot of information inside the cell.
I want to get only the PN for each cell based on the PN list and put in next cell to know what is the PN affected in each cell.
I uploaded one example how I am doing manually at this moment, but basically is this:

Download example here

Information	Extract this text if some number on PN List tab
32365 656 69898 North A1 Information closed 6/5	A1	<---	This text is in the list in PN list tab
65 .56 -56 North C3 Information closed 5/8	C3
9*656+56 North FJKH548 Information closed 6/25	FJKH548
455/658.25North FJKH652 Information closed 5/9	FJKH652
6598* 78/521 North FJKH65687 Information closed 9/1	FJKH65687
456 djh North FJKH6569 Information closed 6/4	FJKH6569
sjhkd dkjdk dds North FJKH6598 Information closed 2/8	FJKH6598
shgdg 65 s North FJKH6600 Information closed 3/4	FJKH6600
skjjd 235 sdffe South B2 Information closed 9/8	B2
skjdi 56 d South FJKH656 Information closed 6/15	FJKH656
s 23d 356d 54d South FJKH6568 Information closed 7/11	FJKH6568
3232 e$ South FJKH6583 Information closed 2/15	FJKH6583
*8 South FJKH6587 Information closed 8/12	FJKH6587
78 12s South FJKH6599 Information closed 6/5	FJKH6599
56 2 568 559 58 South G456 Information closed 1/2	G456

 

[Peter_SSs]

See if this is what you want.

vinzent_1.xlsx
	A
1	A1
2	B2
3	C3
4	G456
5	FJKH6598
6	FJKH6599
7	FJKH6600
8	FJKH656
9	FJKH652
10	FJKH6587
11	FJKH6569
12	FJKH6568
13	FJKH65687
14	FJKH6583
15	FJKH548
PN list

vinzent_1.xlsx
	D	E
1	Information	Result
2	32365 656 69898 North A1 Information closed 6/5	A1
3	65 .56 -56 North C3 Information closed 5/8	C3
4	9*656+56 North FJKH548 Information closed 6/25	FJKH548
5	455/658.25North FJKH652 Information closed 5/9	FJKH652
6	6598* 78/521 North FJKH65687 Information closed 9/1	FJKH65687
7	456 djh North FJKH6569 Information closed 6/4	FJKH6569
8	sjhkd dkjdk dds North FJKH6598 Information closed 2/8	FJKH6598
9	shgdg 65 s North FJKH6600 Information closed 3/4	FJKH6600
10	skjjd 235 sdffe South B2 Information closed 9/8	B2
11	skjdi 56 d South FJKH656 Information closed 6/15	FJKH656
12	s 23d 356d 54d South FJKH6568 Information closed 7/11	FJKH6568
13	3232 e$ South FJKH6583 Information closed 2/15	FJKH6583
14	*8 South FJKH6587 Information closed 8/12	FJKH6587
15	78 12s South FJKH6599 Information closed 6/5	FJKH6599
16	56 2 568 559 58 South G456 Information closed 1/2	G456
Sheet1

Cell Formulas
Range	Formula
E2:E16	E2	=LOOKUP(9.99E+307,SEARCH(" "&'PN list'!A$1:A$15&" "," "&A2&" "),'PN list'!A$1:A$15)

 

[vinzent]

Thank You, this is I want for my file!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.