Quote Originally Posted by theladysaphir View Post
You are correct in your interpretation of what I am doing, and in any other case I would prefer your suggested output, but in this particular case I need the output in every cell.
Okay, here's the solution. Rather than create a new procedure, I simply wrote a macro that repeatedly calls my LookUpConcat function (while it can be used as a UDF, it is also a perfectly good VB function as well). So, put the LookUpConcat function in a module and then put this macro in the same module...
Code:
Sub LookUpAndConcatenate()
  Dim Cell As Range, LastRow As Long, ResultRow As Long
  Dim SearchRange As Range, ReturnRange As Range
  Const SearchCol As String = "B"
  Const ReturnCol As String = "AU"
  Const ResultCol As String = "D"
  Const StartRow As Long = 3
  LastRow = Cells(Rows.Count, SearchCol).End(xlUp).Row
  Set SearchRange = Cells(StartRow, SearchCol).Resize(LastRow - StartRow + 1)
  Set ReturnRange = Cells(StartRow, ReturnCol).Resize(LastRow - StartRow + 1)
  For Each Cell In SearchRange
    Cells(Cell.Row, ResultCol).Value = LookUpConcat(Cell.Value, SearchRange, ReturnRange)
  Next
End Sub
Then, when you want to fill the cells, just run the above LookUpAndConcatenate macro. Note that I used Const statements (they declare constants) to set the various parameters from your worksheet so that you can change them later on in case things change on your worksheet for some reason.