return reference if in date period and 'yes'

mattbnorris

New Member
Joined
Sep 17, 2019
Messages
13
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
DateRefStatusJan-Mar 2019 & Yes
01/01/19#111Yes#111
23/01/19#444Yes#444
25/02/19#333No#222
01/01/19#222Yes#555
07/03/19#555Yes

<tbody>
</tbody>
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
=IF(AND(A2>=DATE(2019,1,1),A2<=DATE(2019,3,31),C2="Yes"),B2,"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,686
Office Version
365
Platform
Windows
Similar concept to this one I helped you with. :)

Excel Workbook
ABCDEFGH
1DateRefStatusStart Month1List
21/01/2019#111YesEnd Month3#111
323/01/2019#444YesStatusYes#444
425/02/2019#333No#222
51/01/2019#222Yes#555
67/03/2019#555Yes
Date Yes
 

mattbnorris

New Member
Joined
Sep 17, 2019
Messages
13
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,686
Office Version
365
Platform
Windows
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? ;)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,686
Office Version
365
Platform
Windows
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.
 
Last edited:

mattbnorris

New Member
Joined
Sep 17, 2019
Messages
13
Give it a try? ;)
Hello again! I got something to work however now I want multiple status conditions to be picked up
Example

DateRefStatusJan-Mar 19 "Active" & "Reactive" & "Yes"
15/01/19#111Yes#111
14/02/18#222No#333
27/03/19#333Active#444
30/01/19#444Reactive
12/01/19#555No

<tbody>
</tbody>


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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,686
Office Version
365
Platform
Windows
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
ABCDEFGH
1DateRefStatusList
215/01/2019#111Yes#111
314/02/2018#222No#333
427/03/2019#333Active#444
530/01/2019#444Reactive
612/01/2019#555No
7
Date Yes (2)
 

mattbnorris

New Member
Joined
Sep 17, 2019
Messages
13
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)

ABCDEFGH
1DateRefStatus List
215/01/2019#111Yes #111
314/02/2018#222No #333
427/03/2019#333Active #444
530/01/2019#444Reactive
612/01/2019#555No
7

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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!
 

Forum statistics

Threads
1,085,693
Messages
5,385,220
Members
401,936
Latest member
stephenpoff

Some videos you may like

This Week's Hot Topics

Top