Excel Formula to Combine Multiple Lookup Results into a Comma-Separated List

Data analysts often struggle to aggregate multiple lookup matches into a single, readable cell, typically getting stuck with only the first returned value. When tracking project allocations across standard funding sources, traditional VLOOKUP formulas fall short. Fortunately, utilizing an advanced combination formula grants users seamless data consolidation, transforming fragmented rows into clean, actionable insights. Stipulation: This method requires Excel 365 or 2021 to support dynamic arrays. By leveraging the TEXTJOIN and FILTER functions to merge matching records, you can automate this tedious task. Below, we outline the exact formula syntax and provide a step-by-step configuration guide.

The Challenge: Going Beyond Standard Lookups

Excel is packed with powerful lookup functions like VLOOKUP, INDEX/MATCH, and the modern XLOOKUP. However, these formulas share a common limitation: they are designed to return only the first matching value they encounter in a dataset.

What happens when you have a one-to-many relationship in your data? For example, imagine you have a list of sales representatives and the clients they manage. A single representative might manage five different clients. If you attempt a standard VLOOKUP on the representative's name, Excel will only return the first client in the list.

To extract all matching clients and display them in a single cell as a clean, comma-separated list, you need to combine lookup logic with text concatenation. Depending on your version of Excel, there are several highly efficient ways to achieve this. In this guide, we will explore the best formulas and techniques to get this job done.

Method 1: The Modern Way (Excel 365 & Excel 2021+)

If you are using a modern version of Excel (Microsoft 365 or Excel 2021 and later), you have access to dynamic arrays. This makes combining multiple lookup results incredibly straightforward by nesting the FILTER function inside the TEXTJOIN function.

The Formula Syntax

=TEXTJOIN(", ", TRUE, FILTER(Return_Range, Criteria_Range = Lookup_Value, "No matches found"))

How It Works Step-by-Step

1. FILTER(Return_Range, Criteria_Range = Lookup_Value): This is the engine of the formula. It scans the Criteria_Range for matches to your Lookup_Value. For every match it finds, it extracts the corresponding value from the Return_Range and outputs them as a dynamic array.
2. TEXTJOIN(", ", TRUE, ...): This function takes the array generated by the FILTER function and joins the values together.
   • The first argument (", ") defines the; to separate the values.
   • The second argument (TRUE) instructs Excel to ignore any empty cells within the returned array, preventing double commas in your output.

Practical Example

Let's look at the following dataset containing employees and the projects they are assigned to:

If you want to find all projects assigned to Alice and output them in a single cell, your formula would look like this:

=TEXTJOIN(", ", TRUE, FILTER(B2:B6, A2:A6 = "Alice", "No Projects"))

Result: Project Alpha, Project Gamma, Project Epsilon

Method 2: Handling Duplicates with the UNIQUE Function

Sometimes, your source data might contain duplicate entries. In our previous example, if "Alice" was listed under "Project Alpha" twice, the standard TEXTJOIN + FILTER formula would display "Project Alpha" twice in your comma-separated list.

To ensure your output list contains only unique values, you can wrap the FILTER function inside the UNIQUE function before joining them:

The Formula Syntax

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(Return_Range, Criteria_Range = Lookup_Value, "No matches found")))

By adding UNIQUE, Excel filters the data first, strips out any duplicates, and then hands the cleaned array over to TEXTJOIN to construct your clean list.

Method 3: The Traditional Way (Excel 2016 & 2019)

If you are using Excel 2016 or Excel 2019, you have access to the TEXTJOIN function, but you do not have access to the dynamic FILTER function. To get around this limitation, you can construct an array formula using TEXTJOIN and IF.

The Formula Syntax

=TEXTJOIN(", ", TRUE, IF(Criteria_Range = Lookup_Value, Return_Range, ""))

Note: Because this is an array formula in older versions of Excel, you must press Ctrl + Shift + Enter instead of just Enter after typing the formula. When done correctly, Excel will wrap your formula in curly braces: {...}.

How It Works

• The IF statement checks each cell in the Criteria_Range. If it matches the Lookup_Value, it returns the corresponding value from the Return_Range. If it does not match, it returns an empty string ("").
• This generates an array of results that looks something like this: {"Project Alpha", "", "Project Gamma", "", "Project Epsilon"}.
• TEXTJOIN then steps in, ignores all the empty strings (thanks to the TRUE argument), and joins the remaining values together with your comma delimiter.

Method 4: Legacy Excel Versions (Excel 2013 & Older)

In legacy versions of Excel, neither TEXTJOIN nor FILTER exist. While you can technically nest multiple IF and CONCATENATE statements, this becomes incredibly messy and impractical for large datasets.

The most elegant solution for legacy Excel versions is to write a simple User Defined Function (UDF) using VBA (Visual Basic for Applications). This creates a custom formula that you can use directly inside your workbook.

The VBA Code

To add this custom function to your Excel sheet, press ALT + F11 to open the VBA editor, click Insert > Module, and paste the following code:

Function SingleCellLookup(LookupValue As Variant, LookupRange As Range, ResultRange As Range, Optional Delimiter As String = ", ") As String
    Dim i As Long
    Dim Result As String
    
    For i = 1 To LookupRange.Rows.Count
        If LookupRange.Cells(i, 1).Value = LookupValue Then
            If Result = "" Then
                Result = ResultRange.Cells(i, 1).Value
            Else
                Result = Result + Delimiter + ResultRange.Cells(i, 1).Value
            End If
        End If
    Next i
    
    SingleCellLookup = Result
End Function

How to Use It

Once the code is saved in your workbook, you can use it just like a native Excel function:

=SingleCellLookup("Alice", A2:A6, B2:B6)

This macro-powered solution is highly compatible and ensures that users running older editions of Excel are still able to accomplish the task seamlessly.

Best Practices & Performance Tips

While these formula configurations are incredibly useful, there are a few best practices to keep in mind when implementing them in large production workbooks:

• Avoid Entire Column References: Try to avoid referencing whole columns (e.g., A:A) inside your FILTER or IF array formulas. Doing so forces Excel to scan over a million rows, which can significantly slow down your calculation speeds. Instead, convert your data to an Excel Table (using Ctrl + T) and use structured table references.
• Handling Empty Cells: Make sure your source database does not contain unintended empty rows in the return range. If it does, TEXTJOIN might display consecutive delimiters unless you properly configure your criteria logic to filter out blanks.
• Sort Order: If you want your comma-separated output to be sorted alphabetically, you can wrap the SORT function around your filter result like this: =TEXTJOIN(", ", TRUE, SORT(FILTER(...))).

Conclusion

Combining multiple lookup results into a single, clean comma-separated list is a frequent business reporting requirement. If you are on Microsoft 365, using TEXTJOIN combined with FILTER provides an elegant, fast, and dynamic solution that updates automatically when your data changes. For older environments, array formulas and custom VBA functions provide robust workarounds that keep your data presentation clean and professional.