Return column header for highest, 2nd highest, 3rd highest etc value

[junkforhr]

I have a dataset (reduced mock version below).

What I'm trying to do is return the column head for the highest count for site. As an example, Site 1 has the highest count of 15.455, so I'd like Type A returned. The 2nd highest value for Site 1 is 9,708 and this would return Type E. I have included a desired outcome table below.

The actual data will have 500 Sites and 100 columns, but I'm only looking for the Top 15 Types.

I had a go using the large formula to return the top nth for each site, but am at a lost how to use this in a lookup.

Mock table below

Site	Type A	Type B	Type C	Type D	Type E
Site1	9,589​	15,455​	3,131​	8,989​	9,708​
Site2	25,140​	13,014​	15,774​	25,302​	23,295​
Site3	22,683​	6,635​	16,905​	19,133​	23,445​
Site4	21,917​	1,812​	19,088​	9,464​	3,305​

Desired result

Site	Highest Type	2nd Highest Type	3rd Highest Type
Site1	Type B	Type E	Type A
Site2	Type D	Type A	Type E
Site3	Type E	Type A	Type D
Site4	Type A	Type C	Type D

 

[Peter_SSs]

1. Is it possible that two (or more) columns in the same row in the top table could have exactly the same values?
2. Can we assume that the Sites in the first column will be in exactly the same order in each table?

 

[junkforhr]

1. Is it possible that two (or more) columns in the same row in the top table could have exactly the same values?
2. Can we assume that the Sites in the first column will be in exactly the same order in each table?

Hi Peter,
Question 1: Yes it is possible, but highly unlikely.
Question 2: Yes

 

[kevin9999]

Given "Yes" to question 2, this might do until Peter returns with a more elegant response

Book1
	A	B	C	D	E	F
1	Site	Type A	Type B	Type C	Type D	Type E
2	Site1	9,589	15,455	3,131	8,989	9,708
3	Site2	25,140	13,014	15,774	25,302	23,295
4	Site3	22,683	6,635	16,905	19,133	23,445
5	Site4	21,917	1,815	19,088	9,464	3,305
6
7		Highest Type	2nd Highest Type	3rd Highest Type
8	Site1	Type B	Type E	Type A
9	Site2	Type D	Type A	Type E
10	Site3	Type E	Type A	Type D
11	Site2	Type A	Type C	Type D
Sheet1

Cell Formulas
Range	Formula
B8:B11	B8	=INDEX($B$1:$F$1,0,MATCH(LARGE($B2:$F2,1),$B2:$F2,0))
C8:C11	C8	=INDEX($B$1:$F$1,0,MATCH(LARGE($B2:$F2,2),$B2:$F2,0))
D8:D11	D8	=INDEX($B$1:$F$1,0,MATCH(LARGE($B2:$F2,3),$B2:$F2,0))

 

[Peter_SSs]

Question 1: Yes it is possible, but highly unlikely.

Given that, these are the best I could come up with so far

23 06 22.xlsm
	A	B	C	D	E	F
1	Site	Type A	Type B	Type C	Type D	Type E
2	Site1	9,708	15,455	3,131	8,989	9,708
3	Site2	25,140	13,014	15,774	25,302	23,295
4	Site3	22,683	6,635	16,905	19,133	23,445
5	Site4	21,917	1,812	19,088	9,464	3,305
6
7
8	Site	Highest Type	2nd Highest Type	3rd Highest Type
9	Site1	Type B	Type A	Type E
10	Site2	Type D	Type A	Type E
11	Site3	Type E	Type A	Type D
12	Site4	Type A	Type C	Type D
Top 3

Cell Formulas
Range	Formula
B9:B12	B9	=INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$F$1)/($B2:$F2=LARGE($B2:$F2,COLUMNS($B:B))),1))
C9:C12	C9	=INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$F$1)/($B2:$F2=LARGE($B2:$F2,COLUMNS($B:C))),1+(INDEX(2:2,MATCH(B9,$1:$1,0))=LARGE($B2:$F2,COLUMNS($B:C)))))
D9:D12	D9	=INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$F$1)/($B2:$F2=LARGE($B2:$F2,COLUMNS($B:D))),1+(INDEX(2:2,MATCH(C9,$1:$1,0))=LARGE($B2:$F2,COLUMNS($B:D)))+(INDEX(2:2,MATCH(B9,$1:$1,0))=LARGE($B2:$F2,COLUMNS($B:D)))))

If the answer to Q1 was "No" then you could use the 3 formulas that Kevin posted or this variation of it which can be copied across and down

23 06 22.xlsm
	A	B	C	D	E	F
1	Site	Type A	Type B	Type C	Type D	Type E
2	Site1	9,589	15,455	3,131	8,989	9,708
3	Site2	25,140	13,014	15,774	25,302	23,295
4	Site3	22,683	6,635	16,905	19,133	23,445
5	Site4	21,917	1,812	19,088	9,464	3,305
6
7
8	Site	Highest Type	2nd Highest Type	3rd Highest Type
9	Site1	Type B	Type E	Type A
10	Site2	Type D	Type A	Type E
11	Site3	Type E	Type A	Type D
12	Site4	Type A	Type C	Type D
Top 3

Cell Formulas
Range	Formula
B9:D12	B9	=INDEX($B$1:$F$1,MATCH(LARGE($B2:$F2,COLUMNS($B:B)),$B2:$F2,0))