Is there a way to specify a number of vlookup results?

Hi macmikey,

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

Regards,

Ian.

Hello

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,

H

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

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