Vlookup multiple values in one cell

[tteezzaa]

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)

 

[taurean]

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?

 

[tteezzaa]

Will try VBA

 

[taurean]

Will try VBA

OK. Open Visual Basic Editor and then Insert a module using:
Insert | Module

Paste the following function:

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]

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]

I tried changing the column from 3 that you had to 5, still #VALUE! error

 

[West Man]

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

 

[tteezzaa]

No change in the outcome

 

[taurean]

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.

 

[taurean]

The speed is definitely an issue with the UDF I have posted. Please also take a look at the following thread [see Robert Mika's post] which has a different UDF used. Test it as well.
http://www.mrexcel.com/forum/excel-questions/645023-return-multiple-values-concatenated.html