Thread: return reference if in date period and 'yes' Thanks:  1 Post #5344782 (1) Likes: 0

1. return reference if in date period and 'yes'

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
 Date Ref Status Jan-Mar 2019 & Yes 01/01/19 #111 Yes #111 23/01/19 #444 Yes #444 25/02/19 #333 No #222 01/01/19 #222 Yes #555 07/03/19 #555 Yes  Reply With Quote

2. Re: return reference if in date period and 'yes'

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

3. Re: return reference if in date period and 'yes'

Similar concept to this one I helped you with. Date Yes

 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

 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)>=F\$1)*(MONTH(A\$2:A\$6)<=F\$2)*(C\$2:C\$6="Yes")),ROWS(H\$2:H2))),"")

Excel tables to the web >> Excel Jeanie HTML 4  Reply With Quote

4. Re: return reference if in date period and 'yes'

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?  Reply With Quote

5. Re: return reference if in date period and 'yes' Originally Posted by 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?
Give it a try?   Reply With Quote

6. Re: return reference if in date period and 'yes' Originally Posted by Peter_SSs 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   Reply With Quote

7. Re: return reference if in date period and 'yes'

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.  Reply With Quote

8. Re: return reference if in date period and 'yes' Originally Posted by Peter_SSs Give it a try? Hello again! I got something to work however now I want multiple status conditions to be picked up
Example

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

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  Reply With Quote

9. Re: return reference if in date period and 'yes' Originally Posted by mattbnorris 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.

Date Yes (2)

 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

 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))),"")

Excel tables to the web >> Excel Jeanie HTML 4  Reply With Quote

10. Re: return reference if in date period and 'yes' Originally Posted by Peter_SSs 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 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

 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))),"")

Excel tables to the web >> Excel Jeanie HTML 4

You clever clever man! Thank you so much!  Reply With Quote

User Tag List

Tags for this Thread

#444, 01/01/19, assigned, date, return  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•