Lookup for a result in a row obtained by mail

[Shacoh]

Hello everyone,

I want to facilitate backup monitoring of several client. I received a mail with the result of a backup (Success, Warning or Failed) with the name of the client, the name of the backup and the date. I tried a lot of solution but I'm not even sure that I can do what I am looking for.
In my Table, I succeed to extract all information that I need in multiple column and concatenate some to try the vlookup function. For exemple, I received this mail "Name1 [Failed] 2.BK-BIS" with the date 2021-10-21. "Name1" is a client, "2.BK-BIS" is the name of the backup.
I have a column with all mail from backup (I used Microsoft Flow to export them on Excel), and I need that the result "Failed" is automatically copy in my 2nd sheet, in the correct column (date) and the correct row (Client and Backup Name).
Do someone think that what I am trying to do is possible with Excel ?

Thank you very much !

[davsth]

In b4 =IF(COUNTIFS(Sheet1!$E$2:$E$5,Sheet2!$A4,Sheet1!$I$2:$I$5,Sheet2!B$2,Sheet1!$L$2:$L$5,"failed"),"failed","")

but you have a fail and a success on the same day, would you want to return this?

[Shacoh]

Thanks a lot for your answer and your time.

I tried your formula, but it return an error "too much argument" (perhaps I did a mistake because I have to translate the formula in FR, like changing "," for ";" and the name of all functions).

I will only have one result everyday for one client/backup. I will never have a fail and a success for the same backup name.

[Shacoh]

Actually, it was my fault. I did not translate the formula correctly. It is working perfectly, thanks a lot !
Is there a way to add every possible result in the formula ? (it work for "Failed", and I would like "Success" and "Warning" too, if it is possible)

[davsth]

IF each combination of date and client is unique (it is not in your sample) =IFERROR(LOOKUP(2,1/((Sheet1!$E$2:$E$5=Sheet2!$A4)*(Sheet1!$I$2:$I$5=Sheet2!B$2)),Sheet1!$L$2:$L$5),"")
might work

but lines 4 and 5
Name2 4.BK-DDE
21/10/2021


they took place as different times but which one would you use?

[Shacoh]

I did the mistake while I was trying to anonymized my data, sorry for that. But yes, each combination date/client is unique. Your formula is working wonderfully.

Thank you very much, you saves me tons of time, I really appreciate your precious help.