Find 6 digit PO using Xlookup in a string of POs in a cell.

[MrIDontKnow]

Trying to use xlookup to search a 6 digit PO# in column of cells that contain multiple POs separated by commas "," or slashes "/" to bring back an invoice #. Some of the POs may have dashes "-", example 4023454-4. Any suggestions?

INV#	PO#'S
001 - EMI-10046-22	426451
EI-14887/22	429273
20221118
D082922-EXT-A	419913, 426453
D090822-EXT-A
6000088306B	427938
D101022-EXT-A	425895
W221219E-S
EMI-12136-22	430746
EI-14892-22	430493
EMI-12136-22	430746
D010623-EXT-A	430749, 431899
14897-23	432276
14897-23	432276
6000089566	431018, 429859
W230213E-S
W230301E-S	417063-10-1
W230327E-S
W230508E-S
ET-14917-23	437048
W230612E-S	402345-4-4 , 410717-5-1, 413067-4-1 , 416013-5-1, 416884-15-4 , 416886-8-2, 417158-11-2, 417381-6-1 , 423754-4-1, 423818-2-2, 423943-7-1 , 424002-5-1 , 424002-5-4 , 424051-11-3, 424051-11-7 , 424051-11-8, 424525-6-1 , 424525-6-2, 424642-2-3 , 424827-3-1 , 424959-3-1, 424986-5-1, 426625-3-1 , 428267-1-1, 428336-1-1, 428369-3-1 , 428529-1-1, 428533-6-5, 429956-4-1, 430860-1-2, 431933-1-1, 436667-1
3920656	438997,S230612,438954,439175
W230703E-S	416886-8-2, 419764-10-1, 423943-10-1, 424002-6-3, 424051-13-4 , 424051-13-5, 424051-13-6, 424525-8-2, 424640-6-3, 424642-2-2 , 424827-3-1 , 424986-5-1, 426141-8-1 , 428533-6-5 , 429956-5-1
W230710E-S	423279-6-1, 423943-10-1 , 424051-13-6, 424304-5-1, 424525-8-2, 424827-3-2, 425501-3-1, 426141-8-1, 428096-5-1, 428533-6-5
W230717E-S	399277-12-1, 399358-4-1, 404544-13-2, 404545-7-2, 409939-10-2, 412088-9-2, 420032-2-2, 423938-8-3, 423943-11-3, 424051-14-7, 424956-2-1, 426141-8-1, 428533-8-5

