A function to lookup a value and concatenate all the resulting results. This is useful if you have a list of emails in a row that have a common identifier such as an "account name" and you want to mail merge all of them in the "to" field.

Lookup_concat.vba

' https://www.get-digital-help.com/excel-udf-lookup-and-return-multiple-values-concatenated-into-one-cell/
' 03/08/2022
' Minor changes to fix my needs.

'Name user defined function and define parameters
Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range, delim_in_return As String)
 
'Dimension variables and declare data types
Dim i As Long
Dim result As String
 
'Iterate through each cell in search column
For i = 1 To Search_in_col.Count
 
'Check if cell is equal to search string
If Search_in_col.Cells(i, 1) = Search_string Then
 
'Concatenate corresponding value on the same row to the result variable
If i = 1 Then
result = Return_val_col.Cells(i, 1).Value
Else
result = result & delim_in_return & Return_val_col.Cells(i, 1).Value
End If

End If
 
'Continue with next cell
Next
 
'Return variable to worksheet
Lookup_concat = Trim(result)
 
End Function