Assign a week commencing date to a random date

Hi All,

With reference to the attached, on tab 2 I have a list of random dates in column G.

On tab 1, I have a horizontal array of week commencing dates.

In column F of tab 2, I need a formula that assigns a week commencing date to the random date.

The rule is that if the random date in column G is equal to or greater than a given week commencing date, but less than the next week commencing date, it should be assigned the former date.

Thanks in advance.

Try

=MATCH(G7,'Tab 1'!$D$7:$BD$7,1)

Hi John,

This isn't generating the correct information. If I generate the formula in date format, the month and year always show as 01 and 1900 respectively, although the day does change (seemingly referencing the number of the week in the array, rather than anything else).

If you want date (rather than week no 1,2 ...))

=INDEX('Tab 1'!$D$7:$BD$7,MATCH(G7,'Tab 1'!$D$7:$BD$7,1))

or use Neil's answer

Thanks Neil that worked perfectly. I had forgotten about this function actually.

My week commencing dates are always Mondays - in the upload I had simply started with the first day of the year, so in my real version I have used the return type 1 instead of 15 and changed my array accordingly.

Thanks again

Thanks again John. This second solution generates the desired result and as it uses the first array as the reference, ensures consistency between the two tabs even when changes are made, so think I'll use this one.

Thank you both Neil and John.