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

 

[just_jon]

There are VBA code solutions, or formula solutions.

Do you have a preference?

 

[dsrt16]

Whicever is easiest to learn and implement. I must admit I am unsure what VBA code solutions are.

 

[just_jon]

Can you post a small sample of the data you need to pull from, as well as which field you will use as a search criteria.

 

[dsrt16]

Authors List.xls
	A	B	C	D
4	Author	Title	Read	Description
5	Abramson,TraciHunter	Undercurrents
6		RippleEffects
7
8	Allen,NancyCambell	AtimefortheHeart
9		Echoes
10		LoveBeyondTime
11		NoTimeforLove
12		FaithofourFathers1
13		FofF2
14		FofF3
15		FofF4
16
17	Anderson,C.Paul	FinalAct
Sheet1

Now in another worksheet (in cell C3) when I type Abramson, Traci Hunter I want it to list in cell C5 Undercurrents and Ripple Effects. When I type in Bell, Michele Ashman I want it to list all 8 of her books in cell C5.

 

[just_jon]

Here's a code-based solution.

You would enter your target value in cell A2 on this sheet

Book3
	A	B	C	D	E
1	Field1	Field2	Field3	Field4
2	AAA	F1	F2	F3
3		F1A	F2A	F3A
4		F1B	F2B	F3B
5
Sheet1

And the results you see in columns B:D above should pop in from this sheet.

Book3
	A	B	C	D	E
1	Field1	Field2	Field3	Field4
2	AAA	F1	F2	F3
3	AAA	F1A	F2A	F3A
4	XXX	1	2	3
5	AAA	F1B	F2B	F3B
6	XXX	4	5	6
7
Sheet2

Here's the code that does that.<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Range)<SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN> Intersect(Target, [A2])<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN>
    Application.EnableEvents =<SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating =<SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastUsed<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>
    LastUsed = Cells(Rows.Count, 2).End(xlUp).Row
    Range("B1:D" & LastUsed).Clear
    <SPAN style="color:#00007F">With</SPAN> Sheet2
        .Columns("A:D").AutoFilter Field:=1, Criteria1:=Target.Value
        .[B:D].Copy [B1]
        .Cells.AutoFilter
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
Application.EnableEvents =<SPAN style="color:#00007F">True</SPAN>
Application.ScreenUpdating =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Hit Alt+F11 to open the VB Editor; on the left hand side, find the sheet name for the sheet on which you want to enter the A2 value.

Click it so it is selected. There should be a big white spae i the center/right of your screen.

Highlight the code above with your cursor, copy and paste in into that white space.

You can now close the Editor. Any change to cell A2 on that sheet should re-seed your other columns.

From now on, when opening the workbook, you'll get a warning that it contains macros -- if you want the code to work, you must answer in the affirmative.

Post back if you have trouble.

 

[NateO]

Hello,

aka A fun title, Another Title, Yeah
{snip}

Here's another potential approach. Use the following UDF, place it in a normal module in your workbook:

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

And use in your workbook as such:

Return all Matches.xls
	A	B	C	D	E
1	Author	Title		Name In	Return
2	Joe	Book1		Harold	Book3, Book7, Book9
3	Tom	Book2
4	Harold	Book3
5	Howard	Book4
6	Joe	Book5
7	Tom	Book6
8	Harold	Book7
9	Howard	Book8
10	Harold	Book9
SalaryCalc

And note, the filter() and Join() functions requires require XL 2000+, and that I have hardcoded to look at the 1st and 2nd column in a Range.


Edited by Nate: Removed harmless, but unecesssary space in UDF.

 

[just_jon]

Pretty slick, Nathan!

 

[dsrt16]

WOw you guys are amazing I would love to be that good with Excel...someday when I am older :D.

Anyway, I am unsure how to implement your ideas. Alt F11 brought up the HTML maker and I was unsure where to paste the code that just jon gave me. I see where to put the foo formula of Nate's, but I do not know where to post the code.

Here is what I am trying to do:

Authors List.xls
	A	B	C	D
3	SearchbyAuthor	Allen,NancyCambell
4
5		Book	Price	Description
6		AtimefortheHeart	#N/A	#N/A
Sheet2

As you can see I am only getting one book result with the VLookup formula. I am positive your guys ideas will work but where do I paste thoe codes. What is really irritating me is C6 and D6 are displaying N/A when they should work as they refer to the book title (where there is only one--not many like the author) and it is just a simple vlookup code.

 

[NateO]

Thanks, Jon.

dsrt16, the VBA UDF belongs in a normal module. Hit alt-f11 to open the VBE. Click Insert->Module and paste the code in the now opened, normal module.

Now go back to your worksheet and try foo() as posted earlier.

And, for future reference, to get rid of those chars in your HTML, see the following:

http://www.mrexcel.com/board2/viewtopic.php?t=92622#problems

Good hunting.