Lookup date with largest value between 2 dates

[wallstudio]

I have worksheet with 2 columns. Column A are date in ascending order. Column B are numbers. C1 and C2 are dates for me to input. C3 is the result.

I need to find the largest number in column B which is between dates of C1 and C2 and return the corresponding date as result.

Have been thinking for a while and can't figure it out. Can anyone help? Thanks a lot.

 

[Expiry]

This works, though I'm sure there's a better and less complicated way to do it.

It doesn't account for duplication of the largest number, so maybe you need to figure that out if that's a possibility.

I've gone down to row 24 in the formula, but change that to suit your range

=INDEX(A1:A24,MATCH(MAX(OFFSET(A1,MATCH(C1,A1:A24,0)-1,1,(C2-C1)+1,1)),B1:B24,0),1)

 

[jim may]

I think the (C2-C1)+1 portion of the formula is miscalculating;
The largest Number between the dates in C1 and C2 is on row 12 - dated 1/18/2011..

Excel Workbook
	A	B	C	D
1	1/16/2011	50	1/17/2011	<< Between
2	1/16/2011	100	1/18/2011	<< Between
3	1/16/2011	500	1/17/2011	<< The related Date of the Largest Number between C1 and C2
4	1/16/2011	25
5	1/17/2011	30
6	1/17/2011	325
7	1/17/2011	250
8	1/17/2011	50
9	1/17/2011	150
10	1/18/2011	31
11	1/18/2011	444
12	1/18/2011	951	<<???????????
13	1/18/2011	24
14	1/19/2011	3524
15	1/19/2011	21
16	1/19/2011	54
17	1/19/2011	9541
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C3	=INDEX(A:A,MATCH(MAX(OFFSET(A1,MATCH(C1,A:A,0)-1,1,(C2-C1)+1,1)),B:B,0),1)

 

[Expiry]

I think the (C2-C1)+1 portion of the formula is miscalculating;

That bit is just to ensure the dates are inclusive. 17/01 - 16/01 is 1, so the +1 is making sure that both days are counted.

It didn't miscalculate for me.

 

[Expiry]

I think the (C2-C1)+1 portion of the formula is miscalculating;
The largest Number between the dates in C1 and C2 is on row 12 - dated 1/18/2011..

Excel Workbook
	A	B	C	D
1	1/16/2011	50	1/17/2011	<< Between
2	1/16/2011	100	1/18/2011	<< Between
3	1/16/2011	500	1/17/2011	<< The related Date of the Largest Number between C1 and C2
4	1/16/2011	25
5	1/17/2011	30
6	1/17/2011	325
7	1/17/2011	250
8	1/17/2011	50
9	1/17/2011	150
10	1/18/2011	31
11	1/18/2011	444
12	1/18/2011	951	<<???????????
13	1/18/2011	24
14	1/19/2011	3524
15	1/19/2011	21
16	1/19/2011	54
17	1/19/2011	9541
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C3	=INDEX(A:A,MATCH(MAX(OFFSET(A1,MATCH(C1,A:A,0)-1,1,(C2-C1)+1,1)),B:B,0),1)

Just looking at your example, there. My formula obviously doesn't like it when dates are repeated. It works fine when there's just one entry per day, though, which is what I assumed would be the case.

 

[Peter_SSs]

I haven't come up with a sensible formula myself yet but I don't think either of the 2 suggestions so far work. Note that my dates are d/mm/yyyy

Excel Workbook
	A	B	C	D
1			4/04/2011
2	1/04/2011	5	9/04/2011
3	2/04/2011	69	2/04/2011	Not between C1 and C2 dates
4	3/04/2011	4
5	4/04/2011	8
6	5/04/2011	5
7	6/04/2011	69
8	7/04/2011	3
9	8/04/2011	2
10	9/04/2011	5
11	10/04/2011	8
12	11/04/2011	6
13	12/04/2011	3
14
Largest between 2 dates Expiry

Excel Workbook
	A	B	C	D
1			4/04/2011
2	1/04/2011	5	9/04/2011
3	2/04/2011	69	2/04/2011	Not between C1 and C2 dates
4	3/04/2011	4
5	4/04/2011	8
6	5/04/2011	5
7	6/04/2011	69
8	7/04/2011	3
9	8/04/2011	2
10	9/04/2011	5
11	10/04/2011	8
12	11/04/2011	6
13	12/04/2011	3
14
Largest between 2 dates jim may

 

[jim may]

Peter, Mine was not a Suggestion -- I was questioning the original suggestion;
Note that I considered multiple dates - not sure if OP has multiple dates or not..
Jim

 

[Expiry]

Peter, I recognised that situation wasn't covered in my formula. Hopefully someone will be able to tweak it.

This problem sounds like it should be really simple.

 

[wallstudio]

First of all, thanks for all yours ideas. And Sorry guys! I didn't make it very clear at first. Dates are not repeated in column A.

Also, don't know if there is any difference for the formula if there is a header on the top row, cos I see offset function in the formula.

 

[Peter_SSs]

See if this is it. These are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Excel Workbook
	A	B	C	D
1			4/04/2011	69
2	1/04/2011	5	9/04/2011
3	2/04/2011	69	6/04/2011
4	3/04/2011	4
5	4/04/2011	8
6	5/04/2011	5
7	6/04/2011	69
8	7/04/2011	3
9	8/04/2011	2
10	9/04/2011	5
11	10/04/2011	8
12	11/04/2011	6
13	12/04/2011	3
14
Largest between 2 dates

This still doesn't cover the circumstance that the largest number in the date range occurs more than once in the date range as shown below.
1. Is that possible?
2. What result(s) would you want in that circumstance?

Excel Workbook
	A	B	C
1			4/04/2011
2	1/04/2011	5	9/04/2011
3	2/04/2011	69
4	3/04/2011	4
5	4/04/2011	8
6	5/04/2011	69
7	6/04/2011	69
8	7/04/2011	3
9	8/04/2011	69
10	9/04/2011	5
11	10/04/2011	8
12	11/04/2011	6
13	12/04/2011	3
14
Largest between 2 dates (2)