## Lookup multiple values in one cell

This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a User defined Function or an Excel 365 dynamic array formula.

**Table of Contents**

## 1. Lookup multiple values in one cell [UDF]

A UDF (User defined Function) is an Excel function that you can build yourself which is great if you can't find a prebuilt one that suits your needs.

You create a UDF the same way you create a macro using VBA code in the VB Editor (Visual Basic Editor), VBA stands for Visual Basic for Applications.

I will explain in greater detail the code I used, where to put it, and how to save your workbook, later in this article.

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) let's 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. Filename: APPS).

I want to start 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

Worksheet Sheet2 contains the items and the corresponding applications.

Worksheet Sheet1 contains the concatenated items in column A and a formula in column B. The first argument in the UDF is the cell that contains the concatenated values, this cell reference is relative meaning it changes when you copy cell B2 and paste it to cells below.

The second argument is the lookup range and the third argument is the return range, both these arguments have absolute cell references.

Lookup_concat(*Search_string, Search_in_col , Return_val_col* )

Formula in cell B2:

### VBA code

'Name the UDF and declare arguments and data types Function Lookup_concat(Search_string As String, _ Search_in_col As Range, Return_val_col As Range) 'Dimension variables and declare data types Dim i As Long, result As String Dim Search_strings, Value As Variant 'Split string using a delimiting character and return an array of values Search_strings = Split(Search_string, ";") 'Iterate through values in array For Each Value In Search_strings 'Iterate through from 1 to the number of cells in Search_in_col For i = 1 To Search_in_col.Count 'Check if cell value is equal to value in variable Value If Search_in_col.Cells(i, 1) = Value Then 'Save the corresponding return value to variable result result = result & " " & Return_val_col.Cells(i, 1).Value End If 'Continue with next number Next i 'Continue with next value Next Value 'Return values saved to result to worksheet Lookup_concat = Trim(result) End Function

### Where to put the code?

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

- Copy VBA code above.
- Open VB Editor (Alt+F11) and select your workbook in the Project Explorer.
- Press with left mouse button on Insert on the menu.
- Press with left mouse button on Module to create a module.
- Paste code to module1
- Exit VB Editor

## 2. Lookup multiple values in one cell - Excel 365

This Excel 365 formula splits a given cell value based on a specific delimiter and performs a lookup for each substring, then the corresponding value on the same row is returned and concatenated together with the remaining results.

Excel 365 formula in cell C3:

### Explaining formula

#### Step 1 - Split cell value

The TEXTSPLIT function splits a string into an array based on delimiting values.

Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(B3, ";")

becomes

TEXTSPLIT("AD1;AD2;AD3", ";")

and returns

{"AD1", "AD2", "AD3"}.

#### Step 2 - Compare substrings to a lookup table

The equal sign lets you compare value to value, you can also compare array to array as long as the first array is horizontal and the second array is vertical or vice versa.

TEXTSPLIT(B3, ";")=$E$3:$E$11

becomes

{"AD1", "AD2", "AD3"}={"AD1";"AD4";"AD1";"AD2";"AD2";"AD4";"AD3";"AD3";"AD5"}

and returns

{TRUE,FALSE,FALSE ; FALSE,FALSE,FALSE ; TRUE,FALSE,FALSE ; FALSE,TRUE,FALSE ; FALSE,TRUE,FALSE ; FALSE,FALSE,FALSE ; FALSE,FALSE,TRUE ; FALSE,FALSE,TRUE ; FALSE,FALSE,FALSE}

#### Step 3 - Convert boolean values

Step 2 returned an array of boolean values, the MMULT function can't handle boolean values. The asterisk character lets you multiply values in an Excel formula, it is also possible to multiply boolean values and in the process create their numerical equivalents.

TRUE equals 1 and FALSE equals 0 (zero).

(TEXTSPLIT(B3, ";")=$E$3:$E$11)*1

becomes

{TRUE,FALSE,FALSE ; FALSE,FALSE,FALSE ; TRUE,FALSE,FALSE ; FALSE,TRUE,FALSE ; FALSE,TRUE,FALSE ; FALSE,FALSE,FALSE ; FALSE,FALSE,TRUE ; FALSE,FALSE,TRUE ; FALSE,FALSE,FALSE}*1

and returns

{1,0,0 ; 0,0,0 ; 1,0,0 ; 0,1,0 ; 0,1,0 ; 0,0,0 ; 0,0,1 ; 0,0,1 ; 0,0,0}.

#### Step 4 - Create an array

We need an array of numbers that match the number of substrings found in the cell, they all need to be 1 so the MMULT function can calculate properly.

The ISTEXT function returns TRUE if argument is text.

Function syntax: ISTEXT(value)

ISTEXT(TEXTSPLIT(B3,,";"))

becomes

ISTEXT({"AD1"; "AD2"; "AD3"})

and returns

{TRUE; TRUE; TRUE}.

#### Step 5 - Convert boolean values

The power of character ^ lets you convert the boolean value to 1 if you calculate the power of 0 (zero).

ISTEXT(TEXTSPLIT(B3, , ";"))^0

becomes

{TRUE; TRUE; TRUE}^0

and returns {1; 1; 1}.

#### Step 6 - Sum numbers row-wise

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

Function syntax: MMULT(array1, array2)

MMULT((TEXTSPLIT(B3, ";")=$E$3:$E$11)*1, ISTEXT(TEXTSPLIT(B3, , ";"))^0)

becomes

MMULT({1,0,0 ; 0,0,0 ; 1,0,0 ; 0,1,0 ; 0,1,0 ; 0,0,0 ; 0,0,1 ; 0,0,1 ; 0,0,0},{1; 1; 1})

and returns

{1; 0; 1; 1; 1; 0; 1; 1; 0}.

#### Step 7 - Filter values based on array

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER($F$3:$F$11, MMULT((TEXTSPLIT(B3, ";")=$E$3:$E$11)*1, ISTEXT(TEXTSPLIT(B3, , ";"))^0))

becomes

FILTER({"APP1";"APP2";"APP3";"APP4";"APP5";"APP6";"APP7";"APP8";"APP9"}, {1; 0; 1; 1; 1; 0; 1; 1; 0})

and returns

{"APP1"; "APP3"; "APP4"; "APP5"; "APP7"; "APP8"}.

#### Step 8 - Concatenate values

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(" ", TRUE, FILTER($F$3:$F$11, MMULT((TEXTSPLIT(B3, ";")=$E$3:$E$11)*1, ISTEXT(TEXTSPLIT(B3, , ";"))^0)))

becomes

TEXTJOIN(" ", TRUE, {"APP1"; "APP3"; "APP4"; "APP5"; "APP7"; "APP8"})

and returns

"APP1 APP3 APP4 APP5 APP7 APP8".

### User defined function category

This user defined function creates a unique distinct list of words and how many times they occur in the selected […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]

The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]

This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]

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

This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]

The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]

This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]

Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]

This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]

Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]

This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]

In this vba tutorial I am going to show you how to return values from an udf, depending on where […]

This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]

## Functions in this article

More than 1300 Excel formulas

## Excel categories

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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)))