Horizontal Return

[savvysum]

Hi I need to horizontally look at a data set & return the date of the most recent sale.
in this example, 2 sales occurred in April 2024, so cell A2 returns 4/30/2024.
A3 returns 1/31/2024 b/c 3 sales most recently occurred in Jan 2024.

A1

return Formula	06/30/2024	05/31/2024	04/30/2024	03/31/2024	02/29/2024	01/31/2024
4/30/2024	0	0	2	0	1	0
1/31/2024	0	0	0	0	0	3

Mr Excel

docs.google.com

 

[dreid1011]

Perhaps this:

Book1
	A	B	C	D	E	F	G
1	return Formula	6/30/2024	5/31/2024	4/30/2024	3/31/2024	2/29/2024	1/31/2024
2	4/30/2024	0	0	2	0	1	0
3	1/31/2024	0	0	0	0	0	3
Sheet3

Cell Formulas
Range	Formula
A2:A3	A2	=INDEX(FILTER($B$1:$G$1,B2:G2>0,"-"),1)

 

[savvysum]

I'm receiving this error when I plug the formula to A2
ErrorFILTER has mismatched range sizes. Expected row count: 1. column count: 6. Actual row count: 1, column count: 1.

 

[dreid1011]

I'm receiving this error when I plug the formula to A2
ErrorFILTER has mismatched range sizes. Expected row count: 1. column count: 6. Actual row count: 1, column count: 1.

Did you retype the formula or copy and paste?

 

[Cubist]

One more option.

Book1
	A	B	C	D	E	F	G
1	return Formula	6/30/2024	5/31/2024	4/30/2024	3/31/2024	2/29/2024	1/31/2024
2	4/30/2024	0	0	2	0	1	0
3	1/31/2024	0	0	0	0	0	3
Sheet1

Cell Formulas
Range	Formula
A2:A3	A2	=XLOOKUP(TRUE,B2:G2<>0,$B$1:$G$1,"-")

 

[dreid1011]

One more option.

Book1
	A	B	C	D	E	F	G
1	return Formula	6/30/2024	5/31/2024	4/30/2024	3/31/2024	2/29/2024	1/31/2024
2	4/30/2024	0	0	2	0	1	0
3	1/31/2024	0	0	0	0	0	3
Sheet1

Cell Formulas
Range	Formula
A2:A3	A2	=XLOOKUP(TRUE,B2:G2<>0,$B$1:$G$1,"-")

I was trying XLOOKUP first, but that didn't occur to me how to look up the value.

 

[Fluff]

ErrorFILTER has mismatched range sizes. Expected row count: 1. column count: 6. Actual row count: 1, column count: 1.

Is this for Excel or for Google Sheets?