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

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

Originally Posted by mattbnorris
You clever clever man! Thank you so much!
You're very welcome. Thanks for the kind words.

2. ## 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

Hi Peter,

Currently revisiting this query

I now want to create this into one big list of with different date periods after each other in the list. So i've started with a nested if statement where i'm using your formula (as quoted above) based on how many records i've found in a date range. For example...

 IDs Q1 (Jan-Mar) Q2 (Apr-Jun) Q3 (Jul-Sep) Q4 (Oct-Dec) #111 Jan 3 2 1 0 #333 Mar #444 Jan #777 Apr #999 Jun

Okay under the quarter headings i'm counting the amount of times it finds a record in that time period and then to get the list i'm using a nested IF function with the formula previously provided

=IF(\$E\$4+2=ROW(A1),IF(\$E\$4+\$F\$4+2=ROW(A1),[...continued...],Aggregate_Data_Formula(01/04/19 - 30/06/19)),Aggregate_Data_Formula(01/01/19 - 31/03/19))

Dragging this down works until it gets to the changing point - so in my example above it returns the first 3 values that are part of Q1 but then won't return further values. The issue is due to the ROW formula in previously provided formula:

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

If i'm nesting the formulas in IF statements as I propose then the ROW part at the end of formula above needs to also include the reference to the Q1 count; as in it would need to start at ROWS(A\$5:A5) in order to provide the right IDs.

I know that's long winded, apologies if you don't understand.

Thanks,
Matt

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

Are those month abbreviations in column B ..

- Actual Dates with cells formatted as mmm, or
- Text Values

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

Originally Posted by Peter_SSs
Are those month abbreviations in column B ..

- Actual Dates with cells formatted as mmm, or
- Text Values

It was just a quick notation for the table on here, in my actual sheet I have actual dates. But its fine, I managed to figure my problem out actually...

=ROW(INDIRECT(E\$4+F\$4+2&":A"ROW(A1))

and dragging this down allows me to get the required outcome.

Thanks