Multivlookup function

[YogeshAradhya]

Hello Team MrExcel,

It's excited to post a query here, I hope would get a solution here.
I have a question about vlookup, How to pullout multiple values on a cell?
[FONT=&quot]Expecting data pull out as below by using Vlookup, which will help me a lot. Need your help! Could you pls help?
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]

Raw data			Data need to be pull out as below, through vlookup
Project Id	Order Id		Project ID	Order IDs
13948	1801010001		13948	1801010001, 1801010002
13948	1801010002		32072	1801030045, 1801030051, 1801030060
51862	1801010004		43507	1801030027
51862	1801030016		51862	1801010004, 1801030016
43507	1801030027		62353	1801030032
62353	1801030032
32072	1801030045
32072	1801030051
32072	1801030060

<colgroup><col width="106" style="width: 80pt;"><col width="96" style="width: 72pt;"><col width="64" style="width: 48pt;"><col width="119" style="width: 89pt;"><col width="237" style="width: 178pt;"></colgroup><tbody>
</tbody>

[/FONT]

 

[Anthony47]

Not easy to do with standard excel function, so let me suggest a User Defined Function:
-copy this code into a standard module of your vba

Function mvlup(ByRef myVal, ByRef myArea As Range, ByVal myInd As Long, Optional ByVal myOpt As Long) As String
'Multi VLookUp
Dim myOC, I As Long, myOut As String

myOC = Application.WorksheetFunction.Index(myArea, 0, 1)
For I = 1 To UBound(myOC)
    If myOC(I, 1) = myVal Then
        myOut = myOut & ", " & myArea(I, myInd)
    End If
Next I
mvlup = Mid(myOut, 3)
End Function

-then you might use a formula like this one:

=mvlup(D2;$A$1:$B$12;2)

If your Table is in A1:B12 and your key is in D2 then the formula will return all the values of column 2 that match the key

The general syntax for the function is:

=mvlup(TheKey;TheTable;TheIndexInTheTable)

MVLUP check only for exact match of the key.

Bye

 

[YogeshAradhya]

Thanks Anthony,

But It's work only one time, when add the code on active excel (i.e., Alt+F11 - Insert - Module - Paste the code which you provided above) Later on if close the file & re-open the file, then function won't work. How to fix this function? works like any other default function on excel? Could you please suggest....

 

[Fluff]

Did you save the file as a macro enabled .xlsm file?

 

[YogeshAradhya]

Yes, I saved as .xlsm format now, but mvlup function working now on .xlsm file only which i saved, which won't work on .xlsx files. what do i have to do for this function enable on all excel files? Can you suggest?

 

[Fluff]

The simplest way is to put the function into your Personal.xlsb and refer to it like
=Personal!mvlup(D2;$A$1:$B$12;2)

 

[YogeshAradhya]

I got a solution to enable function on all files

Steps as below.

1.create empty excel file & open it
2.Press Alt+ F11, VBA editor will open
3.Goto Insert -> Module
4.Paste the VBA code
5.save file as Excell add in (.Xla)
6.Now, Goto -> File -> option -> Add-Ins -> Go -> Browse -> Select the saved .Xla file then OK

Then excel add in function will works on all excel files as usual other default functions.

Thank you all

 

[Saba_Z]

Hi thanks for this. Is there any way I can remove Duplicates too? For e.g I have data like this

Ana 1
Bob 2
Ana 3
Kay 7
Kay 7

I don't want it to return two 7's in front of Kay, just the one. Any ideas how to do that?

Not easy to do with standard excel function, so let me suggest a User Defined Function:
-copy this code into a standard module of your vba

Function mvlup(ByRef myVal, ByRef myArea As Range, ByVal myInd As Long, Optional ByVal myOpt As Long) As String
'Multi VLookUp
Dim myOC, I As Long, myOut As String

myOC = Application.WorksheetFunction.Index(myArea, 0, 1)
For I = 1 To UBound(myOC)
    If myOC(I, 1) = myVal Then
        myOut = myOut & ", " & myArea(I, myInd)
    End If
Next I
mvlup = Mid(myOut, 3)
End Function

-then you might use a formula like this one:

=mvlup(D2;$A$1:$B$12;2)

If your Table is in A1:B12 and your key is in D2 then the formula will return all the values of column 2 that match the key

The general syntax for the function is:

=mvlup(TheKey;TheTable;TheIndexInTheTable)

MVLUP check only for exact match of the key.

Bye

 

[Anthony47]

I don't know how the Moderators will be happy about reviving this old thread, but here we are and let's go on

This variant should be sufficient:

Function UniqMvlup(ByRef myVal, ByRef myArea As Range, ByVal myInd As Long, Optional ByVal myOpt As Long) As String
'Multi VLookUp
Dim myOC, I As Long, myOut As String

myOC = Application.WorksheetFunction.Index(myArea, 0, 1)
For I = 1 To UBound(myOC)
    If myOC(I, 1) = myVal And InStr(1, myOut, ", " & myArea(I, myInd), vbTextCompare) = 0 Then
        myOut = myOut & ", " & myArea(I, myInd)
    End If
Next I
UniqMvlup = Mid(myOut, 3)
End Function

Bye