filling cell with a specific text if another cell contains any of the specific keywords

[Excel Learner007]

Hi Everyone,
We have a huge sheet (almost 40k Rows). Now, there is a column "Short Description" and based on the keywords in that column, we need to categorize it as either CPU, Memory, Disk, Database or Storage. Below is the categorization. Would be great if someone can help.

Other	CPU	Memory	Disk	Database	Storage
aix	core	paging	mount	mssql	Storage
bios	wait	heap	data	oracle	Space
cluster	Cache	page	filesystem	sql
esx	delay	ram	drive	sybase
rate	handler	swap	nfs	sybdump
linux	performance	temp	read
nodes	processor	threshold	storage
slow	queue	timeout	write
unix	stats		vol
windows
vmware
vsphere
vmtools

 

[Excel Learner007]

I assume that already know how to return the Month from the Date (you could simply just return the whole date and format the columns to "mmmm" or you could use the TEXT function).
Can you provide more details where everything exists?
Specifically,
- What is the sheet name that this data will appear on?
- What range exactly, is the header row you show in your previous post located?
- Is your list of categories to look up in the same file?
- What is the name of the sheet this category list appears on?

Hi Joe, thanks for your response, please see my reply in Bold inline.

I assume that already know how to return the Month from the Date (you could simply just return the whole date and format the columns to "mmmm" or you could use the TEXT function). I do, but since we are writing a code, was wondering if we can automate this as well??
Can you provide more details where everything exists?
Specifically,
- What is the sheet name that this data will appear on? Raw_Data, File name would be Incident Analysis Raw Data
- What range exactly, is the header row you show in your previous post located? Row 1
- Is your list of categories to look up in the same file? Yes
- What is the name of the sheet this category list appears on? Keyword_List

 

[Excel Learner007]

Keyword	Category
aix	Other
bios	Other
cluster	Other
esx	Other
rate	Other
linux	Other
nodes	Other
slow	Other
unix	Other
windows	Other
vmware	Other
vsphere	Other
vmtools	Other
core	CPU
wait	CPU
Cache	CPU
delay	CPU
handler	CPU
performance	CPU
processor	CPU
queue	CPU
stats	CPU
paging	Memory
heap	Memory
page	Memory
ram	Memory
swap	Memory
temp	Memory
threshold	Memory
timeout	Memory
mount	Disk
data	Disk
filesystem	Disk
drive	Disk
nfs	Disk
read	Disk
storage	Disk
write	Disk
vol	Disk
mssql	Database
oracle	Database
sql	Database
sybase	Database
sybdump	Database
Storage	Storage
Space	Storage

 

[Joe4]

Would you be able to provide a small sample of data for us to work with (that would save us some time)?
If there is any sensitive information in it, all we really need to see are the Date and Short Description fields (you could remove the data from all the other columns since we are not using those).

 

[Excel Learner007]

Would you be able to provide a small sample of data for us to work with (that would save us some time)?
If there is any sensitive information in it, all we really need to see are the Date and Short Description fields (you could remove the data from all the other columns since we are not using those).

 

[Excel Learner007]

