Returning a date from a range

[pbyrne462]

I have a set of three criteria, Product, Country, Date

My data appears like this:

Product 1 Country 1 01JAN11
Product 2 Country 2 01APR11
Product 3 Country 3 01JUL11
Product 4 Country 4 01OCT11

I have a seperate table which I want to sequence the countries depending on the quarters, so I have 4 columns. I need to look at the table above speficically the dates and return them in sequence depending on if they are in quarter 1 to 4. So following the example above I would have a resulting table that contains Country 1 in Q1, Country 2 in Q2 etc.

Hope this makes sense and I hope someone can help.

 

[GlennUK]

The ROW()-ROW($F$2) part generates a number of 1 or 2 or 3 etc depending on how far down the output table the formula is.

The longer one: MATCH($F$1,$D$2:$D$14,0)+ROW()-ROW($F$2)-1)
is to make a pointer into the source table, offsetting from the first occurence of the required Quarter by 0 or 1, or 2, or 3 etc ... depending on how far down the output table the formula is ( note that this offset is one less than the other generated number of 1, 2 or 3, by design ).

 

[pbyrne462]

Thanks guys that has done it, much appreciated

Paul

 

[GlennUK]

That's great!