List Products having Values Greater than Zero by Date

[dwgnome]

Hi,

Trying to create a list of products in which each product has a value greater than zero for a particular column (Date).

Table:
A B C D

1​	Product​	2/1/2023​	3/1/2023​	4/1/2023​
2​	AU1387001	7.0​	10.0​	8.0​
3​	AUE-101	0.9​	5.0​	1.0​
4​	CGC112	​	​	​
5​	CPC-SP41	330.0​	330.0​	660.0​
6​	CPC-SP47	330.0​	​	220.0​

Results should be for 3/1/2023:
A B

8	AU1387001	3/1/2023
9	AUE-101
10	CPC-SP41

I tried the following array formula:

={INDEX($A$2:$A$6,MATCH(1,($C$2:$C$6>0)*(COUNTIF(A$7:A7,$A$2:$A$6)=0),0))}

While this works, I need the flexibility of changing the date and have the formula choose the matching column rather than the hard entered portion $C$2:$C$6.

I tried inserting a column lookup as follows but it did not work:

={INDEX($A$2:$A$6,MATCH(1,MATCH($B$8,$B$1:$D$1))>0)*(COUNTIF(A$7:A7,$A$2:$A$6)=0)}
Where B8 has the date.

I have Excel 2019.

Thank you.

 

[Peter_SSs]

I have Excel 2019.

Please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Give this a try

25 03 06.xlsm
	A	B	C	D
1	Product	2/01/2023	3/01/2023	4/01/2023
2	AU1387001	7	10	8
3	AUE-101	0.9	5	1
4	CGC112
5	CPC-SP41	330	330	660
6	CPC-SP47	330		220
7
8	AU1387001	3/01/2023
9	AUE-101
10	CPC-SP41
11
12
List

Cell Formulas
Range	Formula
A8:A12	A8	=IFERROR(INDEX(A$2:A$6,AGGREGATE(15,6,(ROW(A$2:A$6)-ROW(A$2)+1)/(INDEX(B$2:D$6,0,MATCH(B$8,B$1:D$1,0))>0),ROWS(A$8:A8))),"")

 

[dwgnome]

Hi Peter,

Thank you for your suggested formula. It works perfectly.
And yes I updated my profile to reflect the Excel version and platform.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your profile.