Multivlookup function

Y

YogeshAradhya

New Member
Joined
Aug 5, 2018
Messages
6
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][TABLE="width: 622"]
<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>[TR]
[TD="class: m_1347601473638318122gmail-xl64, width: 106"]Raw data[/TD]
[TD="class: m_1347601473638318122gmail-xl65, width: 96"][/TD]
[TD="width: 64"][/TD]
[TD="width: 356, colspan: 2"]Data need to be pull out as below, through vlookup[/TD]
[/TR]
[TR]
[TD="class: m_1347601473638318122gmail-xl66"]Project Id[/TD]
[TD="class: m_1347601473638318122gmail-xl67"]Order Id[/TD]
[TD="class: m_1347601473638318122gmail-xl63"][/TD]
[TD="class: m_1347601473638318122gmail-xl70"]Project ID[/TD]
[TD="class: m_1347601473638318122gmail-xl71"]Order IDs[/TD]
[/TR]
[TR]
[TD="class: m_1347601473638318122gmail-xl68"]13948[/TD]
[TD="class: m_1347601473638318122gmail-xl69"]1801010001[/TD]
[TD][/TD]
[TD="class: m_1347601473638318122gmail-xl68"]13948[/TD]
[TD="class: m_1347601473638318122gmail-xl68"]1801010001, 1801010002[/TD]
[/TR]
[TR]
[TD="class: m_1347601473638318122gmail-xl68"]13948[/TD]
[TD="class: m_1347601473638318122gmail-xl69"]1801010002[/TD]
[TD][/TD]
[TD="class: m_1347601473638318122gmail-xl68"]32072[/TD]
[TD="class: m_1347601473638318122gmail-xl68"]1801030045, 1801030051, 1801030060[/TD]
[/TR]
[TR]
[TD="class: m_1347601473638318122gmail-xl68"]51862[/TD]
[TD="class: m_1347601473638318122gmail-xl69"]1801010004[/TD]
[TD][/TD]
[TD="class: m_1347601473638318122gmail-xl68"]43507[/TD]
[TD="class: m_1347601473638318122gmail-xl68"]1801030027[/TD]
[/TR]
[TR]
[TD="class: m_1347601473638318122gmail-xl68"]51862[/TD]
[TD="class: m_1347601473638318122gmail-xl69"]1801030016[/TD]
[TD][/TD]
[TD="class: m_1347601473638318122gmail-xl68"]51862[/TD]
[TD="class: m_1347601473638318122gmail-xl68"]1801010004, 1801030016[/TD]
[/TR]
[TR]
[TD="class: m_1347601473638318122gmail-xl68"]43507[/TD]
[TD="class: m_1347601473638318122gmail-xl69"]1801030027[/TD]
[TD][/TD]
[TD="class: m_1347601473638318122gmail-xl68"]62353[/TD]
[TD="class: m_1347601473638318122gmail-xl68"]1801030032[/TD]
[/TR]
[TR]
[TD="class: m_1347601473638318122gmail-xl68"]62353[/TD]
[TD="class: m_1347601473638318122gmail-xl69"]1801030032[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: m_1347601473638318122gmail-xl64"][/TD]
[/TR]
[TR]
[TD="class: m_1347601473638318122gmail-xl68"]32072[/TD]
[TD="class: m_1347601473638318122gmail-xl69"]1801030045[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: m_1347601473638318122gmail-xl64"][/TD]
[/TR]
[TR]
[TD="class: m_1347601473638318122gmail-xl68"]32072[/TD]
[TD="class: m_1347601473638318122gmail-xl69"]1801030051[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: m_1347601473638318122gmail-xl64"][/TD]
[/TR]
[TR]
[TD="class: m_1347601473638318122gmail-xl68"]32072[/TD]
[TD="class: m_1347601473638318122gmail-xl69"]1801030060[/TD]
[/TR]
</tbody>[/TABLE]
[/FONT]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
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
 
Upvote 0
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....
 
Upvote 0
Did you save the file as a macro enabled .xlsm file?
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
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 :)
 
Upvote 0
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?

Anthony47 said:
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
Click to expand...
 
Upvote 0
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:
VBA Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Vlookup with multiple inputs?
Replies
4
Views
859
Aladin Akyurek
Aladin Akyurek
M
  • Question Question
Finding value in a row
Replies
4
Views
997
MRN227
M
U
  • Question Question
Quick VLOOKUP
Replies
3
Views
647
Aladin Akyurek
Aladin Akyurek
S
  • Question Question
Pull unique names refrencing date field
Replies
3
Views
697
Fluff
Fluff
L
  • Question Question
Sort issue - multiple tables using vlookup or index/match
Replies
1
Views
2K
BlueHornet
B

Forum statistics

Threads
1,223,632
Messages
6,173,472
Members
452,516
Latest member
archcalx

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top