Solved

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

Posted on 2012-03-30
12
282 Views
Last Modified: 2012-04-05
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
0
Comment
Question by:pabrann
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
12 Comments
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 250 total points
ID: 37789588
=VLOOKUP(MAX(B:B)-1,B:C,2,TRUE)

Kevin
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 250 total points
ID: 37789685
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
0
 

Author Comment

by:pabrann
ID: 37789726
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pabrann
ID: 37789767
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
0
 
LVL 81

Expert Comment

by:byundt
ID: 37789780
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
0
 

Author Comment

by:pabrann
ID: 37791045
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
0
 

Author Comment

by:pabrann
ID: 37791050
Sorry, I think I forgot to actually attach the file.
PBDT-Test-20120331g.zip
0
 
LVL 81

Expert Comment

by:byundt
ID: 37791112
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
0
 

Accepted Solution

by:
pabrann earned 0 total points
ID: 37791146
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
0
 

Author Comment

by:pabrann
ID: 37791247
A special thanks to you Brad for your expert clarifications and extra effort!
0
 

Author Closing Comment

by:pabrann
ID: 37810081
Multiple possibilities and they all work. Thanks so much!

Phil
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Database 5 45
Filter an Excel list by multiple criteria 6 39
what program/tool is used to create charts like this? 4 32
Calculate number of months in Excel 13 26
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question