if condition (date range) met in one column, returning values in adjacent columns

[CR1989]

Hi

i have the following:

B1 C1 D1
[TABLE="width: 196"]
<tbody>[TR]
[TD="align: right"]21/08/2013[/TD]
[TD]Chelsea[/TD]
[TD]Aston Villa[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Aston Villa[/TD]
[TD]Liverpool[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Everton[/TD]
[TD]West Brom[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Fulham[/TD]
[TD]Arsenal[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Hull[/TD]
[TD]Norwich[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Newcastle[/TD]
[TD]West Ham[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Southampton[/TD]
[TD]Sunderland[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Stoke[/TD]
[TD]Crystal Palace[/TD]
[/TR]
[TR]
[TD="align: right"]25/08/2013[/TD]
[TD]Cardiff[/TD]
[TD]Man City[/TD]
[/TR]
[TR]
[TD="align: right"]25/08/2013[/TD]
[TD]Tottenham[/TD]
[TD]Swansea[/TD]
[/TR]
[TR]
[TD="align: right"]26/08/2013[/TD]
[TD]Man United[/TD]
[TD]Chelsea[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

i want to analyse the statistics of the games above by each round (ie 10 games) but as you can see the dates of each game are staggered so its difficult to isolate them by each round. as i want to do this over several years I want to avoid doing it manually.

Is threre any way that i could formulate a cell such that if the dates in column B fall into a certain date range (say 21/08<=21/08+6), that it could return the two values from column C and D? also it doesnt necessarily have to be by date, if i could split the data once one full combination of the twenty values is displayed that would work as well.

I could only come up with this so far =IF($B$2<=$B$2+6,C2,0) but this just returns the list of values in the original column that i have in the first place. any suggestions?

 

[cyrilbrd]

Hi, not sure to understand your query, you want to return the values from Column B and Column C for all that fit the date in between the 21st and the 27th of August?
Wouldn't a pivot table give you that kind of results?