Results 1 to 10 of 10

Thread: Vlookup to Return Multiple Values

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10

    Vlookup to Return Multiple Values

    Hi All,

    Here is a custom function to return multiple values for a lookup value.

    Enjoy this custom LOOKUP function (MLOOKUP) !!
    Attached Files Attached Files
    Last edited by Admin; 05-15-2011 at 06:31 PM.

  2. #2
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    Amended

    Code:
    Function MLOOKUP(TableArray As Range, ByVal LookupVal, LookupRange As Range, _
                                                            Optional ByVal NthMatch As Long)
    
    ' Author        : Krishnakumar @ ExcelFox.com
    
    
    
    If Not TypeOf TableArray Is Range Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    If Not TypeOf LookupRange Is Range Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    If TableArray.Rows.Count <> LookupRange.Rows.Count Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    If TableArray.Columns.Count <> LookupRange.Columns.Count Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    
    Dim LV_Cnt      As Long 'Count Loookup Value
    Dim KA1, KA2
    Dim r As Long, c As Long
    Dim fFoundNo    As Long
    Dim n           As Long
    Dim strLval     As String
    
    If IsNumeric(LookupVal) Then
        LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," & LookupVal & ")")
        fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," & LookupRange.Address(, , , 1) & ",0)")
    ElseIf IsDate(LookupVal) Then
        LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," & CLng(LookupVal) & ")")
        fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," & LookupRange.Address(, , , 1) & ",0)")
    Else
        strLval = """" & LookupVal & """"
        LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," & strLval & ")")
        fFoundNo = Evaluate("match(" & strLval & "," & LookupRange.Address(, , , 1) & ",0)")
    End If
    
    If NthMatch > 0 Then
        If LV_Cnt = 0 Or NthMatch > LV_Cnt Then
            MLOOKUP = CVErr(2042)
            Exit Function
        End If
    End If
    
    
    KA1 = TableArray: KA2 = LookupRange
    
    For r = fFoundNo To UBound(KA1, 1)
        For c = 1 To UBound(KA1, 2)
            If LCase$(KA2(r, c)) = LCase$(LookupVal) Then
                If NthMatch Then
                    n = n + 1
                    If n = NthMatch Then
                        MLOOKUP = KA1(r, c)
                        Exit Function
                    End If
                Else
                    MLOOKUP = MLOOKUP & "," & KA1(r, c)
                End If
            End If
        Next
    Next
    MLOOKUP = Mid$(MLOOKUP, 2)
    End Function
    Last edited by littleiitin; 11-26-2012 at 11:37 AM.

  3. #3
    Junior Member
    Join Date
    Feb 2017
    Posts
    2
    Rep Power
    0

    Question Parameter issue or runtime error #VALUE

    Quote Originally Posted by Rajan_Verma View Post
    Amended
    Latest version
    Hi there, and thanks for provider enhanced versions to us .
    As a newbie here, but not to VBA nor Excel, I would like to ask a few simple basic Q to the UDP function:
    1. Can you examplify the Call parameters, either by more extensive wording or by a true example ?
    2. Why is the parameter sequence different from VLookup ? Its not obvious to me, why there are two table Ranges as params


    I have managed to get it work with Text-lookup, but when I try with numbers (or number-like numbers), I get: #VALUE error. (Excel 2010)
    Debugging it, it gets to the second Evaluate, after the Label 1 :
    where variable 'fFoundNo' gets 'OutOfContext' value after Evaluate

    So it may be my parameters, but I do not know yet...

    Thanks in advance

  4. #4
    Junior Member
    Join Date
    Feb 2017
    Posts
    2
    Rep Power
    0

    Question Numeric values, seems to get #VALUE result for Lookup

    Here is a latest version. Code amended to ignore blanks in the result.
    Hi there,
    Can you elaborate on this otherwise useful UDF function here ?
    1. Why is there two ranges as Inputs, and why is the sequence different from VLookup ?
    2. Can you give some commentary notes, or examples that would give some expected results ?


    Thanks to a newbie in here

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Here is a latest version. Code amended to ignore blanks in the result.

    Code:
    Public Function MLOOKUP(TableArray As Range, ByVal LookupVal, LookupRange As Range, _
                                        Optional ByVal NumAsText As Boolean = False, _
                                        Optional ByVal NthMatch As Long, _
                                        Optional IgnoreBlanks As Boolean = True)
    '---------------------------------------------------------------------------------------
    ' Procedure : MLOOKUP
    ' Author    : Krishnakumar @ ExcelFox.com
    ' Date      : 12/5/2012
    ' Purpose   : Returns multiple values
    ' Amended   : Include option to ignore blanks - 07/22/13
    '---------------------------------------------------------------------------------------
    If Not TypeOf TableArray Is Range Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    If Not TypeOf LookupRange Is Range Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    If TableArray.Rows.Count <> LookupRange.Rows.Count Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    If TableArray.Columns.Count <> LookupRange.Columns.Count Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    
    Dim LV_Cnt      As Long 'Count Loookup Value
    Dim KA1, KA2
    Dim r As Long, c As Long
    Dim fFoundNo    As Long
    Dim n           As Long
    Dim strLval     As String
    
    If IsNumeric(LookupVal) Then
        LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," & LookupVal & ")")
        If NumAsText Then GoTo 1
        fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," & LookupRange.Address(, , , 1) & ",0)")
    ElseIf IsDate(LookupVal) Then
        LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," & CLng(LookupVal) & ")")
        fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," & LookupRange.Address(, , , 1) & ",0)")
    Else
    1:
        strLval = """" & LookupVal & """"
        LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," & strLval & ")")
        fFoundNo = Evaluate("match(" & strLval & "," & LookupRange.Address(, , , 1) & ",0)")
    End If
    
    If NthMatch > 0 Then
        If LV_Cnt = 0 Or NthMatch > LV_Cnt Then
            MLOOKUP = CVErr(2042)
            Exit Function
        End If
    End If
    
    
    KA1 = TableArray: KA2 = LookupRange
    
    For r = fFoundNo To UBound(KA1, 1)
        For c = 1 To UBound(KA1, 2)
            If LCase$(KA2(r, c)) = LCase$(LookupVal) Then
                If NthMatch Then
                    n = n + 1
                    If n = NthMatch Then
                        MLOOKUP = KA1(r, c)
                        Exit Function
                    End If
                Else
                    If Not IgnoreBlanks Then
                        MLOOKUP = MLOOKUP & "," & KA1(r, c)
                    ElseIf Len(KA1(r, c)) Then
                        MLOOKUP = MLOOKUP & "," & KA1(r, c)
                    End If
                End If
            End If
        Next
    Next
    MLOOKUP = Mid$(MLOOKUP, 2)
    End Function
    This handles numbers which are not true number ,but text. Just pass the 3rd parameter as TRUE. By default it is FALSE.

    http://www.excelfox.com/forum/f2/mlo...-710/#post3172
    Last edited by Admin; 07-22-2013 at 09:16 AM. Reason: code amended
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Junior Member
    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0

    How can I make it to ignore blank cells?

    1) Hi

    I have the data in the following format:
    criteria.jpg

    Which results in a bunch of commas. (Ignore the #value errors)
    values.png

    How can it be dealt with?


    2) The algorithm seems to be quite time consuming even on a data range which is not that big. How does it work on big databases?

    Thanks

  7. #7
    Junior Member
    Join Date
    Aug 2014
    Posts
    1
    Rep Power
    0

    how to run this code ?

    Quote Originally Posted by Admin View Post
    Here is a latest version. Code amended to ignore blanks in the result.

    Code:
    Public Function MLOOKUP(TableArray As Range, ByVal LookupVal, LookupRange As Range, _
                                        Optional ByVal NumAsText As Boolean = False, _
                                        Optional ByVal NthMatch As Long, _
                                        Optional IgnoreBlanks As Boolean = True)
    '---------------------------------------------------------------------------------------
    ' Procedure : MLOOKUP
    ' Author    : Krishnakumar @ ExcelFox.com
    ' Date      : 12/5/2012
    ' Purpose   : Returns multiple values
    ' Amended   : Include option to ignore blanks - 07/22/13
    '---------------------------------------------------------------------------------------
    If Not TypeOf TableArray Is Range Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    If Not TypeOf LookupRange Is Range Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    If TableArray.Rows.Count <> LookupRange.Rows.Count Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    If TableArray.Columns.Count <> LookupRange.Columns.Count Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
    
    Dim LV_Cnt      As Long 'Count Loookup Value
    Dim KA1, KA2
    Dim r As Long, c As Long
    Dim fFoundNo    As Long
    Dim n           As Long
    Dim strLval     As String
    
    If IsNumeric(LookupVal) Then
        LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," & LookupVal & ")")
        If NumAsText Then GoTo 1
        fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," & LookupRange.Address(, , , 1) & ",0)")
    ElseIf IsDate(LookupVal) Then
        LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," & CLng(LookupVal) & ")")
        fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," & LookupRange.Address(, , , 1) & ",0)")
    Else
    1:
        strLval = """" & LookupVal & """"
        LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," & strLval & ")")
        fFoundNo = Evaluate("match(" & strLval & "," & LookupRange.Address(, , , 1) & ",0)")
    End If
    
    If NthMatch > 0 Then
        If LV_Cnt = 0 Or NthMatch > LV_Cnt Then
            MLOOKUP = CVErr(2042)
            Exit Function
        End If
    End If
    
    
    KA1 = TableArray: KA2 = LookupRange
    
    For r = fFoundNo To UBound(KA1, 1)
        For c = 1 To UBound(KA1, 2)
            If LCase$(KA2(r, c)) = LCase$(LookupVal) Then
                If NthMatch Then
                    n = n + 1
                    If n = NthMatch Then
                        MLOOKUP = KA1(r, c)
                        Exit Function
                    End If
                Else
                    If Not IgnoreBlanks Then
                        MLOOKUP = MLOOKUP & "," & KA1(r, c)
                    ElseIf Len(KA1(r, c)) Then
                        MLOOKUP = MLOOKUP & "," & KA1(r, c)
                    End If
                End If
            End If
        Next
    Next
    MLOOKUP = Mid$(MLOOKUP, 2)
    End Function
    This handles numbers which are not true number ,but text. Just pass the 3rd parameter as TRUE. By default it is FALSE.

    http://www.excelfox.com/forum/f2/mlo...-710/#post3172

    how to run this code ?

  8. #8
    Junior Member
    Join Date
    Dec 2014
    Posts
    1
    Rep Power
    0

    Lightbulb MLOOKUP

    Dear Mister Admin,

    I have been trying to look for something like this. And it gives me hope - but when I tried, this MLOOKUP is very limited, i.e on your example it wor only for one variable $E$2, in this case is strictly VM. And another restriction that it will compare with previous result on a cell above it in this part ROWS($F$2:F2)), showing that this MLOOKUP only work one at a time for one variable with multiple outcome.

    Since I'm working with data of thousands, 10 and 100 of them - I can't utilize this great formula for my purpose.

    Or probably I am missing something.

    Please advise, thanks


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-24-2023 at 02:56 PM.

  9. #9
    Junior Member
    Join Date
    Dec 2012
    Posts
    12
    Rep Power
    0
    What about value repetition?

  10. #10
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    for a large data sets using UDF is not a good way.

    ROWS($F$2:F2) is not comparing previous cell, rather it gives you a number.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. How do I get Vlookup to return multiple matches?
    By HANOOF in forum Excel Help
    Replies: 1
    Last Post: 06-04-2013, 10:06 PM
  2. Replies: 4
    Last Post: 04-24-2013, 10:04 AM
  3. Vlookup Multiple Values By Adding Formula With Loop In VBA
    By Safal Shrestha in forum Excel Help
    Replies: 15
    Last Post: 04-22-2013, 04:49 PM
  4. Vlookup - Multiple Sheets
    By Suhail in forum Excel Help
    Replies: 3
    Last Post: 01-30-2013, 06:47 PM
  5. VLOOKUP with Multiple Results
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 2
    Last Post: 05-26-2011, 10:29 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •