Help with a vlookup formula

[STIRRELL]

Hi everyone ,
I need help with a lookup. I have one spreadsheet that has the invoice number, and on a second spreadsheet they combined all the open invoice numbers in a single cell by PO, I need help with a vlookup that can search the column A for the single invoice number and return the PO associated in column B

So for example on Spreadsheet Invoices 7_19_23 in Column E I have a list of invoice numbers and need to return the PO number to column T ( PO #)


Spreadsheet 7_19_23
A
Invoice Number

A64347

PO_List
A

Associated Invoice(s)

A64297(780.00),A64253(87,987.70),A64226(103,547.54),A64227(2,913.60),A64100(4,414.00),A64303(106,137.23),A64346(66,367.04),A64354(393.16),A64153(46,089.76),A64323(172,845.17),A64304(5,260.00),A64173(115,383.96),A64347(84,167.08)

Return the PO number in Column B

Is there a vlookup Match or other combination to search the string in column A on the PO List tab and return the po number in column B

 

[JamesCanale]

I'm not sure I have a good grip on what you want here...

MrExcelPlayground18.xlsx
	A	B	C	D	E
1	Purchase Order	Invoices		lookup
2	A	A12345(1,000.00),A10000(2,000.00),A20000(15,000.00)		A1235	B
3	B	A1235(3,000.00),A11000(21,000.00),A22000(1,000.00)		C12345	D
4	C	A12(1,500.00),A10(2,500.00),A20(5,000.00)		A10000	A
5	D	B12345(1,000.00),B10000(2,000.00),B20000(15,000.00),C12345(1,000.00),C10000(2,000.00),C20000(15,000.00)		A20000	A
Sheet26

Cell Formulas
Range	Formula
E2:E5	E2	=FILTER($A$2:$A$5,NOT(ISERR(SEARCH(","&D2&"(",","&$B$2:$B$5))))

 

[Bosquedeguate]

Here is a small test sheet with Formulas you might be able to use:

GetLastRow.xlsm
	A	B	C	D
35	PO Number to find	A64307	0	NOT FOUND
36	NOT FOUND	XXXXXX
Sheet3

Cell Formulas
Range	Formula
C35	C35	=IFERROR(FIND(B35,A34),0)
D35	D35	=IF(C35>0,"FOUND","NOT FOUND")
A36	A36	=IF(C35>0,"FOUND","NOT FOUND")
B36	B36	=IF(A36="FOUND",MID(A34,C35,FIND("(",MID(A34,C35,30))-1),"XXXXXX")

Cell A34 contains the string of PO Numbers
B35 is the PO Number to find
C35 is > 0 if the PO Number is found in the list, or 0 if not found
D35 tests C35 and returns "FOUND" or "NOT FOUND"

If the PO Number is Found you can just copy the cell where you entered the PO number to search for, to your other sheet.

If you need to extract both the PO Number and the value in parenthesis a Bit more work is needed. Please clarify.

 

[Bosquedeguate]

Note: If you need the result of the searched to populate a cell other than where you have entered your formula you will need to write a VBA macro (Sub) to do the additional work.

E.g. - write to another sheet and or cell, extract the value in parenthesis after the PO Number and write it to a cell.

 

[STIRRELL]

Thank you but I need a vlookup formula. The issue is they listed all the open invoices in a single cell for each PO number. So I need the lookup formula to be combined with search function ( or something like that) to be able search in the cells for the specific invoice number and return the po number .

Spreadsheet 1 has the individual invoice number
Spreadsheet 2 has a list of open invoices all in one cell and in the next column is the PO number that I need pulled into spreadsheet 1.
Sample of the combined invoice information on spreadsheet 2

Associated Invoice(s)

A64297(780.00),A64253(87,987.70),A64226(103,547.54),A64227(2,913.60),A64100(4,414.00),A64303(106,137.23),A64346(66,367.04),A64354(393.16),A64153(46,089.76),A64323(172,845.17),A64304(5,260.00),A64

I know how to do a normal lookup , but I dont know how to combine the lookup and the search in one vlookup formula.

thank you for any help.

 

[Bosquedeguate]

Thank you for the clarification. I understand it better now.

 

[Bosquedeguate]

A few more questions ....
1) Do you have multiple rows of these comma delimited Invoice Number Strings? If you do please send 3 to 5 of these strings to test with.
2) In the worksheet where the stings of invoice number are listed can we add a column? My idea is to create a calculated value (the desired invoice number if it is found) in the new column. The function will parse it base on the value searched.
3) with the additional column a VLOOKUP can then be done to find the PO Number. See one-line example below.

Invoices Table (one or more rows of data)
Col A - Parsed Invoice number, Col B - Strings of Invoices, Col C - PO Number

GetLastRow.xlsm
	A	B	C
33	Invoice(s)	Invoice(s) Strings	PO Number
34	A64303	A64297(780.00),A64253(87,987.70),A64226(103,547.54),A64227(2,913.60),A64100(4,414.00),A64303(106,137.23),A64346(66,367.04),A64354(393.16),A64153(46,089.76),A64323(172,845.17),A64304(5,260.00),A64173(115,383.96),A64347(84,167.08)	PO12345
Sheet3

Cell Formulas
Range	Formula
A34	A34	=MID(B34,FIND(B35,B34),FIND("(",MID(B34,FIND(B35,B34)+1,30)))

On another sheet you would enter the Invoice Number to be found (like below)

GetLastRow.xlsm
	A	B
35	Invoice Number to find	A64303
36	PO Number	PO12345
Sheet3

Cell Formulas
Range	Formula
B36	B36	=VLOOKUP(B35,A34:C34,3)

The first column of the invoices table changes the contents of Col A based on the invoice number searched for. (B36), and
Returns the PO Number in Cell B36