Can we get multiple lookup values for an item in a single cell (separated by comma or space)?
I have been asked this question multiple times by many of my colleagues. And after a lot of head-banging, I realized that it is extremely difficult (if not impossible) to do this using excel functions.
So I did a bit of VBA coding to come up with a custom function (also called a User Defined Function) in Excel.
In this tutorial, I will show you how to get multiple lookup values in a single cell (with and without repetition).
Get Multiple Lookup Values in a Single Cell (with repetition)
To get multiple lookup values in a single cell, we need to create a function in VBA (similar to the VLOOKUP function) that checks each cell in a column and if the lookup value is found, adds it to the result.
Here is the VBA code that can do this:
'Code by Sumit Bansal (https://trumpexcel.com) Function SingleCellExtract(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells.
Count If LookupRange.Cells(i, 1) = Lookupvalue Then Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & "," End If Next i SingleCellExtract = Left(Result, Len(Result) – 1) End Function
Where to Put this Code?
- Open a workbook and click Alt + F11 (this opens the VBA Editor window).
- In this VBA Editor window, on the left, there is a project explorer (where all the workbooks and worksheets are listed). Right-click on any object in the workbook where you want this code to work, and go to Insert –> Module.
- In the module window (that will appear on the right), copy and paste the above code.
- Now you are all set. Go to any cell in the workbook and type =SingleCellExtract and plug in the required input arguments (i.e., LookupValue, LookupRange, ColumnNumber).
How does this formula work?
This function works similar to the VLOOKUP function.
It takes 3 arguments as inputs:
1. Lookupvalue – A string that we need to look-up in a range of cells.
2. LookupRange – An array of cells from where we need to fetch the data ($B3:$C18 in this case).
3. ColumnNumber – It is the column number of the table/array from which matching value is to be returned (2 in this case).
When you use this formula, it checks each cell in the left most column in the lookuprange and when it find a match, it adds to to the result in the cell in which you have used the formula.
Remember: Save the workbook as a macro-enabled workbook (.xlsm or .xls) to reuse this formula again. Also, this function would be available only in this workbook and not in all workbooks.
Get Multiple Lookup Values in a Single Cell (without repetition)
There is a possibility that you may have repetitions in the data.
If you use the code used above, it will give you repetitions in the result as well.
If you want to get the result where there are no repetitions, you need to modify the code a bit.
Here is the VBA code that will give you multiple lookup values in a single cell without any repetitions.
'Code by Sumit Bansal (https://trumpexcel.com) Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells.Count If LookupRange.Cells(i, 1) = Lookupvalue Then For J = 1 To i - 1 If LookupRange.Cells(J, 1) = Lookupvalue Then If LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Then GoTo Skip End If End If Next J Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left(Result, Len(Result) - 1) End Function
Once you have placed this code in the VB Editor (as shown above in the tutorial), you will be able to use the MultipleLookupNoRept function.
Here is a snapshot of the result you will get with this MultipleLookupNoRept function.