Vlookup with multiple results

[dsrt16]

I am creating a database with authors, book titles, descriptions, and prices. When I type in a specific authors name in cell C3 I need all the titles in the datbase to appear. I know how to get one to do so, but not all of them.

=VLOOKUP(C3,Book List!A5:D563,2,FALSE) will return one of the book titles.

I have a table set up on the Book List worksheet with author, book title, desciption, and price. I have it set up where I only type the authors name once in the first column then in the second column I list their book titles. After listing their titles then I move on to the next author like this:

Then when I enter Name of Author in cell C3 I want cell C5 to list all the books of that author. (aka A fun title, Another Title, Yeah).
However with the formula I have above it will just list the first title. (aka A fun title)

Edited by NateO: Tried to clean up the table.

Edited by NateO, again: Nope, not working. Deleted the table. You might be interested in the HTML Maker, here:

http://www.puremis.net/excel/downloads.shtml

 

[dsrt16]

Thanks it worked GREAT!

 

[mkofler]

NateO's code works for me until I have more than 5462 rows. How would I have ~11,000 rows - how can I make this work?

Or, if someone wants to take a shot at the whole problem:
My goal is similar to the original one here, although it's jsut the first step for me. Once I have the column with the info listed like NateO's code will output (they are dates in my case; so far I was planning to use this code once for the admit date column, and then again for the release date column), I plan to extract each to it's own cell, then get the difference between each set of admit/release dates and sum them - essentially, each name may have come through the program from 1 to 20+ times, for varying lengths each time. I want to know the total number of days they've spent here.
I can move around columns, but essentially what I have are columns with the following headings, each having data in about 11,000 rows (sorry - I can't install software on this computer, so no HTML Maker pic):
Name, Booked, Released.
These columns are in Sheet 1 of a file called JTrac File.xls; I will be referencing them from a file called MHPTR.xls.
Thank you!
M

 

[NateO]

Well, based on your post, I think you're using XL 2000, seeing as Join() works, but you're tripping on trying to Transpose an array with more than 5461 elements.

There's not much getting around this based on how I'm doing this, so, it might be loop-time. See/try Foo2():

Public Function foo2( _
    ByRef rngIn As Range, ByRef nameIn As String) As String
Dim tmpArr() As Variant, newArr() As String
Dim i As Long, j As Long
Let tmpArr = rngIn.Value
ReDim newArr(1 To UBound(tmpArr, 1))
For i = LBound(tmpArr, 1) To UBound(tmpArr, 1)
    If tmpArr(i, 1) = nameIn Then
        Let j = j + 1
        Let newArr(j) = tmpArr(i, 2)
    End If
Next
ReDim Preserve newArr(1 To j)
Let foo2 = Join$(newArr, ", ")
End Function

I'm using Excel '03 and both worked for me on ~10,000 rows, I think Foo2() should work for you.

Book3
	A	B	C	D
1	Title		Name In	Return
2	Joe	Book1	Harold	Book3, Book7, Book9, Book33
3	Tom	Book2	Harold	Book3, Book7, Book9, Book33
4	Harold	Book3
5	Howard	Book4
6	Joe	Book5
7	Tom	Book6
8	Harold	Book7
9	Howard	Book8
10	Harold	Book9
Sheet1

And, not all is lost, Foo2() might be slightly faster, actually. I didn't test them, I did but not with enough resolution to distinguish between the two, but it might be...

 

[mkofler]

Rock! Thanks NateO! All is now well with the world, thanks once again to Mr. Excel!

 

[muro]

Hi, can you transform this code to work in rows rather than in columns?
Thanks!

Public Function foo( _
ByRef rngIn As Range, ByRef nameIn As String) As String

Dim tmpArr() As Variant

With WorksheetFunction
Let tmpArr = .Transpose( _
Evaluate("" & rngIn.Columns(1).Address & "&" _
& rngIn.Columns(2).Address & ""))
Let foo = .Substitute(Join(Filter( _
tmpArr, nameIn), ", "), nameIn, vbNullString)
End With
End Function

 

[NateO]

Hello and welcome to the board!

Yes, but you should use/start with foo2() for 2 reasons, first, for allowing larger ranges w/ Excel 2000. Second, my associate, Martin correctly notes that if the returned value has the same string as the lookup value in it, the returned string gets incorrectly truncated, something I failed to consider, actually. Probably remote, but possible...

Foo2() works as intended in all cases, I think, and doesn't appear to suffer any efficiency loss (probably because foo() has some expensive stuff going on it, which I knew when I wrote it...).

With that said, here's Foo3():

Public Function foo3( _
    ByRef rngIn As Range, ByRef nameIn As String) As String
Dim tmpArr() As Variant, newArr() As String
Dim i As Long, j As Long
Let tmpArr = rngIn.Value
ReDim newArr(1 To UBound(tmpArr, 2))
For i = LBound(tmpArr, 2) To UBound(tmpArr, 2)
    If tmpArr(1, i) = nameIn Then
        Let j = j + 1
        Let newArr(j) = tmpArr(2, i)
    End If
Next
ReDim Preserve newArr(1 To j)
Let foo3 = Join$(newArr, ", ")
End Function

Book1
	A	B	C	D	E	F	G	H	I
1	Harold	Aladin	Juan	MOB	Bill	Aladin	Harold	Jon	Harold
2	Book1	Book2	Book3	Book4	Book5	Book6	Book7	Book8	Book9
3
4	Harold	Aladin
5	Book1, Book7, Book9	Book2, Book6
Sheet1

Again, I've given this limited thought/testing, so if something I haven't considered pops up, please post back to this thread.

 

[MrDeeds]

Data on another sheet

Hi, the code works great! However, if I refer the formula to a data set on another sheet the result is a number?....Is there a fix to allow using data set on another sheet?...Thanks!

 

[NateO]

Hello, welcome to the board.

The following works fine for me:

Public Function foo2( _
    ByRef rngIn As Range, ByRef nameIn As String) As String
Dim tmpArr() As Variant, newArr() As String
Dim i As Long, j As Long
Let tmpArr = rngIn.Value
ReDim newArr(1 To UBound(tmpArr, 1))
For i = LBound(tmpArr, 1) To UBound(tmpArr, 1)
    If tmpArr(i, 1) = nameIn Then
        Let j = j + 1
        Let newArr(j) = tmpArr(i, 2)
    End If
Next
ReDim Preserve newArr(1 To j)
Let foo2 = Join$(newArr, ", ")
End Function

Book4
	A	B	C	D
1	Harold	book1, book33
Sheet1

Book4
	A	B	C	D
1	Harold	book1
2	tom	book2
3	Harold	book33
Sheet2

And you?

 

[NateO]

Could this be the problem:

Second, my associate, Martin correctly notes that if the returned value has the same string as the lookup value in it, the returned string gets incorrectly truncated, something I failed to consider, actually. Probably remote, but possible...

if you have foo in column 1 and foo1 in column 2, 1 will be returned, due to the way I used the Substitute() function in the original UDF.

See foo2().

 

[scullystwin42]

Return these results in a column

Hi -

I am new here, and in desperate need of help. I'm not a programmer, but got roped into making a rather complex excel workbook for my job. It's requiring more vb than i know. I'm using your previous script (foo2) as the basis for returning multiple values from a lookup table, but I can't seem to figure out how to output the values to a column of cells, each value to a column, instead of to a string in one cell. I've been searching all over to try and make it work.

by the way, your Foo2 script was a lifesaver; i tried dozens of ways to get the multiple values from one lookup thing to work, this was the only reliable one.

Please help!