How to check for value in a specific week column with duplicate name rows?

[Ckl43]

Friends,

I come to you in the hopes of finding a solution to a problem I consider rather complex - at least given my level of competence...

As seen in the sheet, I have duplicate rows being names of work-packages, and columns being weeks. For each work-package, and in a prioritized order, I want to check what its status is, which is given by the "worst" status. Thus: if any row for a work-package contains "Bad", the status is "Bad". If "Bad" is not found, but "OK" exists, then it is "OK". If neither "Bad" nor "OK" is found, it should be "Good". I tried to exemplify this in the "Desired overview".

I have toyed around with xlookups based on index/match, and tried to convert the but I am truly out too deep for my level. Can someone please help me create a functional formula? THANK YOU!

I have a t

 

[shaowu459]

is this what you need? i manually entered data into my workbook, so please change cell references in your own workbook.

240614练习.xlsx
	A	B	C	D	E	F	G	H
1		Week 10	Week 11	Week 12	Week 13	Week 14	Week 15
2	A	Good	Bad	Bad	Bad	Bad	Good
3	A	Bad	OK	Bad	Bad	OK	OK
4	A	Good	Good	Good	Bad	OK	OK
5	B	Good	Bad	Bad	OK	Good	OK
6	B	Bad	Good	Bad	OK	Bad	Good
7
8
9				Week 12
10			A	Bad	Bad
11			B	Bad	Bad
12
Sheet2

Cell Formulas
Range	Formula
E10:E11	E10	=LET(s,{"Bad";"OK";"Good"},XLOOKUP(0,0/FIND(s,CONCAT(IF((A$2:A$6=C10)*(B$1:G$1=D$9),B$2:G$6))),s))

 

[KOKOSEK]

Hmm, I think in overview for D should be bad :

Book1
	A	B	C
1		Week 12
2	A	bad
3	A	bad
4	A	good
5	B	bad
6	B	bad
7	C	OK
8	D	OK
9	D	bad
10	E	good
11
12			Week 12
13		A	bad
14		B	bad
15		C	OK
16		D	bad
17		E	good
Sheet1

Cell Formulas
Range	Formula
B13:B17	B13	=UNIQUE($A$2:$A$10)
C13:C17	C13	=CHOOSE(MAX(VLOOKUP(FILTER($B$2:$B$10,$A$2:$A$10=$B13),{"good",1;"OK",2;"bad",3},2,0)),"good","OK","bad")
Dynamic array formulas.