return reference if in date period and 'yes'

[mattbnorris]

So I have a table with a list of loads of different references with dates assigned to them and also 'yes' or 'no' assigned to them. I need to return the reference numbers if the date is in a specific period and they have 'yes' assigned.

Example
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Ref[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan-Mar 2019 & Yes[/TD]
[/TR]
[TR]
[TD]01/01/19[/TD]
[TD]#111[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]#111[/TD]
[/TR]
[TR]
[TD]23/01/19[/TD]
[TD]#444[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]#444[/TD]
[/TR]
[TR]
[TD]25/02/19[/TD]
[TD]#333[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD]#222[/TD]
[/TR]
[TR]
[TD]01/01/19[/TD]
[TD]#222[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]#555[/TD]
[/TR]
[TR]
[TD]07/03/19[/TD]
[TD]#555[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[njimack]

=IF(AND(A2>=DATE(2019,1,1),A2<=DATE(2019,3,31),C2="Yes"),B2,"")

 

[Peter_SSs]

Similar concept to this one I helped you with.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Ref	Status		Start Month	1		List
2	1/01/2019	#111	Yes		End Month	3		#111
3	23/01/2019	#444	Yes		Status	Yes		#444
4	25/02/2019	#333	No					#222
5	1/01/2019	#222	Yes					#555
6	7/03/2019	#555	Yes
Date Yes

 

[mattbnorris]

Hi Peter, thanks for the help on this once again. Does this method work even with columns A, B and C in a different sheet or workbook?

 

[Peter_SSs]

Hi Peter, thanks for the help on this once again. Does this method work even with columns A, B and C in a different sheet or workbook?

Give it a try?

 

[mattbnorris]

Give it a try?

Hi peter, I have tried, I've also tried putting the information on the same sheet and its not working. I think it's something to do with the equation inside the aggregate equation? Not sure

 

[Peter_SSs]

If you set up a fresh sheet exactly like the one in post 3 does it work?

Simply saying "its not working" gives us nothing to go on, especially when you can see the posted example is working.
When things don't work, you need to explain in what way they don't work (gives #REF ! error, Returns "x" instead of "y", crashes Excel etc) and give an example or two of sample data that it failed for.

 

[mattbnorris]

Give it a try?

Hello again! I got something to work however now I want multiple status conditions to be picked up
Example

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Ref[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan-Mar 19 "Active" & "Reactive" & "Yes"[/TD]
[/TR]
[TR]
[TD]15/01/19[/TD]
[TD]#111[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#111[/TD]
[/TR]
[TR]
[TD]14/02/18[/TD]
[TD]#222[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#333[/TD]
[/TR]
[TR]
[TD]27/03/19[/TD]
[TD]#333[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#444[/TD]
[/TR]
[TR]
[TD]30/01/19[/TD]
[TD]#444[/TD]
[TD]Reactive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/01/19[/TD]
[TD]#555[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I tried this but it doesn't seem to be working, is there a limit on the conditions I can have in there?

=INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(C$2:C$6="Reactive")*(C$2:C$6="Active")*(C$2:C$6="Yes")*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2)))


This returns a #NUM ! error

 

[Peter_SSs]

I tried this but it doesn't seem to be working, is there a limit on the conditions I can have in there?

=INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(C$2:C$6="Reactive")*(C$2:C$6="Active")*(C$2:C$6="Yes")*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2)))

There is no practical limit to the number of conditions, however when multiplying the conditions like that it acts like AND. Of course a value in C2:C6 cannot be "Reactive" AND "Active" AND "Yes" at the same time.

Here is one way to do it.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Ref	Status					List
2	15/01/2019	#111	Yes					#111
3	14/02/2018	#222	No					#333
4	27/03/2019	#333	Active					#444
5	30/01/2019	#444	Reactive
6	12/01/2019	#555	No
7
Date Yes (2)

 

[mattbnorris]

There is no practical limit to the number of conditions, however when multiplying the conditions like that it acts like AND. Of course a value in C2:C6 cannot be "Reactive" AND "Active" AND "Yes" at the same time.

Here is one way to do it.

Date Yes (2)

	A	B	C	D	E	F	G	H
Ref	Status					List
#111	Yes					#111
#222	No					#333
#333	Active					#444
#444	Reactive
#555	No

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:83px;"><col style="width:47px;"><col style="width:57px;"><col style="width:16px;"><col style="width:16px;"><col style="width:16px;"><col style="width:16px;"><col style="width:56px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]Date[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]15/01/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]14/02/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]27/03/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]30/01/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]12/01/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula H2 =IFERROR(INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(ISNUMBER(MATCH(C$2:C$6,{"Reactive","Active","Yes"},0)))*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2))),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

You clever clever man! Thank you so much!