MLookup not returning results

[jomili]

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

[Admin]

Hi,

Try this 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)

[jomili]

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.

[jomili]

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/vlookup-return-multiple-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.

[Admin]

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))

[jomili]

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/vlookup-return-multiple-values-4/)?