Sample data.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Ticket Number	Ticket Status	Assigned To	Account Name	Date	Ticket Priority	Short Description	Assignment Group	Month	Category
2					01-12-2021 00:00		'not able to login into AWS'
3					01-12-2021 00:00		''(NOC) Alert: (S4) LiFung - Alert when Disk Usage over Warning Threshold on /u01 -- vmukonedbreppd1''
4					01-12-2021 00:00		'FW: Alert: (S3)-SZX-INT-ZMT cnzmt-ftcluster FT FW-Circuit CT - IPCYW2559545798 (Fortigate)- 116.6.240.114 - port6 - (INT) CT 50M has a receive utilization ove'
5					01-12-2021 00:00		Backup failed for below devices SYD01 :Daily_Backup_App_scsydprpsnt03.suncorp.com and SYD04: Daily_Backup_Mgmt_scnpdb06.suncorp.local Daily_Backup_Mgmt_scnpdb07.suncorp.local Daily_Backup_Mgmt_snppum04.suncorp.local
6					01-12-2021 00:00		Sudden surge in ticket volume for :NCC-PSR
7					01-12-2021 00:00		Backup failed for below devices SYD01 :Daily_Backup_App_scsydprpsnt03.suncorp.com and SYD04: Daily_Backup_Mgmt_scnpdb05.suncorp.local Daily_Backup_Mgmt_scnpsrm01.suncorp.local Daily_Backup_Mgmt_scnpvcsa01.suncorp.local
8					01-12-2021 00:00		'Guest Wifi password does not update'
9					01-12-2021 00:00		'Alert: (S3)-VNM-INT-HCM vnm-hq-ftcluster -202.151.175.105 (Fortigate) - port1 (IA - External) (INT) 100mb has a receive utilization over 95%'
10					01-12-2021 00:00		'Alert: (S3)-KOR-INT-SY-CH-FT (Fortigate) korsy-ch-ft1 (CheonanWarehouse)-Circuit 205800040935-172.16.110.3 - wan1 - (INT) 10MB/10MB has a receive utilization '
11					01-12-2021 00:00		'Alert: (S3)-PHL-INT-IDSMED Internet-phlidsmed-ftcluster-172.16.89.5- (Fortigate) (0830-1730 Mon-Fri) - wan2 - (INT) 20M-146.88.68.122 has a receive utilization '
12					01-12-2021 00:00		'Alert: (S2)-THL-MAN-TRUE DSG Router-U02904 - 172.26.166.1 - GigabitEthernet0/1.100 ((( Connected to TRUE MPLS CID:U90007B ))) (MAN) has a receive utilization '
13					01-12-2021 00:00		'FW: Node Down: (S3)-SHA-WLAN-TB-10F-AP02-172.16.244.21 - IP: 172.16.244.21 at 12/1/2021 10:05 AM'
14					01-12-2021 00:00		''UK region LFEMARNAS1 NAS Filer is not accessible and the backup are failing. ''
15					01-12-2021 00:00		'FW: Alert: (S3)-PHL-LFL-MAN-Davao Router-172.26.188.8-(0800-1900 Mon-Sat) - GigabitEthernet0/1/0 [PLDT_WAN_2M] (MAN) has a receive utilization over 95%'
16					01-12-2021 00:00		'FW: Alert: (S3)-PHL-LFL-MAN-BEVI Alaminos Router-172.27.94.8 - GigabitEthernet0/1/0 [PLDT] has a receive utilization over 95%'
17					01-12-2021 00:00		'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'
18					01-12-2021 00:00		'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'
19					01-12-2021 00:00		'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'
20					01-12-2021 00:00		'(NOC) TEM - <Follow up> Server not reporting to the TEM console > 12 hours - 2021-12-01'
21					01-12-2021 00:00		'(NOC) TEM - <Follow up> Relay server not reporting to the TEM console > 12 hours - 2021-12-01'
22					01-12-2021 00:00		'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'
23					01-12-2021 00:00		'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'
24					01-12-2021 00:00		'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'
25					01-12-2021 00:00		'Alert: (S2)-MYS-LFL-MAN-TPG Redtone Router-172.26.202.136 - GigabitEthernet8 ** WAN[Primary MetroE] burst 4mb)10mb ** (MAN) has a receive utilization over 95'
26					01-12-2021 00:00		'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'
27					01-12-2021 00:00		'Alert: (S2)-TWN-LFL-LAN SYDC2 Core SW-172.26.232.3 - GigabitEthernet0/10 - Gi0/10 Connect to MPLS Line 35M/6M (MAN) has a receive utilization over 95%'
28					01-12-2021 00:00		'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'
29					01-12-2021 00:00		'(NOC) TEM - <Follow up> Server not reporting to the TEM console > 12 hours - 2021-12-01'
30					01-12-2021 00:00		'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'
Sheet1

 

[jtakw]

Hi,

If you're still interested in a formula solution:

Book3.xlsx
	G			J	K	L	M
