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