Lookup and return multiple values in one cell

Lookup-and-return-multiple-values-in-one-cell-in-Excel

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:

  1. Use the MLOOKUP function.
  2. Add the lookup value.
  3. Select the lookup array.
  4. Select the return array.
  5. Press Enter.
  6. 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).

Download the practice file.

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.

If you want to implement the functions into your project manually, here are the source codes:

Function MLOOKUP(lookup_value As String, lookup_array As Range, return_array As Range, outputFormat As Integer, Optional caseSensitive As Boolean = False)
    Dim i As Long
    Dim matches() As String
    Dim matchCount As Integer
    matchCount = 0
    
    If Not caseSensitive Then
        lookup_value = LCase(lookup_value)
    End If
    
    For i = 1 To lookup_array.count
        If Not caseSensitive Then
            If LCase(lookup_array.Cells(i, 1).Value) = lookup_value Then
                ReDim Preserve matches(matchCount)
                matches(matchCount) = return_array.Cells(i, 1).Value
                matchCount = matchCount + 1
            End If
        Else
            If lookup_array.Cells(i, 1).Value = lookup_value Then
                ReDim Preserve matches(matchCount)
                matches(matchCount) = return_array.Cells(i, 1).Value
                matchCount = matchCount + 1
            End If
        End If
    Next i
    
    If outputFormat = 0 Then
        MLOOKUP = Application.Transpose(matches)
    Else
        MLOOKUP = Join(matches, ", ")
    End If
End Function
Function MLOOKUP_NR(lookup_value As String, lookup_array As Range, column_number As Integer)
Dim i, j As Long
Dim result As String
For i = 1 To lookup_array.Columns(1).Cells.count
  If lookup_array.Cells(i, 1) = lookup_value Then
    For j = 1 To i - 1
    If lookup_array.Cells(j, 1) = lookup_value Then
      If lookup_array.Cells(j, column_number) = lookup_array.Cells(i, column_number) Then
        GoTo Skip
      End If
    End If
    Next j
    result = result & " " & lookup_array.Cells(i, column_number) & ","
Skip:
  End If
Next i
MLOOKUP_NR = Trim(Left(result, Len(result) - 1))
End Function