We have a table of business dates, excluding holidays and weekends, which includes stock closing prices. We have another table with dates and we are trying to find the closing price on the preceeding business day. We have been trying to use the Vlookup function, but it isn't working in all cases. If the prior day is a weekend or holiday, the formula returns an #N/A. It can't find the date in the first table.
Does anyone know a solution to this problem?
If you have the analysis toolpak addin installed (Tools|Addins)
Then the Workday() function should get you what you need.
e.g.where A2 contains lookup date (if it is not in your tablerange lookup table, then it will look for previous business day. "holidays" is your list of holidays to exclude.
Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
In which order is your list of dates? If they're sorted ascending (i.e. with oldest at the top) then you can just use your original VLOOKUP but use TRUE or 1 as the final argument, e.g.
=VLOOKUP(D1-1,A1:B2000,2,TRUE)
where you want a match for the business day preceding D1...or if they're not sorted that way try
=VLOOKUP(WORKDAY(D1,-1,holidays),A1:B2000,2,0)
where holidays is a named range containing your holiday dates.
Note:WORKDAY function is part of Analysis ToolPak add-in
Thank you both for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks