I have a sample dataset that contains investment returns from 20 different projects. The twenty projects are listed vertically. Each project lasted anywhere between 3 to 9 days. The dates are listed on the top row.
-In my first worksheet, "active days", I have data that tells me which days the investment was active. If the project was inactive on that date, the cell says "NA." If it was active, then it lists the "nth" day which it's been active.
-In my second worksheet, "gross return", I have data that tells me what the gross return was, for specific dates.
-In my third worksheet, "Return Standardized to Duration", I am trying to standardize my return data according to duration, so I can compare all the "Day 1 return" together vertically, and then compare the "Day 2 return" vertically, etc.
Essentially, for each cell in the third sheet, I want Excel to:
1. read the "Investment Location," in the first column
2. go to the first (leftmost) worksheet and grab the relevant "nth day" info
3. then I want it to use the "nth day" information, along with the corresponding specific date, (i.e. 1/5/1993) go to the second worksheet
4. grab the gross return that corresponds to the date
In my sample spreadsheet, third worksheet, I've filled in two rows of data by hand, Atlanta and San Paolo, as examples. (they're highlighted in light orange) Ideally, I would like a smart formula to do this because my real data set is really really big.
I've been trying to do a combination of hlookup with vlookup, with little success.
I do not know how to code or do macros. I would appreciate any ideas.