## Lookup multiple values in one cell (vba)

Hi Oscar...this is a very interesting function and helped me a lot so far.

My file though is a bit more complicated..

I have multiple info in one cell separated with ";" (example AD1; AD2; AD3) lets say that these are servers (File name SERVERS) and in each server I have multiple applications.

I have now another file that has all the applications per server per line in excel (each line has one server one application. File name: APPS).

I want starting from the file SERVERS to look up the servers that are in one cell find them in the second file APPS and bring all the applications also in one cell in the file SERVERS.

Any ideas here?

Thanks in advance

C

**Answer:**

Sheet 2

Sheet 1

Formula in cell B2:

=Lookup_concat(A2, Sheet2!$A$2:$A$10, Sheet2!$B$2:$B$10)

**VBA code**

Function Lookup_concat(Search_string As String, _ Search_in_col As Range, Return_val_col As Range) Dim i As Long, result As String Dim Search_strings, Value As Variant Search_strings = Split(Search_string, ";") For Each Value In Search_strings For i = 1 To Search_in_col.Count If Search_in_col.Cells(i, 1) = Value Then result = result & " " & Return_val_col.Cells(i, 1).Value End If Next i Next Value Lookup_concat = Trim(result) End Function

**What now?**

You can use the code (see instructions below) in your workbook or download the example file.

- Copy vba code
- Open VB Editor (Alt+F11)
- Click Insert
- Click Module
- Paste code to module1
- Exit VB Editor

**Download excel *.xlsm file**

### Category: User defined functions udf

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Comments(17) Filed in category: Excel, Sort values, Unique distinct values, User defined functions (udf), VBA

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Comments(16) Filed in category: Count values, Excel, Frequency, User defined functions (udf), VBA

Sometimes you need to find formulas containing literals (hard coded values) in a workbook. I found this excellent UDF in […]

Comments(12) Filed in category: Excel, User defined functions (udf), VBA

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

Comments(11) Filed in category: Excel, Unique distinct values, User defined functions (udf), VBA

### Category: Vba

This post describes how to copy values between sheets. I am using the invoice template sheet. This macro copies rows […]

Comments(40) Filed in category: Excel, VBA

This post describes how to copy selected rows using checkboxes. In a previous post I showed you how to add/remove […]

Comments(30) Filed in category: Check-boxes, Excel, VBA

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]

Comments(24) Filed in category: Excel, VBA

You can quickly run a macro using a drop down list. The drop down list may contain multiple macro names. […]

Comments(17) Filed in category: Drop down lists, Excel, VBA

### 6 Responses to “Lookup multiple values in one cell (vba)”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

When I tried this as the formula is repeats the answer over and over:

=IF(Sheet3!A2=Sheet4!A2,Lookup_concat(D2,Sheet4!$C:$C,Sheet4!$D:$D),Sheet3!G2)

How do i get it to display only once?

Kory

Also when I just do the regular formula it still repeats the results over and over again.

I am trying to use this formula and I get a #name error. I am inserting the code in the general module of the file but it gives me an error an error. I initially tried it, it worked, excel crashed and when I attempt again it does not work anymore.

Yolanda,

I am trying to use this formula and I get a #name error.You have misspelled the function name or put the code in the wrong module.

I am inserting the code in the general module of the file but it gives me an error an error.I don't know what "general module" is but you should put your code in the code module. There are instructions in this post, see above.

I initially tried it, it worked, excel crashed and when I attempt again it does not work anymore.Perhaps Excel was unsuccessful recovering all your data?

Hello, how can we get the same result but without concatenating the lookup.

Look at 5 cells in a row and then return the result, without duplicates in the result ?

Thanks

Roland

Is this what you are looking for?

Array formula in cell E2:

=INDEX($B$1:$B$9, SMALL(IF(COUNTIF($D$2:$D$5, $A$1:$A$9)>0, MATCH(ROW($B$1:$B$9), ROW($B$1:$B$9)), ""), ROWS($A$1:A1)))