Using text in a text string to reference data in another cell

[HansM]

	Job #s	Invoice #'s
	a	b
1	#1581, #1580 , #1579 , #1578 , #1577	<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><!--EndFragment--> 1491 <colgroup><col width="87" style="width:65pt"> </colgroup><tbody> <!--StartFragment--> </tbody>
2	#1346, #1366	<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style> 1293
3	#1343 <colgroup><col></colgroup><tbody> </tbody>	1279

<tbody>
</tbody>

My CRM site sends a downloaded file to excel for services performed the issue come because it compiles all the jobs completed into one invoice and places all the job numbers in one cell. They all have the # sign in front of them and they are seperated by commas. I am trying to create a spreadsheet that can search the jobs (A) and refrence the invoice number(B). I have tried Vlookup and Search as well as match and the fact that there are multiple job numbers in the same cell seem to be the issue. Any help would be greatly appericiated.

 

[Norie]

Why not split the job #s out into individual cells?

 

[HansM]

Why not split the job #s out into individual cells?

The original numbers come from a report that is saved in excel I thought about using text to columns but some cells have 100 jobs# in them so my excel sheet would be huge.

 

[Norie]

Which version of Excel do you have?

If it's one with Power Query/Get and transform available it would be easy to transform the column of job #s to a column with one # in each cell.

 

[mrshl9898]

=INDIRECT(ADDRESS(MATCH("*"&F1&"*",A:A,0),2))

I had the invoice number in F1, is this what you wanted?

*just check this if the value doesn't exist, i'm getting some mixed results

 

[HansM]

Here is the original "Source File" Job#'s are in Column (B) and Invoice number are in column (G)

 

[HansM]

Here is a copy of the sheet I'm trying to use to capture the data. The issue is there are thousands of job #'s and I need the function to know the difference between #123 , #123 4, and #123 45.

 

[HansM]

This is good but in the code =INDIRECT(ADDRESS(MATCH("*"&F1&"*",A:A,0),2)) it does not address job numbers of variable size 123, 1234, 12345. See the posts with the actual screenshots of the file. Again thank you for any help.

 

[mrshl9898]

The easiest thing I can think of is to add a comma after the string in B:B (add a column, use =B2&"," then replace column) and use:

=INDIRECT("'Invoices Report'!"&ADDRESS(MATCH("*"&A1&","&"*",'Invoices Report'!$B:$B,0),7))