Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.

[InquisitiveFrog]

I have a large list of citizen names that are associated with eleven different attributes. I'm trying to find the best fit job for them based on each job having two defining attributes. This is what the 'Citizens Stats' tab looks like:

L4 =LARGE(Adaptability,L3)
L5 =COUNTIF(Adaptability,">="&L4)
Rank =IF(ROWS('Citizens Stats'!$A$41:A46)>COUNTIF(Adaptability,">="&LARGE(Adaptability,$L$3)),"",ROWS('Citizens Stats'!$A$41:A46))
Adaptability =IF(K8="","",LARGE(Adaptability,K8))
Name =IF(K8="","",INDEX(Name,AGGREGATE(15,6,(ROW(Name)-ROW('Citizens Stats'!$A$2)+1)/(Adaptability=Ranks!L8),COUNTIF(Ranks!$L$8:L8,Ranks!L8))))

I created a 'Ranks' tab where I first determined how many jobs needed a primary stat. In this case, four jobs ("Top") require a high "Adaptability" score. Then I determined what the highest score for Adaptability was 40, using the Large function. In order to account for duplicates I figured out how many scores matched the top score. For the Rank column I wanted it to change based on how many top scores I needed for jobs. I then added the top 4 scores, which all happened to be 40 in this instance and matched their name with their score taking duplicates into account. I am now trying to search the citizen tab based on the Name criteria to find their second highest score and attribute name. I was able to accomplish this on another sheet, but it requires that all 150 names are in alphabetical order since it is a row based search (2nd Score =LARGE('Citizens Stats'!B2:L2, 2), 2nd Stat =INDEX('Citizens Stats'!$B$1:$L$1,MATCH(LARGE('Citizens Stats'!B2:L2,2),'Citizens Stats'!B2:L2,0)) - although there was a problem with duplicates getting ignored which led me to trying the method outlined at the start of this paragraph. I just want to be able to give them their best job...based on their two highest scores. No rush, this is just me having fun in excel....

 

[StephenCrump]

Welcome to the Forum!

Perhaps something along these lines?

	A	B	C	D	E	F	G
1	Name		P	Q	R	S	T
2	A		30	30	32	21	29
3	B		18	32	35	35	25
4	C		34	34	1	19	13
5	D		23	20	36	31	35
6	E		3	36	23	17	8
7	F		27	3	33	27	33
8	G		5	7	23	32	23
9	H		40	8	31	8	33
10	I		18	32	14	12	11
11	J		36	39	28	40	31
12	K		29	6	25	25	6
13	L		12	1	30	22	30
14	M		26	7	3	34	24
15	N		10	5	5	35	31
16	O		39	4	11	8	16
17
18		Criteria
19		Chosen Score	Q
20		Large (N)	4
21					Highest
22				Q	Not Q
23		Names	B	32	35	R,S
24			C	34	34	P
25			E	36	23	R
26			I	32	18	P
27			J	39	40	S
Sheet1

Cell Formulas
Range	Formula
D22	D22	=C19
C23:C27	C23	=LET(x,XLOOKUP(Score,C1:G1,C2:G16),FILTER(A2:A16,x>=LARGE(x,N)))
D23:D27	D23	=XLOOKUP(C23#,A2:A16,XLOOKUP(Score,C1:G1,C2:G16))
E22	E22	="Not "&Score
E23:F27	E23	=LET(x,XLOOKUP(C23,A$2:A$16,C$2:G$16)*(C$1:G$1<>Score),CHOOSE({1,2},MAX(x),TEXTJOIN(",",,FILTER(C$1:G$1,x=MAX(x)))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
N	=Sheet4!$C$20	C23
Score	=Sheet4!$C$19	C23:D23, D22:E22, E23:E27