Dynamic Lookup with Multiple Results

[mrgreek]

Hi everyone,

A question for you. It might be best to just lay out the issue.

I have a column, A, with dates in chronological, ascending order. For the sake of argument, lets say column A starts from Jan 2009 and ends at today. In column B, I have prices. Column B may have duplicates.

Now, lets say in column E I have a date of March 3, 2009, and in column F I have a price of $4.34 (doesn't really matter what it is for the sake of argument). What I want to do is, determine the NEXT date from column A is that has the corresponding price that is greater than or equal to $4.34 AFTER March 3, 2009. But if we do a VLOOKUP, I would like it to be dynamic, such that, it always uses the same column E and F. This is because I will have other dates and prices in columsn E and F where I'd like to just copy paste the formula downwards.

If that isn't clear enough, please let me know!

Thanks,
Mrgreek

 

[Robert Mika]

Hi everyone,

A question for you. It might be best to just lay out the issue.

I have a column, A, with dates in chronological, ascending order. For the sake of argument, lets say column A starts from Jan 2009 and ends at today. In column B, I have prices. Column B may have duplicates.

Now, lets say in column E I have a date of March 3, 2009, and in column F I have a price of $4.34 (doesn't really matter what it is for the sake of argument). What I want to do is, determine the NEXT date from column A is that has the corresponding price that is greater than or equal to $4.34 AFTER March 3, 2009. But if we do a VLOOKUP, I would like it to be dynamic, such that, it always uses the same column E and F. This is because I will have other dates and prices in columsn E and F where I'd like to just copy paste the formula downwards.

If that isn't clear enough, please let me know!

Thanks,
Mrgreek

Excel 2010
	A	B	C	D	E	F	G
3	3/1/2009	5.0			1/4/2010	5	1/7/2010
4	4/1/2009	3.0			7/1/2009	1	8/1/2009
5	5/1/2009	43.0			1/3/2010	200	1/7/2010
6	6/1/2009	5.0
7	7/1/2009	3.0
8	8/1/2009	2.0
9	9/1/2009	456.0
10	10/1/2009	7.0
11	11/1/2009	8.0
12	12/1/2009	95.0
13	1/1/2010	3.0
14	1/2/2010	4.0
15	1/3/2010	68.0
16	1/4/2010	5.0
17	1/5/2010	3.0
18	1/6/2010	2.0
19	1/7/2010	4567.0
20	1/8/2010	8.0
21	1/9/2010	4.0
22	1/10/2010	25.0
23	1/11/2010	7.0
24	1/12/2010	4.0
Sheet1

Cell Formulas
Range	Formula
G3	{=SMALL(IF($A$3:$A$24>E3,IF($B$3:$B$24>=F3,$A$3:$A$24)),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mrgreek]

i used this

MIN(IF(($B$3:$B$14>F3)*($A$3:$A$14>=E3)*$A$3:$A$14>0,$A$3:$A$14))

and it worked, thanks!