I couldn't find a multi-vlookup function that would return every match listed in a single cell. This can be useful if you want a quick summary of the looked-up data but don't want to waste lots of columns returning lots or results like the Array based multi-vlookups.
I put together an excel macro function to do this, and its working well on the sheet I designed it for. Macro functions are easy to setup by copy and pasting into the Macro editor in your sheet. Make sure you put it in a new Module; not any of the sheets or ThisWorkbook.
Public Function MultiVLookup(MatchWith As String, TRange As Range, col_index_num As Integer) MatchWith = LCase$(MatchWith) If (MatchWith = "") Then MultiVLookup = "" Else For Each cell In TRange If LCase$(cell.Value) = MatchWith Then x = x & cell.Offset(0, col_index_num).Value & ", " End If Next cell If (x = "") Then MultiVLookup = "" Else MultiVLookup = Left(x, Len(x) - 2) End If End If End Function
The format is similar to the normal Vlookup, except a little more flexible: The lookup table doesnt need to cover the whole table, just the one column to lookup into, or searched. The column index number to return is the number of columns to the right of the lookup column. So to return the next column set to 1. You can even return the column to the left of the looked-up column with a -1.
So in the example below, the column being searched is B. Only this B column is needed for the table_array (eg. B$3:B$20 ). The column to be returned doesn't have to be in the table_array. So to return column C you would use 1 for the col_index_num, for coulmn D use 2. To return column A to the left use -1.
=MULTIVLOOKUP(lookup_value, table_array, col_index_num)
lookup_value is the value to be searched for
table_array is a table of one column to be searched
col_index_num is the number of columns to the right of the table_array column to get the return value from.