Partial Match Text in Cell and Have It Return Text From a Different Cell

[OhSheet]

Hello, Excel Wizards! Long time lurker, first time poster.

I am trying to figure out a way to populate text in the 'Program Details' column based on criteria of the 'Billing Code(s)' column BUT the populated/returned text is data from another tab in the workbook. Is this possible?

I currently have a countif function in place =IF(COUNTIF(A2,"*ABC123*"),"Program details blurb", "") but it doesn't provide me with the ability to pull in data from another cell to populate the result, and would need to put the output text right in the equation. Not 100% ideal as this may be updated quite frequently.

Any advice would be greatly appreciated!

Billing Code(s)	Program Details for ABC123
ABC123, DEF456	ideally populated from cell on a different tab of the same workbook
ABC123	ideally populated from cell on a different tab of the same workbook
ABC135, DEF456, GHI789	ideally populated from cell on a different tab of the same workbook

 

[pitchoute]

You can use a combination of the SEARCH, ISNUMBER, IF, and TEXTJOIN functions to check if a cell contains certain text and then return corresponding data from another sheet. By creating a mapping table of your billing codes and their program details, you can dynamically pull the required information without hardcoding it into your formulas.

=TEXTJOIN(", ", TRUE, IF(ISNUMBER(SEARCH(Sheet2!$A$2:$A$100, A2)), Sheet2!$B$2:$B$100, ""))

 

[Asbestos_Jen]

Does this do what you want?

2024-10-07.xlsx
	A	B
1	Billing Code(s)	Program Details for ABC123
2	ABC123, DEF456	blurb1
3	ABC123	blurb1
4	ABC135, DEF456, GHI789	blurb2
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX(Sheet2!$A$2:$B$5,MATCH(LEFT(A2,6),Sheet2!$A$2:$A$5,0),2)

2024-10-07.xlsx
	A	B
1	Code	Details
2	ABC123	blurb1
3	ABC135	blurb2
4	DEF456	blurb3
5	GHI789	blurb4
Sheet2

 

[OhSheet]

Does this do what you want?

2024-10-07.xlsx
	A	B
1	Billing Code(s)	Program Details for ABC123
2	ABC123, DEF456	blurb1
3	ABC123	blurb1
4	ABC135, DEF456, GHI789	blurb2
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX(Sheet2!$A$2:$B$5,MATCH(LEFT(A2,6),Sheet2!$A$2:$A$5,0),2)

2024-10-07.xlsx
	A	B
1	Code	Details
2	ABC123	blurb1
3	ABC135	blurb2
4	DEF456	blurb3
5	GHI789	blurb4
Sheet2

Thank you, Jen!! This works for when 'ABC123' is listed first in the string of billing codes. Is there a way to make it work if it's not listed first in the cell? Thanks so much!

 

[Asbestos_Jen]

Thank you, Jen!! This works for when 'ABC123' is listed first in the string of billing codes. Is there a way to make it work if it's not listed first in the cell? Thanks so much!

Is "ABC123" the only string that will matter, or will the others sometimes matter? Is there a table of possible outcomes, like the one I built on 'Sheet2'!, or do you always want the text from a specific cell to be referenced if "ABC123" is present in the searched cell?

 

[Asbestos_Jen]

Does one of these do what you want?

2024-10-07.xlsx
	A	B	C	D	E	F
1				value->ref	ref->ref	ref->lookup
2	Billing Code(s)	Program Details for ABC123			ABC123	ABC123
3	ABC123, DEF456	blurb1		blurb1	blurb1	blurb1
4	ABC123	blurb1		blurb2	blurb1	blurb1
5	ABC135, DEF456, GHI789	blurb2
Sheet1

Cell Formulas
Range	Formula
D3:D5	D3	=IF(COUNTIF(A3,"*ABC123*"),Sheet2!B2, "")
E3:E5	E3	=IF(ISNUMBER(SEARCH($E$2,A3)),Sheet2!$B$2,"")
F3:F5	F3	=IF(ISNUMBER(SEARCH($F$2,A3)),INDEX(Sheet2!$A$2:$B$5,MATCH($F$2,Sheet2!$A$2:$A$5,0),2),"")
B3:B5	B3	=INDEX(Sheet2!$A$2:$B$5,MATCH(LEFT(A3,6),Sheet2!$A$2:$A$5,0),2)

2024-10-07.xlsx
	A	B
1	Code	Details
2	ABC123	blurb1
3	ABC135	blurb2
4	DEF456	blurb3
5	GHI789	blurb4
Sheet2

 

[Peter_SSs]

Welcome to posting at the MrExcel board!

Perhaps you could give us a small sample from both sheets (with the expected results manually filled in) with XL2BB and explain the results in relation to the actual sample data?
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)