In this **Excel VLookup to return multiple values in one cell (separated by a comma) Tutorial**, you learn how to:

- Carry out a VLookup; and
- Return multiple values:
- In one cell; and
- Separated by a comma.

This Excel VLookup to return multiple values in one cell (separated by a comma) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. **You can get this example workbook (for free) by clicking the button below**.

Use the following Table of Contents to navigate to the Section you're interested in.

## Related Excel VLookup Tutorials

This Excel VLookup to return multiple values in one cell (separated by a comma) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

- Excel VLOOKUP Tutorial (under development): Click here to open.
- Excel VLOOKUP from Another Sheet in 4 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with INDEX MATCH in 4 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with the FILTER Function in 2 Easy Steps: Click here to open.
- Excel VLOOKUP Return Multiple Values with Helper Column in 4 Easy Steps: Click here to open.
- Excel VLookup Return Multiple Values with the INDEX Function in 7 Easy Steps: Click here to open.
- Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to open.

## The VLookup Return Multiple Values in One Cell (Separated by a Comma) Formula Template/Structure

The following is the VLookup return multiple values in one cell (separated by a comma) formula template/structure I explain (step-by-step) in the Sections below.

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-values-cell-comma/
=TEXTJOIN(", ",TRUE,IF(ColumnYouSearchIn=LookupValue,ColumnWithValuesToReturn,""))
```

This formula:

- Relies on the TEXTJOIN function. The TEXTJOIN function is available in Excel 2019 and later versions (including Excel 365).
- Is an array formula. If you're not working with Excel 365, you may have to enter this formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-values-cell-comma/
{=TEXTJOIN(", ",TRUE,IF(ColumnYouSearchIn=LookupValue,ColumnWithValuesToReturn,""))}
```

## The Example Before VLookup Return Multiple Values in One Cell (Separated by a Comma)

This Excel VLookup to return multiple values in one cell (separated by a comma) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. **You can get this example workbook (for free) by clicking the button below**.

The example worksheet has 2 tables/sections with the following characteristics.

**(1) Table 1 (Cells A7 to H27).**

The table:

- With the data.
- Where you search with the VLookup to return multiple values in one cell (separated by a comma) formula.

Main characteristics:

- 8 columns (Salesperson, Date, Customer, Product, City, Units, Unit Price, Total Sales).
- 1 header row (row 7).
- 20 entries (rows 8 to 27).

**(2) Table 2 (Cells J7 to L8).**

The table (with 1 row, in addition to the header row) where you:

- Set up the VLookup return multiple values in one cell (separated by a comma) formula.
- Display the results.

This table has the following 3 columns:

- Salesperson: Cell J8 contains the criterion/value (lookup value) you look up in Table 1.
- Total Sales: Cell K8:
- Is currently empty.
- Will store the VLookup return multiple values in one cell (separated by a comma) formula.

- Formula: Cell L8:
- Currently display the #N/A error.
- Will display the VLookup return multiple values in one cell (separated by a comma) formula.

## Step 1: Set Up the VLookup Test

Create an expression that does the following:

- Test whether the values stored in the column you search in (with the VLookup criteria) match the value you search for (the lookup value); and
- Return a Boolean value (TRUE or FALSE) for each row (in the column you search in), indicating whether the value stored in the applicable row matches (or not) the lookup value.

To do this inside a VLookup return multiple values in one cell (separated by a comma) formula, work with the equal to comparison operator (=).

Use the following template/structure to set up the VLookup test:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-values-cell-comma/
ColumnYouSearchIn=LookupValue
```

The expression template/structure I describe above returns an array of TRUEs and FALSEs.

### Step 1 Example

In the example worksheet:

- The lookup value is stored in cell J8.
- The column I search in is column A (cells A8 to A27; $A$8:$A$27).

The resulting expression (using a relative reference for cell J8, and absolute references for cells A8 to A27) is:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-values-cell-comma/
$A$8:$A$27=$J8
```

The image below illustrates how this expression returns:

- TRUE or FALSE for each row (in the column I search in; column A), indicating whether the value stored in the applicable row matches (or not) the lookup value (Salesperson 10).
- Column I (Step 1 Array) returns TRUE or FALSE, depending on whether the value stored in column A (Salesperson) of the applicable row matches the lookup value (Salesperson 10).
- Cell J8 displays the formula (using the basic structure of the expression carrying out the VLookup test).