1	Short Description			Category		Keyword	Category
2	'not able to login into AWS'			NA		aix	Other
3	''(NOC) Alert: (S4) LiFung - Alert when Disk Usage over Warning Threshold on /u01 -- vmukonedbreppd1''			Memory		bios	Other
4	'FW: Alert: (S3)-SZX-INT-ZMT cnzmt-ftcluster FT FW-Circuit CT - IPCYW2559545798 (Fortigate)- 116.6.240.114 - port6 - (INT) CT 50M has a receive utilization ove'			Other		cluster	Other
5	Backup failed for below devices SYD01 :Daily_Backup_App_scsydprpsnt03.suncorp.com and SYD04: Daily_Backup_Mgmt_scnpdb06.suncorp.local Daily_Backup_Mgmt_scnpdb07.suncorp.local Daily_Backup_Mgmt_snppum04.suncorp.local			NA		esx	Other
6	Sudden surge in ticket volume for :NCC-PSR			Disk		rate	Other
7	Backup failed for below devices SYD01 :Daily_Backup_App_scsydprpsnt03.suncorp.com and SYD04: Daily_Backup_Mgmt_scnpdb05.suncorp.local Daily_Backup_Mgmt_scnpsrm01.suncorp.local Daily_Backup_Mgmt_scnpvcsa01.suncorp.local			NA		linux	Other
8	'Guest Wifi password does not update'			NA		nodes	Other
9	'Alert: (S3)-VNM-INT-HCM vnm-hq-ftcluster -202.151.175.105 (Fortigate) - port1 (IA - External) (INT) 100mb has a receive utilization over 95%'			Other		slow	Other
10	'Alert: (S3)-KOR-INT-SY-CH-FT (Fortigate) korsy-ch-ft1 (CheonanWarehouse)-Circuit 205800040935-172.16.110.3 - wan1 - (INT) 10MB/10MB has a receive utilization '			NA		unix	Other
11	'Alert: (S3)-PHL-INT-IDSMED Internet-phlidsmed-ftcluster-172.16.89.5- (Fortigate) (0830-1730 Mon-Fri) - wan2 - (INT) 20M-146.88.68.122 has a receive utilization '			Other		windows	Other
12	'Alert: (S2)-THL-MAN-TRUE DSG Router-U02904 - 172.26.166.1 - GigabitEthernet0/1.100 ((( Connected to TRUE MPLS CID:U90007B ))) (MAN) has a receive utilization '			NA		vmware	Other
13	'FW: Node Down: (S3)-SHA-WLAN-TB-10F-AP02-172.16.244.21 - IP: 172.16.244.21 at 12/1/2021 10:05 AM'			NA		vsphere	Other
14	''UK region LFEMARNAS1 NAS Filer is not accessible and the backup are failing. ''			NA		vmtools	Other
15	'FW: Alert: (S3)-PHL-LFL-MAN-Davao Router-172.26.188.8-(0800-1900 Mon-Sat) - GigabitEthernet0/1/0 [PLDT_WAN_2M] (MAN) has a receive utilization over 95%'			NA		core	CPU
16	'FW: Alert: (S3)-PHL-LFL-MAN-BEVI Alaminos Router-172.27.94.8 - GigabitEthernet0/1/0 [PLDT] has a receive utilization over 95%'			NA		wait	CPU
17	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		Cache	CPU
18	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		delay	CPU
19	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		handler	CPU
20	'(NOC) TEM - <Follow up> Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		performance	CPU
21	'(NOC) TEM - <Follow up> Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		processor	CPU
22	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		queue	CPU
23	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		stats	CPU
24	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		paging	Memory
25	'Alert: (S2)-MYS-LFL-MAN-TPG Redtone Router-172.26.202.136 - GigabitEthernet8 ** WAN[Primary MetroE] burst 4mb)10mb ** (MAN) has a receive utilization over 95'			NA		heap	Memory
26	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		page	Memory
27	'Alert: (S2)-TWN-LFL-LAN SYDC2 Core SW-172.26.232.3 - GigabitEthernet0/10 - Gi0/10 Connect to MPLS Line 35M/6M (MAN) has a receive utilization over 95%'			CPU		ram	Memory
28	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		swap	Memory
29	'(NOC) TEM - <Follow up> Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		temp	Memory
30	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		threshold	Memory
31						timeout	Memory
32						mount	Disk
33						data	Disk
34						filesystem	Disk
35						drive	Disk
36						nfs	Disk
37						read	Disk
38						storage	Disk
39						write	Disk
40						vol	Disk
41						mssql	Database
42						oracle	Database
43						sql	Database
44						sybase	Database
45						sybdump	Database
46						Storage	Storage
47						Space	Storage
Sheet1044

