Vlookup multiple values in one cell

tteezzaa

New Member
Joined
Mar 29, 2013
Messages
4
WinXP, Excel 07

I am trying to find out how to return my result in the same cell.
I have a VLookup going to another file and returning a value but the lookup can have multiple instances. Right now, it returns only the first occurrence, and need to try to get them all in the same return result. Since it's a number, I mean can I get it to return like "541234,4563465,45234" if there were three instances? What do I need to add into this formula to get that to happen?

=VLOOKUP($A2,'[FI.xls]MASTER INVENTORY SHEET'!$A$1:$E$65536,5,FALSE)
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,181
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Welcome to MrExcel.

What you are requesting is not possible with VLOOKUP. Other functions such as INDEX / MATCH can be implemented but the results will be in separate cells. Is that OK with you?

To get results in the same cell, you will need VBA.

Please tell us which route would you prefer?
 

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,181
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Will try VBA
OK. Open Visual Basic Editor and then Insert a module using:
Insert | Module

Paste the following function:
Code:
Public Function mVLOOKUP(mlookup_value As Range, mtable_array As Range, mcol_index_num As Long)
Dim r As Range
Dim m As Long

mVLOOKUP = vbNullString
m = mtable_array.Cells(1, 1).Row - 1

For Each r In mtable_array
    If r.Value = mlookup_value.Value Then
        mVLOOKUP = mVLOOKUP & ", " & Application.Index(mtable_array, r.Row - m, mcol_index_num)
    End If
Next r

mVLOOKUP = Mid(mVLOOKUP, 3, Len(mVLOOKUP))

End Function

Then use it in your workbook like normal function e.g.
=mVLOOKUP(A2,[Book2]Sheet1!$A$2:$C$9,3)
 

tteezzaa

New Member
Joined
Mar 29, 2013
Messages
4

ADVERTISEMENT

I inserted the module via cut and paste then took the formula I had and inserted an m before it. Now it is giving me a #value error

=mVLOOKUP($A2,'[FI.xls]MASTER INVENTORY SHEET'!$A$1:$E$65536,5,FALSE)

With the regular Vlookup, I did at least get one entry, now I have an error.
 

tteezzaa

New Member
Joined
Mar 29, 2013
Messages
4
I tried changing the column from 3 that you had to 5, still #VALUE! error
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175

ADVERTISEMENT

Try it without the ",FALSE" at the end.
 

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,181
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I hope that you have inserted the code correctly.

I created false data and tested it. Following formula works for me but it makes the sheet very very slow.
=mVLOOKUP(A1,'[FI.xls]MASTER INVENTORY SHEET'!A:E,5)

Instead of that if you use smaller range like below then it works better.
=mVLOOKUP(A1,'[FI.xls]MASTER INVENTORY SHEET'!$A$1:$E$10000,5)

Please note there's no 4th argument in my UDF.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,444
Messages
5,642,190
Members
417,259
Latest member
gtacw

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
Top