- An array of TRUEs and FALSEs.

## Step 2: Extract the Values in the Rows Where the Lookup Value is Found

Use the IF function to create an array with the values stored in the rows (in the column with the values to return) where the lookup value is found.

The IF function:

- Checks whether a condition is met (an expression returns TRUE).
- Returns:
- One value if the condition is met (the expression returns TRUE).
- A different value if the condition isn't met (the expression returns FALSE).

Specify the arguments of the IF function as follows when creating a VLookup return multiple values in one cell (separated by a comma) formula:

- Logical_test: The array of TRUEs and FALSEs you created in step #1.
- Value_if_true: The column with the values to return.
- Value_if_false: An empty string (“”).

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-values-cell-comma/
IF(ArrayFromStep1,ColumnWithValuesToReturn,"")
```

When considering the array you created in step #1:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-values-cell-comma/
IF(ColumnYouSearchIn=LookupValue,ColumnWithValuesToReturn,"")
```

### Step 2 Example

The column with the values to return is column H (cells H8 to H27; $H$8:$H$27).

Considering the array I created in step #1:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-values-cell-comma/
IF($A$8:$A$27=$J8,$H$8:$H$27,"")
```

The image below illustrates how this expression returns one of the following:

- The value in the applicable column (with the values to return), if:
- The lookup value is found in the applicable row; and
- The expression (Logical_Test argument of the IF function) returns TRUE.

- An empty string, if:
- The lookup value isn't found in the applicable row; and
- The expression (Logical_Test argument of the IF function) returns FALSE.

For these purposes:

- Column K (IF Function Output) returns:
- The values (in the column with the values to return) in the rows where the lookup value is found; or
- An empty string in the rows where the lookup value isn't found.

- Cell L8 displays the formula (using the basic structure of the expression extracting the value in the rows where the lookup value is found).

## Step 3: Concatenate the Values in the Rows Where the Lookup Value is Found and Separate Them by a Comma

Use the TEXTJOIN function to:

- Concatenate the values in the rows where the lookup value is found (obtained in step #2); and
- Separate those values by a comma.

Specify the arguments of the TEXTJOIN function as follows when creating a VLookup return multiple values in one cell (separated by a comma) formula:

- Delimiter: A string containing a comma followed by a space (“, “).
- Ignore_Empty: TRUE.
- Text1: The values in the rows where the lookup value is found (obtained in step #2).

The TEXTJOIN function is available in Excel 2019 and later versions (including Excel 365).

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-values-cell-comma/
=TEXTJOIN(", ",TRUE,ValuesFromStep2)
```

When considering the IF function you created in step 2:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-values-cell-comma/
=TEXTJOIN(", ",TRUE,IF(ColumnYouSearchIn=LookupValue,ColumnWithValuesToReturn,""))
```

### Step 3 Example

Considering the IF function I created in step #2:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-values-cell-comma/
=TEXTJOIN(", ",TRUE,IF($A$8:$A$27=$J8,$H$8:$H$27,""))
```

## Step 4: Enter the Formula as an Array Formula

The VLookup return multiple values in one cell (separated by a comma) formula template/structure you learned in this Tutorial is an array formula.

If you're not working with Excel 365, you may have to enter this formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

### Step 4 Example

The image below displays the formula wrapped in curly braces ({}) if I press “Ctrl + Shift + Enter” to enter it. The results are the same as those displayed step #3.

## Download the VLookup Return Multiple Values in One Cell (Separated by a Comma) Example Workbook

This Excel VLookup to return multiple values in one cell (separated by a comma) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process above. **You can get this example workbook (for free) by clicking the button below**.

## Related Excel VLookup Tutorials

This Excel VLookup to return multiple values in one cell (separated by a comma) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

- Excel VLOOKUP Tutorial (under development): Click here to open.
- Excel VLOOKUP from Another Sheet in 4 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with INDEX MATCH in 4 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with the FILTER Function in 2 Easy Steps: Click here to open.
- Excel VLOOKUP Return Multiple Values with Helper Column in 4 Easy Steps: Click here to open.
- Excel VLookup Return Multiple Values with the INDEX Function in 7 Easy Steps: Click here to open.
- Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to open.