Excel Question
Hi – I was hoping anybody could answer this question for me.
I have specific dates (about 400 dates total). I used a VLOOKUP to find the price corresponding to that date. If I want the prices for the five previous days prior to the event date, and the five following days, is there an easy way to reference a location directly 1 cell, 2 cells,3 cells etc below or above my date in the original table from where I got my VLOOKUP result, and then repeat this for 400 different dates.
Thank you for your help
use this functon with the vlookup. MATCH and ADDRESS are also useful for relative and absolute locations.
guru - thanks for the response. Unfortunately, I had tried this earlier, but excel doesnt let me do this - it won't accept it
You should be able to do the lookup without using another formula. Just modify your references to the initial lookup cell (e.g., vlookup(e$51 - 1, ...) or vlookup($e51 +5, ...)) while leaving the array and column references unchaged. Assuming your dates are formatted correctly, you won't have any problems.
Smugguy, thanks for the response. My problem there is that I need to take out weekends and holidays (only looking at trading days). If I use the vlookup like that, my dates go wrong. Is there a way to get around that, and still use the vlookup? The data that I have is only trading days, and I am hoping to look at all five previous trading days prior to an event, and five following days.
If the data is organized into trading days/required data, you can match the row in the required column (sorting the column appropriately), then offset as desired (ie. offset 0,1, 2, 3, 4), as guru stated. If not trading days (ie. just days), and from your vlookup you need to get only trading days the problem becomes more complicated. You need to define a trading day, for instance: is it a day when a certain market is closed, a day when libor on the day before isn't O/N, etc?
Assuming you have the trading days labeled, you can simply drop in a row of numbered trading days (1 thru 400) between each trading date and its respective price. Your first lookup should identify the numbered row for the date in question, while your next 11 lookups (-5,0 and +5 days) should reference the result of your first lookup (i.e., the trading day number), not the date itself.
I use a combination of match and index functions alot for things like this.
Match will give you the row number, then use index to give you the data:
INDEX(A:A,MATCH(B1,A:A,0)-5)
Tenetur pariatur aut sed optio distinctio repellendus sit. Sapiente accusantium placeat sequi iure. Dolorum minus mollitia quod atque neque totam numquam. Natus voluptatem ut quo. A adipisci nulla qui sit ullam. Nam quis suscipit sint vero. Ad nostrum recusandae voluptate occaecati.
Molestiae numquam maiores et ea aut quia officia. Et voluptatem et ullam officiis eos. Magnam labore itaque laborum sint et sunt ullam hic. Accusantium rem minus neque magnam ut. Et rerum non hic aut et.
Non magni ad nihil quo perferendis enim nihil. Provident dolores et provident perspiciatis. Fuga unde perspiciatis ducimus qui cupiditate corporis earum ipsa. Molestias veniam ut molestiae perspiciatis qui.
Unde minima est recusandae molestiae nihil. Ratione sed in corporis eos vel est. Ratione autem unde vitae voluptatem et deleniti.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...