Lookup a date in a range challenge

[hobson]

Hi

I've run into a bit of a challenge with dates. Essentially I'm have a bi-monthly calendar with date ranges appearing every two months for a 12 month period.

I have two tabs, the first (tab A) contains all the date ranges and an entry for a specific date that doesn't correspond to the range (it shouldn't in this instance). For example, see the table below, B4 contains the date Sun-06-Jul currently in the range of B2.

	A	B	C	D	E	F
1	July		August		September
2	Sun-06-Jul <tbody> </tbody>	Sun-13-Jul <tbody> </tbody>	Sun-03-Aug <tbody> </tbody>	Sun-17-Aug <tbody> </tbody>	Sun-07-Sep <tbody> </tbody>	Sun-14-Sep <tbody> </tbody>
3				Mon-28-Jul <tbody> </tbody>
4		Sun-06-Jul <tbody> </tbody>
5		Sun-06-Jul <tbody> </tbody>
6			Sun-06-Jul <tbody> </tbody>

<tbody>
</tbody>

The aim is on a separate tab (tab B) to show the date within the range to which it corresponds. For example, if(And(B4>=A2, B4<b2) then="" show="" the="" date="" in="" cell="" <em="">A4.<b2,b4,"") <="" em=""> In words the Sun-06-Jul (B4) should appear in the range to which it corresponds based on the rule that it is greater or equal to the start of that range and less than the start of the next range.

I've tried several ways of solving this using lookups, if statements and can only think of a valid solution using a nested if function which for each cell (e.g. A4 checks if the date in A4 on tab A sits within the range, if not, then it looks if B4 sits within the range and so on). Obviously the problem here is that this explodes and apart from excel not allowing such a large function, it is a poor solution.

Any thoughts on this would be greatly appreciated. I've exhausted my abilities.

Thanks.</b2,b4,"")></b2)>

 

[Rukt]

Hi hobson,

In your example, there's only one date per row. Is this necessarily the case for the rest of the data?

Cheers,
Rukt

 

[Rukt]

(Too late to edit previous post)

Because if that is the case, the following should illustrate a solution:

https://www.dropbox.com/s/ijcn8fzi3zchy8i/match dates in range.xlsx

It uses the following formula in cell A3 of Sheet2, copied & pasted to the rest of the range corresponding with Sheet1.

=IF(MATCH(SUM(Sheet1!$A3:$F3),$A$2:$F$2,1)=MATCH(A$2,$A$2:$F$2,0),SUM(Sheet1!$A3:$F3),"")

Rukt