Multivlookup function

YogeshAradhya

New Member
Joined
Aug 5, 2018
Messages
4
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 dataData need to be pull out as below, through vlookup
Project IdOrder IdProject IDOrder IDs
139481801010001139481801010001, 1801010002
139481801010002320721801030045, 1801030051, 1801030060
518621801010004435071801030027
518621801030016518621801010004, 1801030016
435071801030027623531801030032
623531801030032
320721801030045
320721801030051
320721801030060

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

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,568
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
Code:
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:
Code:
=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:
Code:
=mvlup(TheKey;TheTable;TheIndexInTheTable)
MVLUP check only for exact match of the key.

Bye
 

YogeshAradhya

New Member
Joined
Aug 5, 2018
Messages
4
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,509
Office Version
365
Platform
Windows
Did you save the file as a macro enabled .xlsm file?
 

YogeshAradhya

New Member
Joined
Aug 5, 2018
Messages
4
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,509
Office Version
365
Platform
Windows
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

New Member
Joined
Aug 5, 2018
Messages
4
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 :)
 

Forum statistics

Threads
1,089,201
Messages
5,406,801
Members
403,106
Latest member
AliO

This Week's Hot Topics

Top