Using INDEX MATCH & LARGE to get value in multiple columns

[bunny1122]

Hi All

I need to get the largest value(date) for multiple reports in a large dataset and update it in Column B. The Report sample looks like the one from Column E to N.

I'm trying to lookup the figures in Column A and match with the Headers row in the dataset, then get the largest value(date) from the below columns.
I tried to use the INDEX MATCH with LARGE function but it only worked for the first one and return #REF! for the rest and I couldn't figure out how to fix it. Is there also a better way to lookup the whole dataset instead of selecting each column for each report? Appreciate any inputs. Thank you!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Report	Last Updated	expected output		AFS	%	HSL	%	ALL	%	DLL	%	MLL	%
2	AFS	07-Jun	07-Jun		03-Jun		07-Jun		07-Jun		07-Jun		03-Jun
3	HSL	#REF!	09-Jun		03-Jun		07-Jun		07-Jun		07-Jun		03-Jun
4	ALL	#REF!	10-Jun		04-Jun		08-Jun		08-Jun		08-Jun		04-Jun
5	DLL	#REF!	11-Jun		04-Jun		08-Jun		08-Jun		08-Jun		04-Jun
6	MLL	#REF!	08-Jun		05-Jun		09-Jun		09-Jun		09-Jun		05-Jun
7					05-Jun		09-Jun		09-Jun		09-Jun		05-Jun
8					06-Jun		09-Jun		10-Jun		10-Jun		06-Jun
9					06-Jun				10-Jun		10-Jun		06-Jun
10					07-Jun						11-Jun		07-Jun
11											11-Jun		07-Jun
12													08-Jun
13													08-Jun
Sheet1

Cell Formulas
Range	Formula
B2	B2	=INDEX(LARGE(E2:E13,1),MATCH(A2,E1:N1,0))
B3	B3	=INDEX(LARGE(G2:G13,1),MATCH(A3,E1:N1,0))
B4	B4	=INDEX(LARGE(I2:I13,1),MATCH(A4,E1:N1,0))
B5	B5	=INDEX(LARGE(I2:I13,1),MATCH(A5,E1:N1,0))
B6	B6	=INDEX(LARGE(I2:I13,1),MATCH(A6,E1:N1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AhoyNC]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Report	Last Updated	expected output		AFS	%	HSL	%	ALL	%	DLL	%	MLL	%
2	AFS	6/7/22	6/7/22		6/3/22		6/7/22		6/7/22		6/7/22		6/3/22
3	HSL	6/9/22	6/9/22		6/3/22		6/7/22		6/7/22		6/7/22		6/3/22
4	ALL	6/10/22	6/10/22		6/4/22		6/8/22		6/8/22		6/8/22		6/4/22
5	DLL	6/11/22	6/11/22		6/4/22		6/8/22		6/8/22		6/8/22		6/4/22
6	MLL	6/8/22	6/8/22		6/5/22		6/9/22		6/9/22		6/9/22		6/5/22
7					6/5/22		6/9/22		6/9/22		6/9/22		6/5/22
8					6/6/22		6/9/22		6/10/22		6/10/22		6/6/22
9					6/6/22				6/10/22		6/10/22		6/6/22
10					6/7/22						6/11/22		6/7/22
11											6/11/22		6/7/22
12													6/8/22
13													6/8/22
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=MAX(INDEX($E$2:$M$13,0,MATCH($A2,$E$1:$N$1,0)))

 

[Peter_SSs]

Another way

22 07 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Report	Last Updated	expected output		AFS	%	HSL	%	ALL	%	DLL	%	MLL
2	AFS	07-Jun	07-Jun		03-Jun		07-Jun		07-Jun		07-Jun		03-Jun
3	HSL	09-Jun	09-Jun		03-Jun		07-Jun		07-Jun		07-Jun		03-Jun
4	ALL	10-Jun	10-Jun		04-Jun		08-Jun		08-Jun		08-Jun		04-Jun
5	DLL	11-Jun	11-Jun		04-Jun		08-Jun		08-Jun		08-Jun		04-Jun
6	MLL	08-Jun	08-Jun		05-Jun		09-Jun		09-Jun		09-Jun		05-Jun
7					05-Jun		09-Jun		09-Jun		09-Jun		05-Jun
8					06-Jun		09-Jun		10-Jun		10-Jun		06-Jun
9					06-Jun				10-Jun		10-Jun		06-Jun
10					07-Jun						11-Jun		07-Jun
11											11-Jun		07-Jun
12													08-Jun
13													08-Jun
14
Max Date

Cell Formulas
Range	Formula
B2:B6	B2	=MAX(FILTER(E$2:M$20,E$1:M$1=A2))

 

[bunny1122]

Thanks so much for the fix!