Lookup and return multiple values in one cell

To return multiple lookup values in one cell in Excel separated by a comma, use the MLOOKUP or MLOOKUP_NR functions.

This tutorial will show you how to find a value in a column and concatenate multiple lookup values on the same row without using an array formula. However, even if you use built-in lookup functions in Excel, the question remains: What if you want to put multiple values in a single cell? Is it possible to use only a simple formula?

The MLOOKUP function is similar to XLOOKUP but can support multiple matches (return values) and display them in a single cell.

How to return multiple lookup values in one cell?

Here are the steps to get multiple values into a single cell:

Use the MLOOKUP function.
Add the lookup value.
Select the lookup array.
Select the return array.
MLOOKUP will return multiple values (comma-separated) in a single cell.

The MLOOKUP function

Take a look at the MLOOKUP syntax and arguments:

Syntax:

=MLOOKUP(lookup_value, lookup_array, return_array)

Arguments:

lookup value: the value you are looking for
lookup array: the range where we find the lookup value
return array: the list from which you want the result

Our user-defined function library, DataFX, will support the MLOOKUP function (and other 190+ new Excel functions) with Intellisense (intelligent text completion)

Download the practice file here.

Get multiple lookup values in a single cell

You can write easy-to-readable formulas using named ranges. In the first example, we have two lists that contain categories and products. First, select the B3:B12 range, click the name box, and add a name to a range, in this case, “category”. The next step is to select the range C3:C12 and add a descriptive name to a range, “product”.

We aim to find all corresponding records for category “A”.

Formula:

=MLOOKUP(“A”, category, product)

The lookup value is “A”, and the lookup array where we find the matches is “category”. We’ll get multiple results in the “product” range and return all matching values in a single cell. By default, the MLOOKUP function uses a comma separator.

Result:

get multiple lookup values in a single cell comma separated list

Return multiple lookup values in one cell (unique values)

In the next example, the list contains redundant records (category: “A”; product: “kiwi”). First, try to extract all matching records in a single cell using the MLOOKUP function.

By default, the MLOOKUP function separates the values using a comma and does not handle duplicates. So, the result is:

={apple, banana, kiwi, kiwi, kiwi}

Return multiple lookup values in one cell unique values

To create a list that contains unique values, use the MLOOKUP_NR function:

Syntax:

=MLOOKUP_NR(lookup_value, range, column_number)

Select the range B3:C12 and create a named range, “data”. In this case, the formula in E6 is the following:

=MLOOKUP_NR(“A”, data, 2)

={apple, banana, kiwi}

The list contains unique values.