PO#	INV#
419913	=xlookup(E2,
426453
431018
429859
402345
410717
413067
416013
416884
416886
417158
417381
423754
423818
423943
424002
424002
424051
424051
424051
424525
424525
424642
424827
424959
424986
426625
428267
428336
428369
428529
428533
429956
430860
431933
436667

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you want?

24 03 01.xlsm
	A	B	C	D	E	F
1	INV#	PO#'S			PO#	INV#
2	001 - EMI-10046-22	426451			419913	D082922-EXT-A
3	EI-14887/22	429273			426453	D082922-EXT-A
4	20221118				431018	6000089566
5	D082922-EXT-A	419913, 426453			429859	6000089566
6	D090822-EXT-A				402345	W230612E-S
7	6000088306B	427938			410717	W230612E-S
8	D101022-EXT-A	425895			413067	W230612E-S
9					416013	W230612E-S
10	W221219E-S				416884	W230612E-S
11	EMI-12136-22	430746			416886	W230612E-S
12	EI-14892-22	430493			417158	W230612E-S
13	EMI-12136-22	430746			417381	W230612E-S
14	D010623-EXT-A	430749, 431899			423754	W230612E-S
15	14897-23	432276			423818	W230612E-S
16	14897-23	432276			423943	W230612E-S
17	6000089566	431018, 429859			424002	W230612E-S
18	W230213E-S				424002	W230612E-S
19	W230301E-S	417063-10-1			424051	W230612E-S
20	W230327E-S				424051	W230612E-S
21	W230508E-S				424051	W230612E-S
22	ET-14917-23	437048			424525	W230612E-S
23	W230612E-S	402345-4-4 , 410717-5-1, 413067-4-1 , 416013-5-1, 416884-15-4 , 416886-8-2, 417158-11-2, 417381-6-1 , 423754-4-1, 423818-2-2, 423943-7-1 , 424002-5-1 , 424002-5-4 , 424051-11-3, 424051-11-7 , 424051-11-8, 424525-6-1 , 424525-6-2, 424642-2-3 , 424827-3-1 , 424959-3-1, 424986-5-1, 426625-3-1 , 428267-1-1, 428336-1-1, 428369-3-1 , 428529-1-1, 428533-6-5, 429956-4-1, 430860-1-2, 431933-1-1, 436667-1			424525	W230612E-S
24	3920656	438997,S230612,438954,439175			424642	W230612E-S
25	W230703E-S	416886-8-2, 419764-10-1, 423943-10-1, 424002-6-3, 424051-13-4 , 424051-13-5, 424051-13-6, 424525-8-2, 424640-6-3, 424642-2-2 , 424827-3-1 , 424986-5-1, 426141-8-1 , 428533-6-5 , 429956-5-1			424827	W230612E-S
26	W230710E-S	423279-6-1, 423943-10-1 , 424051-13-6, 424304-5-1, 424525-8-2, 424827-3-2, 425501-3-1, 426141-8-1, 428096-5-1, 428533-6-5			424959	W230612E-S
27	W230717E-S	399277-12-1, 399358-4-1, 404544-13-2, 404545-7-2, 409939-10-2, 412088-9-2, 420032-2-2, 423938-8-3, 423943-11-3, 424051-14-7, 424956-2-1, 426141-8-1, 428533-8-5			424986	W230612E-S
28					426625	W230612E-S
29					428267	W230612E-S
30					428336	W230612E-S
31					428369	W230612E-S
32					428529	W230612E-S
33					428533	W230612E-S
34					429956	W230612E-S
35					430860	W230612E-S
36					431933	W230612E-S
37					436667	W230612E-S
Inv No

Cell Formulas
Range	Formula
F2:F37	F2	=XLOOKUP("*"&E2&"*",B$2:B$27,A$2:A$27,"",2)

 

[SunnyAlv]

Try this if 1 PO# have more than 1 INV#

=IFERROR(TEXTJOIN(", ",,FILTER($A$2:$A$5,IFERROR(NOT(1-SEARCH("*"&E2&"*",$B$2:$B$5)),FALSE))),"Not Found")

 

[Peter_SSs]

Try this if 1 PO# have more than 1 INV#

=IFERROR(TEXTJOIN(", ",,FILTER($A$2:$A$5,IFERROR(NOT(1-SEARCH("*"&E2&"*",$B$2:$B$5)),FALSE))),"Not Found")

By my reading that is not what was asked for ..

bring back an invoice #

.. but if it is what is wanted there is no need for wildcard characters or any IFERROR functions. For your sample data this would do the same thing.

=TEXTJOIN(", ",,FILTER(A$2:A$5,ISNUMBER(FIND(E2,B$2:B$5)),"Not Found"))

 

[MrIDontKnow]

Thanks, I'll try all three. I was also given a new task to find multiple invoices. I'll let you all know. thanks again.

 

[Peter_SSs]

I was also given a new task to find multiple invoices.

If you don't want those multiple invoices listed in a single cell like the formulas in posts 5 & 6 do then you could consider something like this.

24 03 01.xlsm
	B	C	D	E		G	H	I	J
1	PO#'S			PO#
2	426451			419913		D082922-EXT-A
3	429273			426453		D082922-EXT-A
4				431018		6000089566
5	419913, 426453			429859		6000089566
6				402345		W230612E-S
7	427938			410717		W230612E-S
8	425895			413067		W230612E-S
9				416013		W230612E-S
10				416884		W230612E-S
11	430746			416886		W230612E-S	W230703E-S
12	430493			417158		W230612E-S
13	430746			417381		W230612E-S
14	430749, 431899			423754		W230612E-S
15	432276			423818		W230612E-S
16	432276			423943		W230612E-S	W230703E-S	W230710E-S	W230717E-S
17	431018, 429859			424002		W230612E-S	W230703E-S
18				424002		W230612E-S	W230703E-S
19	417063-10-1			424051		W230612E-S	W230703E-S	W230710E-S	W230717E-S
20				424051		W230612E-S	W230703E-S	W230710E-S	W230717E-S
21				424051		W230612E-S	W230703E-S	W230710E-S	W230717E-S
22	437048			424525		W230612E-S	W230703E-S	W230710E-S
23	402345-4-4 , 410717-5-1, 413067-4-1 , 416013-5-1, 416884-15-4 , 416886-8-2, 417158-11-2, 417381-6-1 , 423754-4-1, 423818-2-2, 423943-7-1 , 424002-5-1 , 424002-5-4 , 424051-11-3, 424051-11-7 , 424051-11-8, 424525-6-1 , 424525-6-2, 424642-2-3 , 424827-3-1 , 424959-3-1, 424986-5-1, 426625-3-1 , 428267-1-1, 428336-1-1, 428369-3-1 , 428529-1-1, 428533-6-5, 429956-4-1, 430860-1-2, 431933-1-1, 436667-1			424525		W230612E-S	W230703E-S	W230710E-S
24	438997,S230612,438954,439175			424642		W230612E-S	W230703E-S
25	416886-8-2, 419764-10-1, 423943-10-1, 424002-6-3, 424051-13-4 , 424051-13-5, 424051-13-6, 424525-8-2, 424640-6-3, 424642-2-2 , 424827-3-1 , 424986-5-1, 426141-8-1 , 428533-6-5 , 429956-5-1			424827		W230612E-S	W230703E-S	W230710E-S
26	423279-6-1, 423943-10-1 , 424051-13-6, 424304-5-1, 424525-8-2, 424827-3-2, 425501-3-1, 426141-8-1, 428096-5-1, 428533-6-5			424959		W230612E-S
27	399277-12-1, 399358-4-1, 404544-13-2, 404545-7-2, 409939-10-2, 412088-9-2, 420032-2-2, 423938-8-3, 423943-11-3, 424051-14-7, 424956-2-1, 426141-8-1, 428533-8-5			424986		W230612E-S	W230703E-S
28				426625		W230612E-S
29				428267		W230612E-S
30				428336		W230612E-S
31				428369		W230612E-S
32				428529		W230612E-S
33				428533		W230612E-S	W230703E-S	W230710E-S	W230717E-S
34				429956		W230612E-S	W230703E-S
35				430860		W230612E-S
36				431933		W230612E-S
37				436667		W230612E-S
Inv No

Cell Formulas
Range	Formula
G2:G10,G12:G15,G26,G28:G32,G35:G37,G34:H34,G33:J33,G27:H27,G25:I25,G24:H24,G22:I23,G19:J21,G17:H18,G16:J16,G11:H11	G2	=TRANSPOSE(FILTER(A$2:A$27,ISNUMBER(FIND(E2,B$2:B$27)),"Not Found"))