Comparing Cell to Range, is it possible to display all matches found in a single cell?

Try this.

paste the code below into a VBA module. And then use this formula =myFunction( "pen" , H5:I8 ) The H5:I8 will need to be changed to the two column range you have your data in.

Function myFunction(ByVal KeyValue As String, ByRef Data2Columns As Range) As String
    
    Dim str As String
    Dim r As Range
    Dim rng As Range
    
    Set r = Data2Columns.Resize(, 1)
    For Each rng In r
        If rng.Value = KeyValue Then
            str = str & ", " & rng.Offset(, 1).Value
        End If
    Next
    If str = "" Then
        myFunction = ""
    Else
        myFunction = Mid(str, 3, 1000)
    End If
End Function