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)
Ut animi vero ut incidunt qui. Nisi fugiat et vero cupiditate atque.
Rem ut sint vel reprehenderit maiores exercitationem incidunt. Et nulla expedita atque distinctio non ut. Asperiores necessitatibus temporibus ipsa laborum ipsa. Illo quia consequatur qui in.
Laboriosam molestiae necessitatibus non qui qui similique omnis. Aut voluptatem autem aut laudantium earum in. Molestiae distinctio nam velit sit perspiciatis dolorum magnam. Numquam qui nam dolorum atque. Qui minus minus aut expedita. Ut omnis voluptas labore ut assumenda quae. Ea distinctio tempora necessitatibus odio et. Culpa minus impedit est amet facere veniam sequi.
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...