*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 leftmost column in the *lookuprange *and when it finds a match, it adds 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.

**Try it yourself.. Download the file from here**

**Excel VBA Course**

### 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.

**Try it yourself.. Download the Example file
**

**You May Also Like the Following Excel Tutorials:**

- Get Multiple Lookup Values in a Row in Adjacent Cells in Excel.
- How to Create and Use an Excel Add-in.
- How to Run a Macro in Excel.
- How to Record a Macro in Excel – A Step by Step Guide.

Would anyone help me with macro for a shift schedule of operators working in 2 shifts.

It should choose operators from database and populate in the shift tables according to their expertise in relevant machine?

excellent

How do i get this to work as a Hlookup?

Hi there. I want to do something similar to this. I have a table with people named in row 2, and the date all the way down column 2. I want to be able to generate a comma separated list of those who are on annual leave “AL” for each corresponding day.

Something like this:

date P1 P2 P3 AL

1/1 AL

2/1 AL AL AL P1,P2, P3

3/1 AL AL P1,P2

So it’s similar to your code, but looks along a row, instead of down a column.

Thanks

I need to use this code on a different worksheet – from the one the list is created on. What do I need to edit to make that happen? Not much of a coder at all….

Same issue for me. I need this to work on another worksheet. Were you able to figure it out?

Hello Sumit,

I found your Multiple lookup values in one ell in your forum and it helped me a lot.

Now i need to improve that and I need your help regarding this.

We have used a code for only one column or a cell as a lookup reference. Now I need to include one more to this. 2 columns as s lookup reference to get the same results.

My code as per your example code is below.

Function SingleCellExtractInward(lookupvalue As String, lookuprange As Range, ColumnNumber As Integer)

Dim i As Double

Dim Result1 As String

Dim Result2 As String

If Result2 = Empty Then

Result2 = “no recent inward”

SingleCellExtractInward = Result2

End If

For i = 1 To lookuprange.Columns(1).Cells.Count

If lookuprange.Cells(i, 1) = lookupvalue Then

Result1 = Result1 & ” ” & lookuprange.Cells(i, ColumnNumber) & “,”

SingleCellExtractInward = Left(Result1, Len(Result1) – 1)

End If

Next i

End Function

Could you please help me on this code to lookup 2 columns as a reference.?

Hello, I have found your code and it helped. I have a query that, I need to lookup one more column with the same process. How can i do that? Please help me on this.

Dear Sumit,

It seems it is the thing i am looking for, but it doesn’t work! I get a #Name error. I have put it in the VB editor and saved it as .xslm. First used the function across sheets and doubted this was the problem. Then tried it i a single sheet with an example wit still got the #Name error.

Any idea what i could be doing wrong? Saved it the wrong way or something?

This is fantastic! Is there a simple way to extend this to search through multiple columns, for example, if column D was another list of names (sales rep 2)?

Also, is there a way to exclude blank cells?,If there happened to be a missing name, for example?

Thank you for this. I had found something similar a while back called vlookupall that gives similar results but was a little harder to follow the internal logic.

Hi, I appreciate your post showing how to do this using VBA, however I am wondering if there is a way to do this using only excel formulas?

Hi Sumit,

Your macro does exactly what I require so I am pleased to find it but I cannot get it to work – I am new to VBA so could be just me 🙂

I have included it into my macro enabled spreadsheet and everytime it executes there is a compile error, like the one mentioned by Laura below.

Compile Error:

Syntax Error

SingleCellExtract = Left(Result, (Len(Result) – 1))

I have tried altering the brackets but no improvement. It looks like it cannot find the added function but I am just guessing. Any suggestions?

Could it be a setting local to me on

FIXED!

I think the example xlsm down load is fine but the above code text included some funny error in the call to

SingleCellExtract = Left(Result, Len(Result) – 1)

It seems there is a funny character in there after copy / paste. As I just deleted it and typed it by hand and then it was fine.

Really pleased as does exactly what I need.

hi! thanks for this vba – how do i correct it so the vlookup value is not case sensitive? currently if I’m looking up “Apple” for example, I need to type “Apple”. If I use a lowercase “a”, it doesn’t lookup the value. Thank you!

