Vlookup to Return Multiple Values

[Admin]

Hi All,

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

Enjoy this custom LOOKUP function (MLOOKUP) !!

[Rajan_Verma]

Amended



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

[Admin]

Here is a latest version. Code amended to ignore blanks in the result.


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/mlookup-not-returning-results-710/#post3172

[radionut]

1) Hi

I have the data in the following format:
1017

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

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

[phaniy.405]

Here is a latest version. Code amended to ignore blanks in the result.


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/mlookup-not-returning-results-710/#post3172


how to run this code ?

[KingTamo]

What about value repetition?

[zanjabil]

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/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (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=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=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=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (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=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (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=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Admin]

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.

[PetRose]

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:

Can you examplify the Call parameters, either by more extensive wording or by a true example ?
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

[PetRose]

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 ?

Why is there two ranges as Inputs, and why is the sequence different from VLookup ?
Can you give some commentary notes, or examples that would give some expected results ?


Thanks to a newbie in here