Results 1 to 6 of 6

Thread: MLookup not returning results

  1. #1
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0

    MLookup not returning results

    I'm wanting to include the MLookup function in my addin, so I can let others use it, but I'm having trouble with the usability of the function itself. I have one workbook example where I use MLookup, though the function isn't in the workbook, instead it's in my Addin. That all works great.

    So, now I'm trying it in a real world situation, and it's not working. See the example attached (I've moved my MLookup function to the workbook). I'm trying to return all Job Codes (column T) associated with the subject Posn Func Code (column A) by looking in Column S, and I'm getting VALUE errors. I'd appreciate it if anyone can point out what I'm doing wrong.

    BTW, in this case I can use Rick Rothsteins LOOKUPCONCAT function, but I really want to get the MLookup working right for my team. I'd appreciate any help I can get.

    Thanks,
    John
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Try this code.

    Code:
    Public Function MLOOKUP(TableArray As Range, ByVal LookupVal, LookupRange As Range, _
                                Optional ByVal NumAsText As Boolean = False, Optional ByVal NthMatch As Long)
    '---------------------------------------------------------------------------------------
    ' Procedure : MLOOKUP
    ' Author    : Krishnakumar @ ExcelFox.com
    ' Date      : 12/5/2012
    ' Purpose   : Returns multiple values
    '---------------------------------------------------------------------------------------
    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
                    MLOOKUP = MLOOKUP & "," & KA1(r, c)
                End If
            End If
        Next
    Next
    MLOOKUP = Mid$(MLOOKUP, 2)
    End Function
    Actually it treats the text like number as a true number.

    use the formula like

    =MLOOKUP(T$2:T$226,A3,S$2:S$226,TRUE)
    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)

  3. #3
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0
    I've examined the code and can't discover where it differs from the code in the workbook. Can you pinpoint the difference for me?

    Putting "True" at the end, using the new code, works to fill the cells with the concatenated results, which is the same thing Rick Rothsteins LOOKUPCONCAT function does. However, I'm also interested in using MLookups ability to put each result in a different cell, using the formula like so:

    =MLOOKUP(T$2:T$228,A7,S$2:S$228,ROWS($Q$7:Q7))
    =MLOOKUP(T$2:T$228,A7,S$2:S$228,ROWS($Q$7:Q8))

    This works with the original MLookup macro on my working example, but neither option worked on the workbook I previously posted. Now with whatever change you made, the function to return concatenated results works, but returning only the 1st, 2nd, or Nth value doesn't work, instead it returns all results.

    See the updated workbook attached.
    Attached Files Attached Files

  4. #4
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0
    Okay, I found the differences in the code, but can't see where the Nth Match is impacted by the changes. However, that seems to be the case. As it was originally posted, at http://www.excelfox.com/forum/f12/vl...iple-values-4/, the formula configurations were:
    =MLOOKUP(T$2:T$228,A3,S$2:S$228) to return the concatenated values
    =MLOOKUP(T$2:T$228,A7,S$2:S$228,ROWS($Q$7:Q7)) to return the first value matching Q7
    =MLOOKUP(T$2:T$228,A7,S$2:S$228,ROWS($Q$7:Q8)) to return the second value matching Q7

    Now the format is
    =MLOOKUP(T$2:T$228,A3,S$2:S$228, TRUE) whcih returns the concatenated values
    Anything other than TRUE returns an error.

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    The Nth match is the 4th parameter. So your formula would be

    =MLOOKUP(T$2:T$228,A7,S$2:S$228,TRUE,ROWS($Q$7:Q7) )

    or Nth match by omitting the Number as text

    =MLOOKUP(T$2:T$228,A7,S$2:S$228,,ROWS($Q$7:Q7))
    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 2011
    Posts
    19
    Rep Power
    0
    Thanks Admin, that did the trick! Just a suggestion, but this is my second question on MLookup (I posted earlier about how to use it). Do you think both the direction on how to use, and the updated version you just provided, should be placed with the Original Posting (http://www.excelfox.com/forum/f12/vl...iple-values-4/)?

Similar Threads

  1. LookUp Value and Concatenate All Found Results
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 48
    Last Post: 10-31-2019, 07:00 AM
  2. MLookup:How to use
    By jomili in forum Excel Help
    Replies: 5
    Last Post: 01-16-2014, 11:05 PM
  3. VLOOKUP produces undesired results
    By Dimitrios Charalampidis in forum Excel Help
    Replies: 1
    Last Post: 02-15-2013, 11:32 PM
  4. Replies: 12
    Last Post: 05-27-2012, 08:38 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

Posting Permissions

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