Hey Ashley.. You can use the following code for it:

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 LCase(LookupRange.Cells(i, 1)) = LCase(Lookupvalue) Then

Result = Result & ” ” & LookupRange.Cells(i, ColumnNumber) & “,”

End If

Next i

SingleCellExtract = Left(Result, Len(Result) – 1)

End Function

THANK YOU SO MUCH!!! it works!!

Hi Sumit, Great Job i was searching for this exact one. i am not getting the value with the cell having particular sting. Kindly help how we can get the cell having particular sting. pls help

for example:

https://uploads.disquscdn.com/images/72b430f0123fb5f1ca1275477c73ded853c901e314ec1f7da59821b316c02e9d.jpg

please sir upload it again ASAP because i need it

Hey.. Sorry for the misisng file.. I have uploaded it!

please sir upload excel file regarding this topic because this excel sheet not found

I wonder if anyone will still respond to this thread….

I am trying to use this macro, but I get the below error:

Compile Error:

Syntax Error

and it highlights the below portion, as the step it get stuck on

SingleCellExtract = Left(Result, Len(Result) – 1)

My data is 3 columns, my formula goes as follows:

=SingleCellExtract(E3,$A:$C,3)

E3 is the value (a number) i want it to find in the range

$A:$C is the range

3 is the 3rd column where i want the formula to look to pull out the results (result is text).

What am i doing incorrectly?

Hello Laura.. Would be great if you could share the file with me. You can send it at sumitbansal@trumpexcel.com

Actually, I have the same problem, even copying your cells to my workbook. How did you resolved it?

Cheers,

Pier

I’m not sure if anyone is still checking this thread, but by trial and error I was able to make it work by adding some additional parentheses around the Len function:

SingleCellExtract = Left(Result, (Len(Result) – 1))

Not sure why VBA prefers it that way now, but it appears to work.

This worked perfectly! Thank you for the tip!

Hi Sumit,

Another great idea from you. Code optimised and options integrated

Public Function fLookUpMultiple(ByRef LookUpValue As String, _

ByRef LookUpRange As Excel.Range, _

ByRef ColumnNumber As Long, _

Optional ByRef bUnique As Boolean = True) As String ‘Variant

‘Get all values from a list that match specific value

Dim lgRow As Long

Dim strFilter As String

Dim lgElement As Long

For lgRow = 1 To LookUpRange.Columns(1).Cells.Count

If bUnique Then

If LookUpRange.Cells(lgRow, 1).Value2 = LookUpValue Then

For lgElement = 1 To lgRow – 1

If LookUpRange.Cells(lgElement, 1).Value2 = LookUpValue Then

If LookUpRange.Cells(lgElement, ColumnNumber).Value2 = LookUpRange.Cells(lgRow, ColumnNumber).Value2 Then GoTo Skip

End If

Next lgElement

strFilter = strFilter & ” ” & LookUpRange.Cells(lgRow, ColumnNumber) & “,”

Skip:

End If

Else

If LookUpRange.Cells(lgRow, 1).Value2 = LookUpValue Then strFilter = strFilter & ” ” & LookUpRange.Cells(lgRow, ColumnNumber).Value2 & “,”

End If

Next lgRow

‘Delete last “,”

fLookUpMultiple = VBA.Left(strFilter, VBA.Len(strFilter) – 1)

End Function

I have a question! Student names with ID’s and Class groups. How to take the list and use a combo box – select the class and the names appear on another sheet with their ID’s and class name.

Hi Joe.. Have a look at this – http://trumpexcel.com/2013/07/extract-data-from-drop-down-list/

It does what you are looking for, and uses a data validation drop down instead of a combo box. But it can be easily replicated for a combo box as well. Hope this helps!

Great Job Bro…But how can we get the results without duplicates?

Hi Saji, Glad you liked it.

To get values without duplicates, use this code:

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

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

SingleCellExtract = Left(Result, Len(Result) – 1)

End Function

Can u explain me this VB code?