# Dynamic Lookup with Multiple Results

#### mrgreek

##### New Member
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

Last edited:

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
ABCDEFG
33/1/20095.01/4/201051/7/2010
44/1/20093.07/1/200918/1/2009
55/1/200943.01/3/20102001/7/2010
66/1/20095.0
77/1/20093.0
88/1/20092.0
99/1/2009456.0
1010/1/20097.0
1111/1/20098.0
1212/1/200995.0
131/1/20103.0
141/2/20104.0
151/3/201068.0
161/4/20105.0
171/5/20103.0
181/6/20102.0
191/7/20104567.0
201/8/20108.0
211/9/20104.0
221/10/201025.0
231/11/20107.0
241/12/20104.0
Sheet1
Cell Formulas
RangeFormula
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.

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!

Replies
56
Views
793
Replies
11
Views
323
Replies
11
Views
685
Replies
7
Views
312
Replies
2
Views
96

1,196,080
Messages
6,013,315
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back