# 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 are using 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:

1. Use the MLOOKUP function.
3. Select the lookup array.
4. Select the return array.
5. 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 200+ new Excel functions) with Intellisense (intelligent text completion) from January 31, 2023.

### 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:

### 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}``

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. Istvan Vozar

Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps people to reach the top in Excel.