Find each occurance of string and return column value(s) where string was found

[rknudson]

I have an array of text in range Sheet1!$A$2:$BM$111 that I am trying to match each value that is in column Adresses!a4:a1098 and return the column in sheet1! where the string was found.

I have this formula

INDEX(Sheet1!$A$1:$BM$1, SUMPRODUCT(MAX((Sheet1!$A$2:$BM$111=A4)*(COLUMN(Sheet1!$A$2:$BM$111))))-COLUMN(Sheet1!$A$1)+1)

This formula will match the last item it finds. I am trying to get it to match each find and then return the corresponding index column to where it was found. Also if nothing is found it will return all index columns where it should be a blank. I hope I explained this well enough. I am frustratedly stuck.

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[rknudson]

CTCOEP-6-14-21.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM
1	Public_165.1.59.0-Hosts	NYC-XP-Hosts	Akamai-Whitelist	thunderstone.hosts	Amazon.trap.senders	subnet.165.1.0.0	ctc.https.public	aws-us-east-1-whitelist	Mashery-Whitelist	badguys	APImagesBadguys	Badhost	ctc.external.devices	crmQA-DEV	ctc.http.8080	Mobile-News	tacacs.servers	mimecast-int-hosts	ctc.ftp.public	ctc.http.public	Classification.Servers	Hosted2.Servers	SNMP.trap.recievers	SMTP-CTC-VIPs	Hosted1.Servers	TeraMark-Badguys	ctc.dmz.email.public	access-edge-lync-srv	web-conf-lync-srv	a-v-lync-srv	vancouver.destinations	Management-Networks	ctc.Outside.firewalls	ctceaisql	Internal.MS.Office365	Bureau-Private-NATs	amazon	fiberlink.radius.servers	ftp.fdch.com.servers	markets-ftp-servers	SMTP.outbound	SMTP-out-block	Mimecast	mimecast-datacenters	CTC-UserNets	subnet.165.1.58.0	subnet.165.1.59.0	subnet.172.16.0.0	subnet.173.1.245.32-sm27	subnet.174.129.0.0-sm16	subnet.64.151.88.240-sm28	subnet.74.3.238.80-sm28	ctc.dmz.nntp.servers	thunderstone.hosts.apsportspower	subnet.10.0.0.0	subnet.192.168.0.0	salesforce.subnets	statsinc.destinations	Static-Routes	statsinc.servers	subnet.all.ap	Subnet.MS.Office365	subnet.gogrid	ftp.sources	vancouver.servers
2	accuweatherbak	advstgdemo.apext	Akamai.204.237.142.0	ap.thunderstone.com	APMS1-3-Teragram-LB.elb.amazonaws.com	ap-public-range	access-edge-lync-srv	AWS-	AWS-Gateway1-US-West	badguy.100.43.153.60	badguys109.77.17.144	Badhost-10.1.26.163	crctcamazon1	CRMgwd-ext	ctcapmobile01-ext	ctcapmobile01-ext	ctcciscoworks	ctcdc20-int	apdigftppullnew	accuweatherbak	ctceapiclacl1_26.230	CTCEAPXCNSWEB01_124.167	CTCESMNNM01-ext	CTCEXCH01-ext	ctchnewsedge01_65.201	badguy_10.1.22.23	ctcipdx01	CTCLYNCEDGE01_52.111	CTCLYNCEDGE01_52.112	CTCLYNCEDGE01_52.113	ctcmegamq01	ctcneteng01-42.64	ctcoutep01	ctcxapif1am_49.84	CTCXTOOLS01-ext	Eastern-Private	ec2-184-73-121-236.compute-1.amazonaws.com	blue-bell	ftp.fdch.com	ftp1.tradeline.com	H-10.1.60.205	H-10.1.62.45	mimecast-network-1	mimecast-network-1	N-10.1.104.0-24	N-165.1.58.0-24	N-165.1.59.0-24	N-172.16.0.0-12	N-173.1.245.32-27	N-174.129.0.0-16	N-64.151.88.240-28	N-74.3.238.80-28	newscliptest	nrack.thunderstone.com	obj-10.0.0.0	obj-192.168.0.0	Salesforce-Merachnic-home-4testing	ctcmegamq01	Static-Route-107.21.26.30	statsinc10.statsinc.com	subnet.10.0.0.0	subnet.104.212.58.0-sm23	subnet.173.1.245.32-sm27	cp143.cp.org	vancouver1
3	adsendweb05	CTCAPIQCPXFA01_52.75	Akamai.23.203.52.0	nrack.thunderstone.com	EC2APMSCLA01.amazonaws.com		adsendweb05	AWS-54.71.177.216	AWS-Gateway-Europe	badguy.104.194.160.59	badguys178.149.67.27	Badhost-10.1.7.70	crctcsla1	CRMgwq-ext	ctcapmobile03-ext	ctcapmobile03-ext	ctcnetfinity	ctciam21_111_6	apmebeta	adsendweb05	ctceapiclasql01_26.228	CTCEAPXCNSWEB02_124.168		CTCEXCH02-ext	ctchnewsedge02_65.202	badguy_165.1.16.189	ctcipdx02-ext	CTCLYNCEDGE02_52.121	CTCLYNCEDGE02_52.122	CTCLYNCEDGE02_52.123	ctcmegamq02	ctcpanaorama01-175.25	ctcoutep02	ctcxapif1c0_49.85	CTCXTOOLS02-ext	WDC-Bureau-Private	ec2-204-236-192-128.compute-1.amazonaws.com	fiberlinkrad05	ftp.fdch2.com	ftp3.tradeline.com	H-10.1.60.206	H-10.1.62.46	mimecast-network-2	mimecast-network-2	N-10.1.105.0-24								seasprite				salesforce-subnet-member1.2	ctcmegamq02	Static-Route-118.41.146.213	statsinc5.statsinc.com	subnet.165.1.0.0	subnet.104.47.0.0-sm17	subnet.64.151.88.240-sm28	cp153.cp.org	vancouver2
4	advstgdemo.apext	CTCAPIQCPXFA02_52.76	Akamai.23.215.104.0	thunder.thunderstone.com1	EC2APMSCLA01a.amazonaws.com		advstgdemo.apext	aws-us-east-1-whitelist-member1	AWS-Gateway-IP2-Europe	badguy.114.143.243.234	badguys74.115.1.41	Badhost-10.6.50.56	crispmaster		ctcapmobile04-ext	ctcapmobile04-ext	ctcnetfinity01_29.25		campaignplus	advstgdemo.apext	ctceapiclasql02_26.229	CTCEAPXCNWEB01_124.11		CTCEXCH03-ext	ctchnewsedge03_65.203	bncanystream01	ctcipmx01				ctcunixdev01	CTCPANORAMA01	ctcoutep-cluster	ctcxapif1c1_49.86	SMTP-CTC.AP-ext	Western-Bureau-Private		fiberlinkrad06	ftp.fdch3.com	ftp6.tradeline.com		H-10.1.62.60	mimecast-network-3	mimecast-network-3	N-10.1.107.0-24								newsfeed				salesforce-subnet-member1.3	ctcunixdev01	Static-Route-12.130.28.213	statsinc6.statsinc.com	subnet.172.16.0.0	subnet.111.221.112.0-sm21	subnet.74.3.238.80-sm28	desktop-knudson-verizon
Sheet1

 

[rknudson]

Data I am searching for example

accuweatherbak
adres-beta
adsendweb05
advstgdemo.apext

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B
1
2
3
4	accuweatherbak	Public_165.1.59.0-Hosts, ctc.http.public
5	adres-beta
6	adsendweb05	Public_165.1.59.0-Hosts, ctc.https.public, ctc.http.public
7	advstgdemo.apext	NYC-XP-Hosts, Public_165.1.59.0-Hosts, ctc.https.public, ctc.http.public
8
Sheet2

Cell Formulas
Range	Formula
B4:B7	B4	=TEXTJOIN(", ",,IF(Sheet1!$A$2:$BM$111=A4,Sheet1!$A$1:$BM$1,""))

 

[rknudson]

Perfect, I attempted textjoin prior but was not getting the result I needed. Thank you for the quick response.

 

[Fluff]

You're welcome & thanks for the feedback.