Como trata-se de uma pesquisa que pode retornar de Zero a N resultados, então deve ser tratada como Array.
Então a maneira de utilizar a função altera-se um pouco.
Insira a formula em E1=PesquisaItem(D1;C1;0), então seleciona a range E1:E30 (por exemplo), tecla F2, então tecle Ctrl+Alt+Enter pois trata-se de uma matricial
A função ficara =/- assim

'byJackSSL
'recebe item a pesquisar,coluna a efetuar a pesquisa e tipo de pesquisa.
'retorna todas as ocorrências do item pesquisado
'se passar 1 como tipo pesquisa, retorna somente palavra exata, 0 retorna palavra semelhante
Public Function PesquisaItem(ByVal strpesquisa As String, _
                             ByVal coluna_pesquisa As Range, _
                             ByVal tipo_pesquisa As Integer) As Variant
On Error Resume Next

Dim linha_leitura As String
Dim i As Integer, ultima_linha As Integer, x As Integer
Dim vEncontrado() As String
Dim tem_resultado As Boolean
Dim cIni As Long, lIni As Long
Dim Resultou, RowNdx, ColNdx

cIni = ActiveCell.Column: lIni = ActiveCell.Row
'pegamos a última linha preenchida da coluna
ultima_linha = CInt(Range(coluna_pesquisa.Address).End(xlDown).Cells.Row) + 1
tem_resultado = False

'se pesquisa foi preenchida pecorremos 1 a 1 as células da coluna selecionada em busca de ocorrências
If strpesquisa <> Empty Then
    For i = 0 To ultima_linha - 1 Step 1
        linha_leitura = Plan1.Cells(i, Range(coluna_pesquisa.Address).End(xlDown).Cells.Column)
        'se tipo da pesquisa = 1 (pesquisa exata)
        If tipo_pesquisa = 1 Then
            If linha_leitura = strpesquisa Then
                ReDim Preserve vEncontrado(0 To x)
                tem_resultado = True
                vEncontrado(x) = linha_leitura
                x = x + 1
            End If
        Else
            'pesquisa ocorrencia contendo a palavra chave
            If InStr(1, linha_leitura, strpesquisa) Then
                ReDim Preserve vEncontrado(0 To x)
                tem_resultado = True
                vEncontrado(x) = linha_leitura
                x = x + 1
            End If
        End If
    Next i
    If Not tem_resultado Then
        ReDim vEncontrado(1)
        vEncontrado(0) = "nenhuma ocorrência da palavra pesquisada foi encontrada!"
    Else
    End If
Else
End If
ReDim Resultou(1 To UBound(vEncontrado) + 1, 1 To 1)
    
For RowNdx = 1 To UBound(vEncontrado) + 1
    For ColNdx = 1 To 1
    Resultou(RowNdx, ColNdx) = vEncontrado(RowNdx - 1)
    Next ColNdx
Next RowNdx
PesquisaItem = Resultou
End Function

Muito obrigado!!

Meu problema foi solucionado, passei dias tentando descobrir e não consegui.

Meus parabéns!!

Abraço.