Want to find an Excel formula to lookup the value to the right of the next smallest date

I'm trying to create a formula similar to a vlookup that finds the next smallest occurance of a date and selects the value in the column to the right of the date found. I have attached an excel spreadsheet example to help explain. I'm using Excel 2010 with Windows 7.

Thanks!

Phil
Excel-Lookup-Example.xlsm
pabrannPresidentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
=VLOOKUP(MAX(B:B)-1,B:C,2,TRUE)

Kevin
byundtMechanical EngineerCommented:
Phil,
It looks as though you want to return the previous trading day's closing price. If so, you might modify Kevin's formula to:
=VLOOKUP(B11-1,B:C,2)                         put this formula in row 11

Brad
pabrannPresidentAuthor Commented:
Thanks Kevin, Your formula worked! Awesome. I did have a circular reference error so I inserted the row coordinates in the max(B:B) and that seemed to fix it.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

pabrannPresidentAuthor Commented:
Hi Brad,

Sorry, I didn't see that you had commented before I accepted Kevin's suggestion.

Your formula works also. Is there any way I can give you points for this as well?

Thanks so much...

Phil
byundtMechanical EngineerCommented:
Phil,
While the two formulas return the same result if you paste them in one cell only, they give different results if you copy them down a long column of data. Kevin's formula always returns the same answer. Mine will return a different answer depending on the date in column B.

Which behavior do you need? If the formula will only be applied in one cell, then Kevin posted first and deserves all the points. But if you do need different answers depending on the date in column B, then a split would be appropriate. I'd be glad to reopen the question in such a situation.

Brad
pabrannPresidentAuthor Commented:
Hi Brad,

I attached a copy of the actual spreadsheet involving this formula. It starts in range("AE3") and copied down to the last row. The formula is a modified version of Kevin's formula which yours may be able to replace. I have had some circular reference issues and also Excel has crashed a few times when I had too many records and tried to delete them.

This spreadsheet is a sheet used to backtest a daytrading system that I created. The system trades a maximum of three symbols per day and the number of shares is calculated with the formula in question (selecting the previous days portfolio balance, dividing by the number of maximum trades, and then dividing by the trigger price per share).

I will try your formula and see how it works. If you have any questions, please let me know.

Thanks so much, Phil
pabrannPresidentAuthor Commented:
Sorry, I think I forgot to actually attach the file.
PBDT-Test-20120331g.zip
byundtMechanical EngineerCommented:
Phil,
Because your dates in column AO are already sorted in ascending order, there is no need to take the MAX of an ever-growing range. You can therefore simplify the formula in cell AE3 to either of:
=IF(AD3>0,ROUND(VLOOKUP(AO3-1,AO:AP,2)/AJ$1/AD3,0),0)                   'TRUE is the default value of fourth parameter in VLOOKUP
=IF(AD3>0,ROUND(VLOOKUP(AO3-1,AO:AP,2,TRUE)/AJ$1/AD3,0),0)

I tested column AO to make sure that it was arranged in ascending order--it is. I also tested the suggested formula to make sure it returned the same answer as what you are using--it does.

Brad
pabrannPresidentAuthor Commented:
Brad,

Thanks so much for the clarification. I appreciate your expertise and am very satisified with these solutions. If you want to reopen the case and share points, I will be happy to do so.

Thanks again!

Phil

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pabrannPresidentAuthor Commented:
A special thanks to you Brad for your expert clarifications and extra effort!
pabrannPresidentAuthor Commented:
Multiple possibilities and they all work. Thanks so much!

Phil
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.