I'm trying to make a kind of loose pivot-table-like date record lookup. Probably best if I show something analogous to what I'm wanting:
|1/2/13||The Hunchback of Notre Dame|
|1/4/13||The Phantom of the Opera|
|1/6/13||The Hunchback of Notre Dame|
|1/7/13||War and Peace|
|1/10/13||The Phantom of the Opera|
|1/14/13||The Hunchback of Notre Dame|
In a separate table, I want to be able to pull the data this way, from most recent (Date1) to least (Date4):
|The Hunchback of Notre Dame||1/14/13||1/6/13||1/2/13|
|The Phantom of the Opera||1/10/13||1/4/13|
|War and Peace||1/7/13|
The results in each row need to be the most recent four instances for that book(e.g., "Les Miserables" should only return the last four instances and disregard any more) and only the results for that book.
I've been trying to rack my brain on how to dynamically limit the range of rows for a VLOOKUP: that is, in each row of Table2:
- Date1: VLOOKUP for last instance as usual (which will return the bottom-most result)
- Date2: VLOOKUP for last instance prior to the row found in Date1
- Date3: VLOOKUP for last instance prior to the row found in Date2
- Date4: VLOOKUP for last instance prior to the row found in Date3
I've already tried category tables; it doesn't come close to what I want. Heck, VLOOKUP may not even be the way to go.
I'm concerned this will create an ENORMOUS amount of calculations, as the lookup table (Table2) I need to add this feature to currently has over 2700 rows, and the source table (Table1) it will compute from has over 1400. (That's right, I will be testing a TON of items in the first table that will have no instance at all in the second--that is, I want to test for way more "books" than are even used in the record. I know this seems backwards, but humor me.)
Is this doable, or am I delusional?