I am using the following VBA to concatenate multiple results of Index Match formulas:
Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
result = result & ", " & Return_val_col.Cells(i, 1).Value
End If
Next
Lookup_concat = Mid(result, 3)
End Function
With the following formula:
=Lookup_concat(E2,Sheet2!$D$2:$D$2000,Sheet2!$N$2:$N$2000)
This is working well, however sometimes the N column in sheet2 contains a blank cell. In this scenario I want to return the value in the M column.
Please see attached xlsm.
Bookmarks