Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VLookup Return Multiple Values in One Cell Separated by a Comma in 4 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

Excel VLookup Return Multiple Values in One Cell Separated by a Comma in 4 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)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.

The VLookup to return multiple values in one cell (separated by a comma) formula template/structure you learn in this Tutorial isn't the only way to carry out a VLookup and return multiple values. You may (also) be interested in the following alternative formula template/structures:

  • Excel VLOOKUP Return Multiple Values with Helper Column.
  • Excel VLookup Return Multiple Values with the INDEX Function.
  • Excel VLookup Return Multiple Values with the FILTER Function.
  • Excel VLookup Return Multiple Values in One Cell Separated by a Comma.

I link to these Tutorials in the Related Excel Training Materials and Resources Section below.

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. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

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.

My Excel XLOOKUP Tutorial (click here to open) may help you:

  • Better understand and implement the contents below.
  • Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link). The following are some of the topics covered in these Excel Courses:

  • Excel essentials and must-know skills.
  • Advanced Excel Formulas.
  • Excel Tables.
  • Pivot Tables.
  • Dashboards.
  • Power Pivot.
  • Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

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 (including Excel 365).
  • Is an array formula. If you're working with Excel 2019 or earlier, 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,""))}


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

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. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

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) example 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) example formula.
  • Formula: Cell L8:
    • Currently display the #N/A error.
    • Will display the VLookup return multiple values in one cell (separated by a comma) example formula.
Example: Data for Excel VLookup return multiple values in one cell separated by a comma


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

Step 1: Set Up the VLookup Test

Create an expression that does the following:

  1. 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
  2. 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.


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

Step 1 Example

In the example worksheet:

  • The lookup value is stored in cell J8 ($J8, when using a mixed reference).
  • The column I search in is column A (cells A8 to A27; $A$8:$A$27 when using absolute references).
'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.
Example: Set of VLookup test to return multiple values in one cell separated by a comma


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

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:

  1. Checks whether a condition is met (an expression returns TRUE).
  2. 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,"")

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


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

Step 2 Example

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

'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).
Example: Extract values for Excel VLookup return multiple values in one cell separated by a comma


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

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 (including Excel 365).

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

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


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

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,""))
Example: Concatenate values and separate by a comma to return multiple values in one cell


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

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 working with Excel 2019 or earlier, enter this VLookup return multiple values in one cell (separated by a comma) formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

Step 4 Example

The image below displays the VLookup return multiple values in one cell (separated by a comma) example formula wrapped in curly braces ({}) if I press “Ctrl + Shift + Enter” to enter it. The results are the same as those displayed in step #3.

Example: Excel VLookup return multiple values in one cell separated by a comma as an array formula


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

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. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VLookup return multiple values in one cell separated by a comma workbook example

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.

My Excel XLOOKUP Tutorial (click here to open) may help you:

  • Better understand and implement the contents above.
  • Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link). The following are some of the topics covered in these Excel Courses:

  • Excel essentials and must-know skills.
  • Advanced Excel Formulas.
  • Excel Tables.
  • Pivot Tables.
  • Dashboards.
  • Power Pivot.
  • Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

Subscribe
Notify of
guest
1 Comment
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure