Search partial text in a lookup

[Blockhead15]

I have 2 tables of data. Table 1 has a column of data with a text message. Below is a simplified example - sometimes the Column X data can be 5 lines of data so the Key 'm matching against may not always be in the first row of the cell. Depending on the text, I'm looking up the next step to do in our process. But I cannot get the lookup to work. used color coding to align which sections used where. Any thoughts?

Table 1

Column X	Logic
Fatal Error: The process cannot access the file	=@INDEX(Errors!$C$2:$C$99,AGGREGATE(15,6,MATCH("*"&Errors!$B$2:$B$99&"*",Q8,0)*(ROW(Errors!$B$2:$B$99)-ROW(Errors!B$2)+1),1))
Access to the path has been denied.
Failed: No records processed.
System failure. File in use by another process.

Table 2

Key	Lookup value
The process cannot access the file	Contact group GHI
Access to the path has been denied.	Contact group ABC
No records processed.	Fix file
File in use by another process.	Contact group XYZ

 

[felixstraube]

Since you have Excel 365, how about?:

Libro1
	A	B	C	D	E
1	column X	Logic		Key	Lookup value
2	Fatal Error: The process cannot access the file	Contact group GHI		The process cannot access the file	Contact group GHI
3	Access to the path has been denied.	Contact group ABC		Access to the path has been denied.	Contact group ABC
4	Failed: No records processed.	Fix file		No records processed.	Fix file
5	System failure. File in use by another process.	Contact group XYZ		File in use by another process.	Contact group XYZ
Hoja1

Cell Formulas
Range	Formula
B2:B5	B2	=FILTER($E$2:$E$5,ISNUMBER(FIND($D$2:$D$5,A2)))