How to return cell header with latest date

[Sian1]

Hi, i have 4 columns with various date on it, how could i write a formula to return the header named based on the latest date

A	B	C	D	Return Value
	1/20/2020	1/20/2020	1/23/2020	D
7/11/2019	5/28/2019	8/6/2020	8/10/2020	D
	7/5/2021	6/23/2021	6/23/2021	B
7/5/2021	1/14/2021	1/14/2021	1/15/2021	A
	8/10/2020	1/3/2020	1/6/2020	B
	10/10/2019	6/23/2021	10/27/2020	C

 

[6StringJazzer]

Assuming you actually have A, B, C in the first row then this in row 2 and fill down:

=INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))

$scratch.xlsm
	A	B	C	D	E
1	A	B	C	D	Return Value
2		1/20/2020	1/20/2020	1/23/2020	D
3	7/11/2019	5/28/2019	8/6/2020	8/10/2020	D
4		7/5/2021	6/23/2021	6/23/2021	B
5	7/5/2021	1/14/2021	1/14/2021	1/15/2021	A
6		8/10/2020	1/3/2020	1/6/2020	B
7		10/10/2019	6/23/2021	10/27/2020	C
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))

 

[Sian1]

thanks so much, however can this be done in Power query?

 

[6StringJazzer]

I'm sorry, I didn't notice what forum this was in, I was just listing "Unanswered threads." I don't know PQ so hopefully someone else will come along.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"A", type date}, {"B", type date}, {"C", type date}, {"D", type date}}),
    AddCol = Table.AddColumn(ChangeType, "Latest Date", each Table.FromColumns({Table.ColumnNames(ChangeType),Record.ToList(_)})),
    TransformCol = Table.TransformColumns(AddCol, {"Latest Date", each Text.Combine(Table.SelectRows(_, (x)=> x[Column2] = List.Max(_[Column2]))[Column1],", ")})
in
    TransformCol

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	A	B	C	D		A	B	C	D	Latest Date
2		1/20/2020	1/20/2020	1/23/2020			1/20/2020	1/20/2020	1/23/2020	D
3	7/11/2019	5/28/2019	8/6/2020	8/10/2020		7/11/2019	5/28/2019	8/6/2020	8/10/2020	D
4		7/5/2021	6/23/2021	6/23/2021			7/5/2021	6/23/2021	6/23/2021	B
5	7/5/2021	1/14/2021	1/14/2021	1/15/2021		7/5/2021	1/14/2021	1/14/2021	1/15/2021	A
6		8/10/2020	1/3/2020	1/6/2020			8/10/2020	1/3/2020	1/6/2020	B
7	6/23/2021	10/10/2019	6/23/2021	10/27/2020		6/23/2021	10/10/2019	6/23/2021	10/27/2020	A, C
8
Sheet1

 

[Sian1]

thanks so much

 

[Sian1]

Table.TransformColumns(AddCol, {"Latest Date", each Text.Combine(Table.SelectRows(_, (x)=> x[Column2] = List.Max(_[Column2]))[Column1],", ")})

Hi, May i know what the Column 2 and Column 1 means? i am having a problem as tables have more data than the one that i have shown here. How could i list out the dates that i want to compare?

thanks so much for the help

 

[JGordon11]

Look at the tables in the [Latest Date] column of the AddCol step. Column1 contains the table column names and Column2 contains the dates.

 

[Sian1]

Yeah I did find out however I still get the error saying it couldn't find column1. Do you know why?

 

[Sian1]

Look at the tables in the [Latest Date] column of the AddCol step. Column1 contains the table column names and Column2 contains the dates.

Hi, if I add additional column with ID in it will this formula works?