Create list of data if criteria is met

user_4934150 0
Viewed 91

I have the following simple Excel spreadsheet:

        A              B               C               D
1      CW02       2020-01-06          CW03        2020-01-13
2      CW02       2020-01-07                      2020-01-14
3      CW02       2020-01-08                      2020-01-15
4      CW02       2020-01-09                      2020-01-16
5      CW02       2020-01-10                      2020-01-17
6      CW02       2020-01-11                      2020-01-18
7      CW02       2020-01-12                      2020-01-19
8      CW03       2020-01-13
9      CW03       2020-01-14
10     CW03       2020-01-15
11     CW03       2020-01-16
12     CW03       2020-01-17
13     CW03       2020-01-18
14     CW03       2020-01-19
15
16

In Cell C1 I want to enter a calender week from Column A. In the example above CW03.
Based on this criteria all days which refer to this calender week in Column B should be listed in Column D.

With =VLOOKUP($C$1,$A:$B,2,FALSE) I am only able to extract the first value.

What formula do I need to get the list?

excel
1 Answers

If one has the Dynamic Array formula FILTER:

=FILTER(B:B,A:A=C1)

enter image description here

IF not put:

=IFERROR(INDEX(B:B,AGGREGATE(15,7,ROW($A$1:$A$14)/($A$1:$A$14=$C$1),ROW(ZZ1))),"")

And copy down.

enter image description here

If you data is always sorted on column A then you can use:

=IF(ROW(ZZ1)>COUNTIF(A:A,$C$1),"",INDEX(B:B,MATCH($C$1,A:A,0)+ROW(ZZ1)-1))

And copy down.

enter image description here

user_48515900
Related