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

 

[cgmojoco]

OK I finally got something working...but very slow.

If anyone can see a way to speed this up please

I added some Optional parameters to a function found on this forum and changed the format extensivley:

Option Explicit
'Original function written by "parmel" on MR.Excel.com and modified below
'This is a SLOW function that returns multiple UNIQUE lookup values into one cell
Function muvlookup(lIndex As String, r As Range, Optional icol As Integer, Optional strDEL As String) As String
'lIndex is the matching criteria
'r is the range to look in
'Optional i is the column# in the range to return values, default is 1 (return column value to right of criteria match)
'Optional strDEL is an optional deliminator to separate values, default is ","
'use the dictionary object to find the duplicate entries
Dim c As Range, l As String, sResult As String
Dim dict As Variant
Application.Volatile
sResult = ""
Set dict = CreateObject("Scripting.Dictionary")
If icol = 0 Then
icol = -1
End If
If strDEL = "" Then
strDEL = ","
End If
For Each c In r
    'do only for the index of interest
    If c.Offset(0, -1 * icol) = lIndex Then
      'use the value (apple, elephant, etc.) as the key and the index as the value
      'if it's not a repeat of apple, elephant, etc.) add the entry to the dictionary
      'and concatenate the value to the final string (sResult)
      If Not dict.Exists(c.Value) Then
            dict.Add c.Value, c.Offset(0, 1).Value
            sResult = sResult & strDEL & c.Value
      End If
    End If
Next c
muvlookup = sResult
End Function

Alas, this is almost too slow to use...

 

[Sav]

Hi, (a few years on!)

I've just been playing with the neat little 'Foo' procedure to return multiple results for a VLOOKUP. I have it working in a local sheet (thx), but wish to use it to look up data in another worksheet in the same Excel file.

What works locally returns #NAME when I try to reference data in another sheet. Data is setup in two adjacent columns as before.

as in =foo(OtherSheet!G2:H50,A1)
Suggestions appreciated .

Sav

[EDIT] Should have read past page 1, playing again now

 

[cgmojoco]

Try this compliments of Jindon:

'
*************************************************************************************************
'**********Multiple V--Change the myjoin string to your custom deliminator Lookup**********************************************************************
'*************************************************************************************************
Function muvlookup(ByVal strIndex As String, ByVal rng As Range, _
                 Optional ref As Integer = 1, Optional myJoin As String = " ", _
                 Optional myOrd As Boolean = True) As String
Dim a, b(), i As Long, n As Long
a = rng.Value
ReDim b(1 To UBound(a, 1), 1 To 2)
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a, 1)
        If a(i, 1) = strIndex Then
            If Not .Exists(a(i, ref)) Then
                .Add a(i, 2), Nothing
                n = n + 1: b(n, 1) = a(i, 2)
                b(n, 2) = IIf(IsNumeric(a(i, 2)), a(i, 2), UCase(a(i, 2)))
            End If
        End If
    Next
End With

If n > 0 Then
    VSortM b, 1, n, 2, myOrd
    For i = 1 To n
        muvlookup = muvlookup & IIf(muvlookup = "", "", myJoin) & b(i, 1)
    Next
End If
End Function
Sub VSortM(ary, LB, UB, ref, myOrd)
Dim i As Long, ii As Long, iii As Long, M, temp
i = UB: ii = LB
M = ary(Int((LB + UB) / 2), ref)
Do While ii <= i
    If myOrd Then
        Do While ary(ii, ref) < M: ii = ii + 1: Loop
        Do While ary(i, ref) > M: i = i - 1: Loop
    Else
        Do While ary(ii, ref) > M: ii = ii + 1: Loop
        Do While ary(i, ref) < M: i = i - 1: Loop
    End If
    If ii <= i Then
        For iii = LBound(ary, 2) To UBound(ary, 2)
            temp = ary(ii, iii): ary(ii, iii) = ary(i, iii): ary(i, iii) = temp
        Next
        i = i - 1: ii = ii + 1
    End If
Loop
If LB < i Then VSortM ary, LB, i, ref, myOrd
If ii < UB Then VSortM ary, ii, UB, ref, myOrd
End Sub

 

[Sav]

Many thanks for your time and interest,

I won't get time to try this until this evening (GMT)

I presume the parameters work the same way as in VLOOKUP.

I'm very competent with Excel formulae and can programme, but have never used VBA in excel. I got the previous 'foo' procedure workng locally but not the 'Foo2' and then the 'Foo' procedure.

I just pasted the procedure into the VB editor with the source sheet up and saved the spreadsheet, is that correct pls?

Thx.

Sav (Chris)

 

[Sav]

Had a quick try and got #NAME?

I just fired up the VB editor with the source sheet up, clicked view code, pasted the procedure in and saved the spreadsheet.

Am I missing something please? Try some more this evening!

Sav

 

[cgmojoco]

=muvlookup(valuetolookup,rangetolookin,numberofcolumnsovertoreturn--Ithinkthishastobesecondcolumn,"deliminatorlike-or;",trueorfalseifyouwantosorttheresultsstring)

Good luck

 

[Sav]

Thanks again.

It looks like it was a scoping problem. Tried inserting a new procedure called muvlookup and noted that it also had 'Public' at the front.

Copied the muvlookup code and parameters into the new procedure and it worked first time.

If I've cracked it great! if I've missed anything then advice appreciated.

Think all I have to do now it concatenate the results from a sheet for each month of the year now!

Sav.

 

[lox1985]

can someone give an explanation of how to use that muvlookup function? What each field means. I'm getting an error for it when I follow cgmojoco's explanation. Here are the values I need filled in.

Lookup Value = B2
Range to look in = 'Loans Out of Tolerance'!AI2:Ai65536
Column to look at = 10 columns to the left from the Range where I'm lookin
MyJoin = ??? don't know, what to insert
MyOrd = ??? don't know, what to insert

Thanks

 

[cgmojoco]

Doesn't look left

MyJoin is what you would put in quotes like "," if you want to have your multiple results separated by a comma.

MyOrd is true if you want to sort the multiple results in order.

 

[excel-proficient]

Hi Guys,

Is it possible to vlookop miltiple results via formula (customized will do) rather than using macro?