5 Replies Latest reply: Jul 24, 2013 8:53 AM by macmikey
macmikey Level 1 (20 points)

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/1/13Les Miserables
1/2/13The Hunchback of Notre Dame
1/3/13Les Miserables
1/4/13The Phantom of the Opera
1/5/13Don Quixote
1/6/13The Hunchback of Notre Dame
1/7/13War and Peace
1/8/13Les Miserables
1/9/13Les Miserables
1/10/13The Phantom of the Opera
1/11/13Les Miserables
1/12/13Don Quixote
1/14/13The Hunchback of Notre Dame
1/15/13Les Miserables



In a separate table, I want to be able to pull the data this way, from most recent (Date1) to least (Date4):



Don Quixote1/12/131/5/13

Les Miserables1/15/131/11/131/9/131/8/13
The Hunchback of Notre Dame1/14/131/6/131/2/13
The Phantom of the Opera1/10/131/4/13

War and Peace1/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?

MacBook Pro, Mac OS X (10.6.8)
  • Yellowbox Level 6 (8,510 points)

    Hi macmikey,


    Another way is to sort Table 1 by the BookRead column. You can always resort by the Date column.




  • macmikey Level 1 (20 points)

    Hi Ian,


    The size of the tables I'm working with (the actual tables themselves have much more information and way more records) makes simple sorting/filtering impractical. In addition, I'm REALLY picky about how results of this query (I really think I'm dealing with a database issue here) will be presented. Thanks for the thought, though.



  • Hiroto Level 5 (6,480 points)



    Here's something you may try. Not sure at all, though, whether this works acceptably fast for thousands of entries.


    Table 2 will retieve unique book titles from Table 1 by using row indices in Table 1 :: E. If you're using independent table with unique book titles to retrieve data from Table 1, Table 1 :: E is not necessary and you can simply replace the formulae in Table 2 :: A with the unique book titles.


    Table 1 works as follows.


    1) In column C, count the occurence of each book title in column B after the current date in column A.


    2) In column D, build string TITLE[k], where TITLE = title in column A, k = number of occurence in column C.


    3) In column E, retrive the row indices where k = 1 in column C (i.e., row indices of most recently read books).



    Table 2 works as follows.


    1) In column A, retrieve the (unique) book titles in rows whose indices are listed in Table 1 :: E.


    2) In column B, retiieve date in Table 1 :: A for TITLE[1] in Table 1 :: D, where TITLE = title in column A.


    3) In column C, retiieve date in Table 1 :: A for TITLE[2] in Table 1 :: D, where TITLE = title in column A.


    4) In column D, retiieve date in Table 1 :: A for TITLE[3] in Table 1 :: D, where TITLE = title in column A.


    5) In column E, retiieve date in Table 1 :: A for TITLE[4] in Table 1 :: D, where TITLE = title in column A.



    # Table 1


    A1  Date
    A2  2013-01-01
    A3  2013-02-01
    A4  2013-03-01
    A5  2013-04-01
    A6  2013-05-01
    A7  2013-06-01
    A8  2013-07-01
    A9  2013-08-01
    A10 2013-09-01
    A11 2013-10-01
    A12 2013-11-01
    A13 2013-12-01
    A14 2013-12-02
    A15 2013-12-03
    B1  BookRead
    B2  Les Miserables
    B3  The Hunchback of Notre Dame
    B4  Les Miserables
    B5  The Phantom of the Opera
    B6  Don Quixote
    B7  The Hunchback of Notre Dame
    B8  War and Peace
    B9  Les Miserables
    B10 Les Miserables
    B11 The Phantom of the Opera
    B12 Les Miserables
    B13 Don Quixote
    B14 The Hunchback of Notre Dame
    B15 Les Miserables
    C1  k
    C2  =COUNTIF(OFFSET($B$1,ROW($B2)-1,0,ROWS($B)-ROW($B2)+1,1),$B2)
    C3  =COUNTIF(OFFSET($B$1,ROW($B3)-1,0,ROWS($B)-ROW($B3)+1,1),$B3)
    C4  =COUNTIF(OFFSET($B$1,ROW($B4)-1,0,ROWS($B)-ROW($B4)+1,1),$B4)
    C5  =COUNTIF(OFFSET($B$1,ROW($B5)-1,0,ROWS($B)-ROW($B5)+1,1),$B5)
    C6  =COUNTIF(OFFSET($B$1,ROW($B6)-1,0,ROWS($B)-ROW($B6)+1,1),$B6)
    C7  =COUNTIF(OFFSET($B$1,ROW($B7)-1,0,ROWS($B)-ROW($B7)+1,1),$B7)
    C8  =COUNTIF(OFFSET($B$1,ROW($B8)-1,0,ROWS($B)-ROW($B8)+1,1),$B8)
    C9  =COUNTIF(OFFSET($B$1,ROW($B9)-1,0,ROWS($B)-ROW($B9)+1,1),$B9)
    C10 =COUNTIF(OFFSET($B$1,ROW($B10)-1,0,ROWS($B)-ROW($B10)+1,1),$B10)
    C11 =COUNTIF(OFFSET($B$1,ROW($B11)-1,0,ROWS($B)-ROW($B11)+1,1),$B11)
    C12 =COUNTIF(OFFSET($B$1,ROW($B12)-1,0,ROWS($B)-ROW($B12)+1,1),$B12)
    C13 =COUNTIF(OFFSET($B$1,ROW($B13)-1,0,ROWS($B)-ROW($B13)+1,1),$B13)
    C14 =COUNTIF(OFFSET($B$1,ROW($B14)-1,0,ROWS($B)-ROW($B14)+1,1),$B14)
    C15 =COUNTIF(OFFSET($B$1,ROW($B15)-1,0,ROWS($B)-ROW($B15)+1,1),$B15)
    D1  book[k]
    D2  =B2&"["&C2&"]"
    D3  =B3&"["&C3&"]"
    D4  =B4&"["&C4&"]"
    D5  =B5&"["&C5&"]"
    D6  =B6&"["&C6&"]"
    D7  =B7&"["&C7&"]"
    D8  =B8&"["&C8&"]"
    D9  =B9&"["&C9&"]"
    D10 =B10&"["&C10&"]"
    D11 =B11&"["&C11&"]"
    D12 =B12&"["&C12&"]"
    D13 =B13&"["&C13&"]"
    D14 =B14&"["&C14&"]"
    D15 =B15&"["&C15&"]"
    E1  i[k=1]
    E2  =MATCH(1,$C,0)
    E3  =IFERROR(E2+MATCH(1,OFFSET($C$1,E2,0,ROWS($C)-E2,1),0),"")
    E4  =IFERROR(E3+MATCH(1,OFFSET($C$1,E3,0,ROWS($C)-E3,1),0),"")
    E5  =IFERROR(E4+MATCH(1,OFFSET($C$1,E4,0,ROWS($C)-E4,1),0),"")
    E6  =IFERROR(E5+MATCH(1,OFFSET($C$1,E5,0,ROWS($C)-E5,1),0),"")
    E7  =IFERROR(E6+MATCH(1,OFFSET($C$1,E6,0,ROWS($C)-E6,1),0),"")
    E8  =IFERROR(E7+MATCH(1,OFFSET($C$1,E7,0,ROWS($C)-E7,1),0),"")
    E9  =IFERROR(E8+MATCH(1,OFFSET($C$1,E8,0,ROWS($C)-E8,1),0),"")
    E10 =IFERROR(E9+MATCH(1,OFFSET($C$1,E9,0,ROWS($C)-E9,1),0),"")
    E11 =IFERROR(E10+MATCH(1,OFFSET($C$1,E10,0,ROWS($C)-E10,1),0),"")
    E12 =IFERROR(E11+MATCH(1,OFFSET($C$1,E11,0,ROWS($C)-E11,1),0),"")
    E13 =IFERROR(E12+MATCH(1,OFFSET($C$1,E12,0,ROWS($C)-E12,1),0),"")
    E14 =IFERROR(E13+MATCH(1,OFFSET($C$1,E13,0,ROWS($C)-E13,1),0),"")
    E15 =IFERROR(E14+MATCH(1,OFFSET($C$1,E14,0,ROWS($C)-E14,1),0),"")



    # Table 2


    A1  book
    A2  =IFERROR(INDEX(Table 1::$B,Table 1::E2,1),"")
    A3  =IFERROR(INDEX(Table 1::$B,Table 1::E3,1),"")
    A4  =IFERROR(INDEX(Table 1::$B,Table 1::E4,1),"")
    A5  =IFERROR(INDEX(Table 1::$B,Table 1::E5,1),"")
    A6  =IFERROR(INDEX(Table 1::$B,Table 1::E6,1),"")
    A7  =IFERROR(INDEX(Table 1::$B,Table 1::E7,1),"")
    A8  =IFERROR(INDEX(Table 1::$B,Table 1::E8,1),"")
    A9  =IFERROR(INDEX(Table 1::$B,Table 1::E9,1),"")
    B1  date[i[k=1]]
    B2  =IFERROR(INDEX(Table 1::$A,MATCH($A2&"[1]",Table 1::$D,0),0),"")
    B3  =IFERROR(INDEX(Table 1::$A,MATCH($A3&"[1]",Table 1::$D,0),0),"")
    B4  =IFERROR(INDEX(Table 1::$A,MATCH($A4&"[1]",Table 1::$D,0),0),"")
    B5  =IFERROR(INDEX(Table 1::$A,MATCH($A5&"[1]",Table 1::$D,0) ,0) ,"")
    B6  =IFERROR(INDEX(Table 1::$A,MATCH($A6&"[1]",Table 1::$D,0) ,0) ,"")
    B7  =IFERROR(INDEX(Table 1::$A,MATCH($A7&"[1]",Table 1::$D,0) ,0) ,"")
    B8  =IFERROR(INDEX(Table 1::$A,MATCH($A8&"[1]",Table 1::$D,0) ,0) ,"")
    B9  =IFERROR(INDEX(Table 1::$A,MATCH($A9&"[1]",Table 1::$D,0) ,0) ,"")
    C1  date[i[k=2]]
    C2  =IFERROR(INDEX(Table 1::$A,MATCH($A2&"[2]",Table 1::$D,0),0),"")
    C3  =IFERROR(INDEX(Table 1::$A,MATCH($A3&"[2]",Table 1::$D,0),0),"")
    C4  =IFERROR(INDEX(Table 1::$A,MATCH($A4&"[2]",Table 1::$D,0),0),"")
    C5  =IFERROR(INDEX(Table 1::$A,MATCH($A5&"[2]",Table 1::$D,0),0),"")
    C6  =IFERROR(INDEX(Table 1::$A,MATCH($A6&"[2]",Table 1::$D,0) ,0) ,"")
    C7  =IFERROR(INDEX(Table 1::$A,MATCH($A7&"[2]",Table 1::$D,0) ,0) ,"")
    C8  =IFERROR(INDEX(Table 1::$A,MATCH($A8&"[2]",Table 1::$D,0) ,0) ,"")
    C9  =IFERROR(INDEX(Table 1::$A,MATCH($A9&"[2]",Table 1::$D,0) ,0) ,"")
    D1  date[i[k=3]]
    D2  =IFERROR(INDEX(Table 1::$A,MATCH($A2&"[3]",Table 1::$D,0),0),"")
    D3  =IFERROR(INDEX(Table 1::$A,MATCH($A3&"[3]",Table 1::$D,0),0),"")
    D4  =IFERROR(INDEX(Table 1::$A,MATCH($A4&"[3]",Table 1::$D,0),0),"")
    D5  =IFERROR(INDEX(Table 1::$A,MATCH($A5&"[3]",Table 1::$D,0) ,0) ,"")
    D6  =IFERROR(INDEX(Table 1::$A,MATCH($A6&"[3]",Table 1::$D,0) ,0) ,"")
    D7  =IFERROR(INDEX(Table 1::$A,MATCH($A7&"[3]",Table 1::$D,0) ,0) ,"")
    D8  =IFERROR(INDEX(Table 1::$A,MATCH($A8&"[3]",Table 1::$D,0) ,0) ,"")
    D9  =IFERROR(INDEX(Table 1::$A,MATCH($A9&"[3]",Table 1::$D,0) ,0) ,"")
    E1  date[i[k=4]]
    E2  =IFERROR(INDEX(Table 1::$A,MATCH($A2&"[4]",Table 1::$D,0),0),"")
    E3  =IFERROR(INDEX(Table 1::$A,MATCH($A3&"[4]",Table 1::$D,0) ,0) ,"")
    E4  =IFERROR(INDEX(Table 1::$A,MATCH($A4&"[4]",Table 1::$D,0),0),"")
    E5  =IFERROR(INDEX(Table 1::$A,MATCH($A5&"[4]",Table 1::$D,0) ,0) ,"")
    E6  =IFERROR(INDEX(Table 1::$A,MATCH($A6&"[4]",Table 1::$D,0) ,0) ,"")
    E7  =IFERROR(INDEX(Table 1::$A,MATCH($A7&"[4]",Table 1::$D,0) ,0) ,"")
    E8  =IFERROR(INDEX(Table 1::$A,MATCH($A8&"[4]",Table 1::$D,0) ,0) ,"")
    E9  =IFERROR(INDEX(Table 1::$A,MATCH($A9&"[4]",Table 1::$D,0) ,0) ,"")



    Hope this may help,


  • Badunit Level 6 (11,515 points)

    How about the idea shown below. Note that it would be simpler to have the dates summarized in the opposite order.


    Screen Shot 2013-07-17 at 5.44.29 PM.png


    Formulas are

    Table 1 Cell C2 =B2&COUNTIF(OFFSET(B2,0,0,ROWS(Table 1)-ROW(B2)+1),B2)

    Table 1 Cell D2 =A2

    Fill down with both to complete the columns


    Table 2 Column 1 is manually typed in.

    Table 2 Cell B2 =IFERROR(VLOOKUP($A2&COLUMN()-1,Table 1 :: $C:$D,2,0),"")

    Fill across and fill down to complete the table


    The Table 2 formula uses VLOOKUP so that you can specify "exact match". The regular LOOKUP function will return "close" matches if there isn't an exact match. You want exact matches only.  It uses the COLUMN function so you can fill it to the right instead of having to hard-code the index number (2, 3, 4, etc) in each column. 


    Because VLOOKUP looks up values in the leftmost column of the range and returns values from columns to the right of that, Table 1 needed a copy of the date column to the right of the "Index" column.


    A simpler formula for Table 1 that results in the dates being listed in the opposite order would be


    Table 1 Cell C2 =B2&COUNTIF($B$2:B2,B2)


    You would, of course, hide columns C and D in Table 1 when it is all set up.  Because those columns have the same formula in every row, the formulas will be automatically included on new rows as you expand the table.


    Another alternative is to leave Table 1 with only the raw data and make a third table for the "Index" and "Copy of Date" columns. You would use that new table in your VLOOKUP formula.  If you've set it up like shown above, you can simply select then "lift" and drop those two columns out of Table 1 and onto a blank spot to create a new table from them.  All related formulas are supposed to update to reflect this new arrangement but it didn't quite work that way when I did it. I had to manually edit the VLOOKUP formula in Table 2. You would give this new table enough rows so it will always be as long or longer than the data table (Table 1). Don't worry about any error flags (which you will get if the table is longer than Table 1), they will have no effect on the results

  • macmikey Level 1 (20 points)

    Yes, this did it. I like the simplicity of this solution. I agree the second formula for Table 1 would be somewhat easier, but I really need to see the most recent four rows, not the first four. And I have broken the lookups into a separate table. Very elegant--thanks so much!