Cell Formulas
Range	Formula
J2:J30	J2	=IFERROR(LOOKUP(2,1/SEARCH(L$2:L$47,G2),M$2:M$47),"NA")

 

[Joe4]

Hi,

If you're still interested in a formula solution:

Book3.xlsx
	G			J	K	L	M
1	Short Description			Category		Keyword	Category
2	'not able to login into AWS'			NA		aix	Other
3	''(NOC) Alert: (S4) LiFung - Alert when Disk Usage over Warning Threshold on /u01 -- vmukonedbreppd1''			Memory		bios	Other
4	'FW: Alert: (S3)-SZX-INT-ZMT cnzmt-ftcluster FT FW-Circuit CT - IPCYW2559545798 (Fortigate)- 116.6.240.114 - port6 - (INT) CT 50M has a receive utilization ove'			Other		cluster	Other
5	Backup failed for below devices SYD01 :Daily_Backup_App_scsydprpsnt03.suncorp.com and SYD04: Daily_Backup_Mgmt_scnpdb06.suncorp.local Daily_Backup_Mgmt_scnpdb07.suncorp.local Daily_Backup_Mgmt_snppum04.suncorp.local			NA		esx	Other
6	Sudden surge in ticket volume for :NCC-PSR			Disk		rate	Other
7	Backup failed for below devices SYD01 :Daily_Backup_App_scsydprpsnt03.suncorp.com and SYD04: Daily_Backup_Mgmt_scnpdb05.suncorp.local Daily_Backup_Mgmt_scnpsrm01.suncorp.local Daily_Backup_Mgmt_scnpvcsa01.suncorp.local			NA		linux	Other
8	'Guest Wifi password does not update'			NA		nodes	Other
9	'Alert: (S3)-VNM-INT-HCM vnm-hq-ftcluster -202.151.175.105 (Fortigate) - port1 (IA - External) (INT) 100mb has a receive utilization over 95%'			Other		slow	Other
10	'Alert: (S3)-KOR-INT-SY-CH-FT (Fortigate) korsy-ch-ft1 (CheonanWarehouse)-Circuit 205800040935-172.16.110.3 - wan1 - (INT) 10MB/10MB has a receive utilization '			NA		unix	Other
11	'Alert: (S3)-PHL-INT-IDSMED Internet-phlidsmed-ftcluster-172.16.89.5- (Fortigate) (0830-1730 Mon-Fri) - wan2 - (INT) 20M-146.88.68.122 has a receive utilization '			Other		windows	Other
12	'Alert: (S2)-THL-MAN-TRUE DSG Router-U02904 - 172.26.166.1 - GigabitEthernet0/1.100 ((( Connected to TRUE MPLS CID:U90007B ))) (MAN) has a receive utilization '			NA		vmware	Other
13	'FW: Node Down: (S3)-SHA-WLAN-TB-10F-AP02-172.16.244.21 - IP: 172.16.244.21 at 12/1/2021 10:05 AM'			NA		vsphere	Other
14	''UK region LFEMARNAS1 NAS Filer is not accessible and the backup are failing. ''			NA		vmtools	Other
15	'FW: Alert: (S3)-PHL-LFL-MAN-Davao Router-172.26.188.8-(0800-1900 Mon-Sat) - GigabitEthernet0/1/0 [PLDT_WAN_2M] (MAN) has a receive utilization over 95%'			NA		core	CPU
16	'FW: Alert: (S3)-PHL-LFL-MAN-BEVI Alaminos Router-172.27.94.8 - GigabitEthernet0/1/0 [PLDT] has a receive utilization over 95%'			NA		wait	CPU
17	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		Cache	CPU
18	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		delay	CPU
19	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		handler	CPU
20	'(NOC) TEM - <Follow up> Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		performance	CPU
21	'(NOC) TEM - <Follow up> Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		processor	CPU
22	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		queue	CPU
23	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		stats	CPU
24	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		paging	Memory
25	'Alert: (S2)-MYS-LFL-MAN-TPG Redtone Router-172.26.202.136 - GigabitEthernet8 ** WAN[Primary MetroE] burst 4mb)10mb ** (MAN) has a receive utilization over 95'			NA		heap	Memory
26	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		page	Memory
27	'Alert: (S2)-TWN-LFL-LAN SYDC2 Core SW-172.26.232.3 - GigabitEthernet0/10 - Gi0/10 Connect to MPLS Line 35M/6M (MAN) has a receive utilization over 95%'			CPU		ram	Memory
28	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		swap	Memory
29	'(NOC) TEM - <Follow up> Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		temp	Memory
30	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		threshold	Memory
31						timeout	Memory
32						mount	Disk
33						data	Disk
34						filesystem	Disk
35						drive	Disk
36						nfs	Disk
37						read	Disk
38						storage	Disk
39						write	Disk
40						vol	Disk
41						mssql	Database
42						oracle	Database
43						sql	Database
44						sybase	Database
45						sybdump	Database
46						Storage	Storage
47						Space	Storage
Sheet1044

