Return multiple results in one cell

I did a little research and quickly found confirmed what I had thought initially. The solution you seek isn't available in Excel. The nearest you might get to a worksheet function is a UDF - still requiring VBA. Here is an example.

Option Explicit

Function MatchList(Cell As Range) As String
    ' 08 May 2018
    
    ' The "Data" range should be as small as possible, not including any
    ' unnecessary data, with the look-up value in its first column.
    
    Const Clm As Long = 2                   ' adjust as required
    ' Clm is the column of the "Data" range from which to extract the result.
    ' Example: if the "Data" range = C2:D100 then its first column would
    ' be column C with results in column D, so Clm = 2
    ' Clm = 2 would also apply if the "Data" range is A2:B100
    
    Dim Fun() As String
    Dim Arr() As Variant
    Dim ListSubject As Variant
    Dim n As Integer, i As Integer
    Dim R As Long
    
    Application.Volatile                    ' this line can be removed
    ' making this function "volatile" means that all its occurrances will
    ' be recalculated every time something is changed, anywhere in the sheet.
    ' That may slow down the sheet if there are many uses of this function
    ' or the "Data" range is very big. Try it out.
    ' When not Volatile the function will be recalculated when changed itself
    ' or the cell it refers to.
    
    ListSubject = Cell.Value
    With Range("Data")
        ' n = the number of occurrences of ListSubject
        n = Application.WorksheetFunction.CountIf(.Columns(1), ListSubject)
        Arr = .Value
    End With
    
    If n Then                               ' skip if not found
        ReDim Fun(1 To n)
        i = 1
        For R = 1 To UBound(Arr)
            If Arr(R, 1) = ListSubject Then
                Fun(i) = Arr(R, Clm)
                i = i + 1
                ' stop searching when all occurrences have been found:
                If i > n Then Exit For
            End If
        Next R
        MatchList = Join(Fun, ", ")         ' list separator = ", "
    End If
End Function

1. Select your data, starting with the the lookup values in the first selected column and the result data in the last. It doesn't matter how many columns are in between.
2. Type "Data" in the Name Bar (to the left of the Formula Bar above the worksheet grid)
   You can later modify the range using the Name Manager which is available from the ribbon's Formulas tab.
   You can use another name but then you need to repalce all occurrences of "Data" in the code with the name you chose.
3. Open the VB Editor (Alt+F11 will do that quickly).
4. In the Project Explorer window at the top left look for the name of the workbook in which you created the named "Data" range.
5. Right-click on that name. From the drop-down that opens select Insert and then Module. This inserts a blank code sheet called Module1 by default. You can change the name. Giving meaningful names is encouraged.
6. Paste the above code in the blank page on the right of your screen.
7. Save the workbook as macro-enabled in xlsm format. (The UDF should also work in Excel 2003.)

Now your UDF is installed and ready to work. I suggest you read the comments I have added to the code.

Use it as you would use any other function. Its name is MatchList and you will find it in the Intellisense dropdown as you start typing its name. Type, for example, =MatchList($A2). In my test A2 holds the value "1" and the formula duly returns "100, 300, 400" from the data you provided. You can copy the formula down to other cells just as would are used to do with others.