>= in match formula with multiple criteria

[eggman2001]

Let's say I have a date in A1 and dates in B:C where the date in column B is always smaller than the date in column B.

I'd like to find the row in B:C where A1 >= B and A1 <= C. I'm thinking that I should use something like:
match(A1&A1, ">="&B:B&"<="&C:C, 0) although I know the second argument isn't structured correctly.

Can someone tell me the correct structure here?

 

[Fowmy]

Hi

Try this array formula:

=MATCH(1,(A1>=B:B)*(A1<=C:C),0)

Confirm with Ctrl+Shift+Enter

 

[eggman2001]

Hi

Try this array formula:

=MATCH(1,(A1>=B:B)*(A1<=C:C),0)

Confirm with Ctrl+Shift+Enter

I'm not familiar with the first 2 arguments there. Would you be able to explain to me or paste a link to a resource?

 

[Peter_SSs]

1. Are there 'gaps' in the column B:C dates? What I mean by that: Is it possible that a date in cell A1 does not fall into any of the date ranges in B:C?

2. Are the dates in column B sorted ascending? Or could they be?

 

[eggman2001]

1. Are there 'gaps' in the column B:C dates? What I mean by that: Is it possible that a date in cell A1 does not fall into any of the date ranges in B:C?

2. Are the dates in column B sorted ascending? Or could they be?

1 - There are no gaps. The date in cell A1 will fall into one of the date ranges in B:C

2 - The dates in B:C are descending and do not overlap

 

[Peter_SSs]

2 - The dates in B:C are descending and do not overlap

Could they be ascending as asked in my last post? If so, I think this is what you are asking.

Excel Workbook
	B	C	D	E	F
1	Date 1	Date 2		Date of Interest:	19-Mar-11
2	01-Jan-11	15-Jan-11		Row:	7
3	16-Jan-11	30-Jan-11
4	31-Jan-11	14-Feb-11
5	15-Feb-11	01-Mar-11
6	02-Mar-11	16-Mar-11
7	17-Mar-11	31-Mar-11
8	01-Apr-11	15-Apr-11
9	16-Apr-11	30-Apr-11
10	01-May-11	15-May-11
Date Row