Cell Formulas
Range	Formula
J2:J30	J2	=IFERROR(LOOKUP(2,1/SEARCH(L$2:L$47,G2),M$2:M$47),"NA")

Nice formula!

Only issue is it wouldn't handle this scenario:

Yes, there is a possibility that 2 or more words might appear in the cell

as I believe it will only return the first value it finds.

 

[Excel Learner007]

Hi,

If you're still interested in a formula solution:

Book3.xlsx
	G			J	K	L	M
1	Short Description			Category		Keyword	Category
2	'not able to login into AWS'			NA		aix	Other
3	''(NOC) Alert: (S4) LiFung - Alert when Disk Usage over Warning Threshold on /u01 -- vmukonedbreppd1''			Memory		bios	Other
4	'FW: Alert: (S3)-SZX-INT-ZMT cnzmt-ftcluster FT FW-Circuit CT - IPCYW2559545798 (Fortigate)- 116.6.240.114 - port6 - (INT) CT 50M has a receive utilization ove'			Other		cluster	Other
5	Backup failed for below devices SYD01 :Daily_Backup_App_scsydprpsnt03.suncorp.com and SYD04: Daily_Backup_Mgmt_scnpdb06.suncorp.local Daily_Backup_Mgmt_scnpdb07.suncorp.local Daily_Backup_Mgmt_snppum04.suncorp.local			NA		esx	Other
6	Sudden surge in ticket volume for :NCC-PSR			Disk		rate	Other
7	Backup failed for below devices SYD01 :Daily_Backup_App_scsydprpsnt03.suncorp.com and SYD04: Daily_Backup_Mgmt_scnpdb05.suncorp.local Daily_Backup_Mgmt_scnpsrm01.suncorp.local Daily_Backup_Mgmt_scnpvcsa01.suncorp.local			NA		linux	Other
8	'Guest Wifi password does not update'			NA		nodes	Other
9	'Alert: (S3)-VNM-INT-HCM vnm-hq-ftcluster -202.151.175.105 (Fortigate) - port1 (IA - External) (INT) 100mb has a receive utilization over 95%'			Other		slow	Other
10	'Alert: (S3)-KOR-INT-SY-CH-FT (Fortigate) korsy-ch-ft1 (CheonanWarehouse)-Circuit 205800040935-172.16.110.3 - wan1 - (INT) 10MB/10MB has a receive utilization '			NA		unix	Other
11	'Alert: (S3)-PHL-INT-IDSMED Internet-phlidsmed-ftcluster-172.16.89.5- (Fortigate) (0830-1730 Mon-Fri) - wan2 - (INT) 20M-146.88.68.122 has a receive utilization '			Other		windows	Other
12	'Alert: (S2)-THL-MAN-TRUE DSG Router-U02904 - 172.26.166.1 - GigabitEthernet0/1.100 ((( Connected to TRUE MPLS CID:U90007B ))) (MAN) has a receive utilization '			NA		vmware	Other
13	'FW: Node Down: (S3)-SHA-WLAN-TB-10F-AP02-172.16.244.21 - IP: 172.16.244.21 at 12/1/2021 10:05 AM'			NA		vsphere	Other
14	''UK region LFEMARNAS1 NAS Filer is not accessible and the backup are failing. ''			NA		vmtools	Other
15	'FW: Alert: (S3)-PHL-LFL-MAN-Davao Router-172.26.188.8-(0800-1900 Mon-Sat) - GigabitEthernet0/1/0 [PLDT_WAN_2M] (MAN) has a receive utilization over 95%'			NA		core	CPU
16	'FW: Alert: (S3)-PHL-LFL-MAN-BEVI Alaminos Router-172.27.94.8 - GigabitEthernet0/1/0 [PLDT] has a receive utilization over 95%'			NA		wait	CPU
17	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		Cache	CPU
18	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		delay	CPU
19	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		handler	CPU
20	'(NOC) TEM - <Follow up> Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		performance	CPU
21	'(NOC) TEM - <Follow up> Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		processor	CPU
22	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		queue	CPU
23	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		stats	CPU
24	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		paging	Memory
25	'Alert: (S2)-MYS-LFL-MAN-TPG Redtone Router-172.26.202.136 - GigabitEthernet8 ** WAN[Primary MetroE] burst 4mb)10mb ** (MAN) has a receive utilization over 95'			NA		heap	Memory
26	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		page	Memory
27	'Alert: (S2)-TWN-LFL-LAN SYDC2 Core SW-172.26.232.3 - GigabitEthernet0/10 - Gi0/10 Connect to MPLS Line 35M/6M (MAN) has a receive utilization over 95%'			CPU		ram	Memory
28	'(NOC) TEM - <Follow up>Relay server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		swap	Memory
29	'(NOC) TEM - <Follow up> Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		temp	Memory
30	'(NOC) TEM - <Follow up>Server not reporting to the TEM console > 12 hours - 2021-12-01'			NA		threshold	Memory
31						timeout	Memory
32						mount	Disk
33						data	Disk
34						filesystem	Disk
35						drive	Disk
36						nfs	Disk
37						read	Disk
38						storage	Disk
39						write	Disk
40						vol	Disk
41						mssql	Database
42						oracle	Database
43						sql	Database
44						sybase	Database
45						sybdump	Database
46						Storage	Storage
47						Space	Storage
Sheet1044

Cell Formulas
Range	Formula
J2:J30	J2	=IFERROR(LOOKUP(2,1/SEARCH(L$2:L$47,G2),M$2:M$47),"NA")

Thanks, it works! For now, the level of details we are looking for can be solved using this solution. Much Appreciated

 

[Excel Learner007]

Nice formula!

Only issue is it wouldn't handle this scenario:

as I believe it will only return the first value it finds.

True, however, it solves the issue to a level where we are comfortable with the outcome.
Joe, I would like to thank you for all the help and interest you have shown to solve my problem and I look forward to your VBA Solution as well, as that will help us enhance the accuracy of the data..

 

[Joe4]

True, however, it solves the issue to a level where we are comfortable with the outcome.
Joe, I would like to thank you for all the help and interest you have shown to solve my problem and I look forward to your VBA Solution as well, as that will help us enhance the accuracy of the data..

You are welcome.

It may be a little while, as it may take some time to build it (and I got some things going on at work too).