Formula help

[dnorm]

Hi All

I have a table which is the Subjects pulled in from my emails, along with the DateTimeSent.
I am (in the lower table) trying to set up an "OK" "NOK" based on the date time stamp.
The formula I have so far is:-

=MAX(IF(COUNTIF(BCKUPs!$A$1:$A$4503,CONCAT("*",G11,"*")),BCKUPs!F$1:F$4503))

so if Result is found in Subject then return most recent DateTimeSent
I am trying to use a wild card to pull from the text which I receive, but it is not working.
Any help would be most welcome.

		Subject	DateTimeSent
		- [Success] Backup Configuration Job (1 machines)	14/12/2023 10:00​
		- [Success] ES Backup - DB051 Timeware Database (1 machines)	14/12/2023 07:29​
		- [Failed] ES Backup - DB002 (1 machines) 1 failed	14/12/2023 07:03​
		- [Success] ES Backup - SQ003 (1 machines)	14/12/2023 00:35​
		- [Success] ES Backup - AP014 Pirana (1 machines)	13/12/2023 22:29​
		- [Success] PS001, SQ004, AP015, DTS (4 machines)	13/12/2023 20:25​
		- [Success] SM002 + RADIUS + Timeware (3 machines)	13/12/2023 20:02​
		- [Warning] FS002 (1 machines)	13/12/2023 19:47​
		- [Success] FS001 (1 machines)	13/12/2023 19:41​
		- [Success] SQ003 (1 machines)	13/12/2023 19:34​
		- [Success] DB002 & AP014 (2 machines)	13/12/2023 19:18​
		- [Success] Backup Configuration Job (1 machines)	13/12/2023 10:00​
		- [Failed] FS002 (1 machines)	13/12/2023 09:55​
Status	Date Time Sent	Result
NOK	Jan/00 00:00​	- [Success] FS001
​	Jan/00 00:00​	- [Warning] FS001
NOK	Jan/00 00:00​	- [Failed] FS001
​	Jan/00 00:00​	- [Success] FS002
NOK	Jan/00 00:00​	- [Warning] FS002
​	Jan/00 00:00​	- [Failed] FS002

 

[RobP]

Hi,

would something like this offer you any help in your quest ?
Caveat here is that I can see your data is laid out in a time / date chronological order .. ie. most recent would be first (or at the top of the list). So this formula assumes you will always pull the "first instance" of the text find.
If it finds nothing, then it leaves the cell blank.

Let us know how you get one.
Cheers
Rob

Book1
	A	B	C	D	E
1			Subject	DateTimeSent
2			- [Success] Backup Configuration Job (1 machines)	14/12/2023 10:00
3			- [Success] ES Backup - DB051 Timeware Database (1 machines)	14/12/2023 07:29
4			- [Failed] ES Backup - DB002 (1 machines) 1 failed	14/12/2023 07:03
5			- [Success] ES Backup - SQ003 (1 machines)	14/12/2023 00:35
6			- [Success] FS001 (1 machines)	13/12/2023 22:29
7			- [Success] PS001, SQ004, AP015, DTS (4 machines)	13/12/2023 20:25
8			- [Success] SM002 + RADIUS + Timeware (3 machines)	13/12/2023 20:02
9			- [Warning] FS002 (1 machines)	13/12/2023 19:47
10			- [Success] FS001 (1 machines)	13/12/2023 19:41
11			- [Success] SQ003 (1 machines)	13/12/2023 19:34
12			- [Success] DB002 & AP014 (2 machines)	13/12/2023 19:18
13			- [Success] Backup Configuration Job (1 machines)	13/12/2023 10:00
14			- [Failed] FS002 (1 machines)	13/12/2023 09:55
15
16	Status	Date Time Sent	Result
17	NOK	Jan/00 00:00	- [Success] FS001	13/12/2023 22:29
18		Jan/00 00:00	- [Warning] FS001
19	NOK	Jan/00 00:00	- [Failed] FS001
20		Jan/00 00:00	- [Success] FS002
21	NOK	Jan/00 00:00	- [Warning] FS002	13/12/2023 19:47
22		Jan/00 00:00	- [Failed] FS002	13/12/2023 09:55
23
Sheet1

Cell Formulas
Range	Formula
D17:D22	D17	=IFERROR(CHOOSEROWS(FILTER(IF(ISNUMBER(FIND(C17,C$2:C$14)),D$2:D$14,""),(IF(ISNUMBER(FIND(C17,C$2:C$14)),D$2:D$14,""))<>""),1),"")

 

[Fluff]

Another option is the date/time is always in order.

Fluff.xlsm
	A	B	C	D
1			Subject	DateTimeSent
2			- [Success] Backup Configuration Job (1 machines)	14/12/2023 10:00
3			- [Success] ES Backup - DB051 Timeware Database (1 machines)	14/12/2023 07:29
4			- [Failed] ES Backup - DB002 (1 machines) 1 failed	14/12/2023 07:03
5			- [Success] ES Backup - SQ003 (1 machines)	14/12/2023 00:35
6			- [Success] FS001 (1 machines)	13/12/2023 22:29
7			- [Success] PS001, SQ004, AP015, DTS (4 machines)	13/12/2023 20:25
8			- [Success] SM002 + RADIUS + Timeware (3 machines)	13/12/2023 20:02
9			- [Warning] FS002 (1 machines)	13/12/2023 19:47
10			- [Success] FS001 (1 machines)	13/12/2023 19:41
11			- [Success] SQ003 (1 machines)	13/12/2023 19:34
12			- [Success] DB002 & AP014 (2 machines)	13/12/2023 19:18
13			- [Success] Backup Configuration Job (1 machines)	13/12/2023 10:00
14			- [Failed] FS002 (1 machines)	13/12/2023 09:55
15
16	Status	Date Time Sent	Result
17	NOK	13/12/2023 22:29	- [Success] FS001
18			- [Warning] FS001
19	NOK		- [Failed] FS001
20			- [Success] FS002
21	NOK	13/12/2023 19:47	- [Warning] FS002
22		13/12/2023 09:55	- [Failed] FS002
Sheet5

Cell Formulas
Range	Formula
B17:B22	B17	=TAKE(FILTER($D$2:$D$14,ISNUMBER(SEARCH(C17,$C$2:$C$14)),""),1)