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

- 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 200+ new Excel functions) with Intellisense (intelligent text completion) **from January 31, 2023**.

**You can 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:

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