Filter Function: Return single row while looking at entire array?

[Mplz]

Hello all. I have a data set that keeps track of individuals metrics for the calendar year. I would like to be able to search for a specific person, and return all dates and values that are over 0. Below is an example of my data set. I am trying to get the formula to spit out the dates and numbers for any instance where the value is greater than 0, and matches the name in the search field.

Using this function (=FILTER(D11:J15,D12:J12>0,""), I can get all values greater than 0, but it seems to me like you would want to have the whole array in the "include" portion of the formula because this is currently only giving me when the first person has values > 0. I can also get all values for the person in the search box using "D11:D15=M11" in the include portion, but when I try and combine the two using (D12:J12)*(D11:D15=M11) I get a Value error. Plus it also seems like even if it did work that would only give me the values for the first person since the array doesn't include the entire data set.

The goal would be to input the name in M11, and have it spit me out the dates at the top of the column as well as the value where the value is greater than 0.

Is there a way to use ChooseRow or something similar? Any help would be appreciated!

Attendance Draft Final Test v1.xlsx
	D	E	F	G	H	I	J	K	L	M
11		1/1/2025	1/2/2025	1/3/2025	1/4/2025	1/5/2025	1/6/2025		Name:	Bob Jones
12	Bob Jones	0	0	0.33	0	1	0
13	Jon Smith	0.5	0	0	1	0	0
14	Gary Neuman	0	1	0	0	0	0.5
15	Jack Gray	0	0	0	0	1	0
Sheet1

 

[Cubist]

Something like this?

Book1
	D	E	F	G	H	I	J	K	L	M
11		1/1/25	1/2/25	1/3/25	1/4/25	1/5/25	1/6/25		Name:	Bob Jones
12	Bob Jones	0	0	0.33	0	1	0		1/3/25	0.33
13	Jon Smith	0.5	0	0	1	0	0		1/5/25	1
14	Gary Neuman	0	1	0	0	0	0.5
15	Jack Gray	0	0	0	0	1	0
Sheet3

Cell Formulas
Range	Formula
L12:M13	L12	=LET(f,FILTER(E12:J15,D12:D15=M11),TRANSPOSE(FILTER(VSTACK(E11:J11,f),f>0)))
Dynamic array formulas.

 

[Mplz]

Something like this?

Book1
	D	E	F	G	H	I	J	K	L	M
11		1/1/25	1/2/25	1/3/25	1/4/25	1/5/25	1/6/25		Name:	Bob Jones
12	Bob Jones	0	0	0.33	0	1	0		1/3/25	0.33
13	Jon Smith	0.5	0	0	1	0	0		1/5/25	1
14	Gary Neuman	0	1	0	0	0	0.5
15	Jack Gray	0	0	0	0	1	0
Sheet3

Cell Formulas
Range	Formula
L12:M13	L12	=LET(f,FILTER(E12:J15,D12:D15=M11),TRANSPOSE(FILTER(VSTACK(E11:J11,f),f>0)))
Dynamic array formulas.

Incredible, you are a genius. This works perfectly!

If you are able can you help me wrap my head around the formula? I think I understand the first part, Let is defining "f" as the array filtered by name, but what is the calculation portion of the function doing? Transpose is changing it from horizontal to vertical I assume, how is the second filter with Vstack working? You are putting the Dates at the top together with "f" that you defined earlier and then filtering out everything > 0?

 

[Cubist]

If you are able can you help me wrap my head around the formula?

You can only FILTER 1-dimension at a time, either row or column.
The first FILTER filters for the row that contains the name.
VSTACK attaches the date row
The second FILTER filters columns where >0
Lastly, TRANSPOSE is as you say.