Quote Originally Posted by Agent100 View Post
Has this been responded to? I have the same predicament that I am trying to solve for. In short, the lookup_Concat UDF works very well for validating a single criterion and returning the concatenated results into a single cell. However, in my particular use case, I have 2 criterion that require validation prior to returning the concatenated results. This would be equivalent to an "=if(and(" type function. Is this possible, and if so, any assistance in how to properly operate it would be very much appreciated.

Thanks in advance - and nice work on the UDF as it gets me 90% of the way there!

Agent100

So after some digging, I eventually found the below VBA. It works, but causes my spreadsheet to operate VERY slowly. Looking to use a macro now instead of calling the UDF in every cell....hopefully I am able to figure that out. In any event, figured it would be a good idea to share the VBA for the multiple criteria UDF. Here goes:

Function Lookup_concat(Search_stringA As String, Search_in_colA As Range, _
Search_stringB As String, Search_in_colB As Range, Return_val_col As Range)

Dim i As Long
Dim result As String

For i = 1 To Search_in_colA.Count
If Search_in_colA.Cells(i, 1) = Search_stringA And _
Search_in_colB.Cells(i, 1) = Search_stringB Then
result = result & " " & Return_val_col.Cells(i, 1).Value
End If
Next i

Lookup_concat = Trim(result)

End Function


The formula syntax in the cell would be:

=lookup_concat(cell of first value,'Sheet Name'!range:range,cell of second value,'Sheet name'!range:range,'Sheet name'!return results range: return results range)

For instance:
=lookup_concat(E$3,'Consolidated Data'!$I:$I,$B5,'Consolidated Data'!$E:$E,'Consolidated Data'!$A:$A)

Again, just trying to share what I have learned. Hopefully it helps others who are digging for the solution of multiple criteria. I will update if/when I am able to effectively write a macro to call this UDF vs. using the UDF (again, causes my workbook to perform SUPER slowly).

Cheers