Formula para buscar texto y devolver valor de todas las celdas contiguas en que aparezca

Es posible que se pueda hacer solo a base de funciones, pero creo que mucho más complicado y, al menos yo, no esto seguro poder encontrar la combinación de fórmulas adecuada, sin sobrepasar el límite de anidamiento de Excel.

Por ello, te propongo uses la siguiente función de usuario. Copia y pega el código en un módulo:

Function CompletaIguales(r1 As Range, r2 As Range) As String
'Busca en la fila actual de r1 el valor y cocatena en la celda
'actual todos los valores de r2 con el mismo valor en r1
Dim f As Integer
Dim c As Range
Dim vbuscar As String
Dim res As String
Dim subres As String
res = ""
f = Application.ThisCell.Row 'Fila donde está el elemento actual
'buscamos en r1 el elemento de la fila f
For Each c In r1
If c.Row = f Then 'es este
vbuscar = c.Value
Exit For
End If
Next c
'buscamos repetidos en r1
For Each c In r1
If c.Value = vbuscar Then 'es uno de ellos
'Miramos si en r2 misma fila hay algo
If Cells(c.Row, r2.Column) > "" Then
subres = Cells(c.Row, r2.Column)
If InStr(rews, subres) = 0 Then 'hay que incluirlo
res = res & " - " & subres
End If
End If
End If
Next c
CompletaIguales = res
End Function

Ahora si escribes en la primera celda de la columna E lo siguiente:

=CompletaIguales($D$1:$D$8;$C$1:$C$8)

Y arrastras la fórmula por toda la columna E obtendrás los resultados. Bueno a mi me da diferente el último, pero creo que es una errata tuya al escribir el ejemplo.

Si quieres ver la función está en el grupo de funciones "User Defined" o definidas por el usuario.

Eres un fiera!!!

Funciona a la perfección. Además tienes razón, el resultado que pongo en la fila 8 es un error que cometí yo, no la función que has desarrollado.

Sólo una última aclaración: como ya supondrás, el ejemplo que puse es sólo eso, un ejemplo. En la tabla real no coinciden los datos en la misma columna. Tendría que modificar el código para adaptarlo a las circunstancias concretas de mi hoja?. Creo que no, porque ya hice una prueba en una nueva columna con otros datos y funciona también perfectamente.

Un millón de gracias.

Comprobado! También funciona correctamente en mi hoja de cálculo a pesar de todas las configuraciones y macros que tiene. Ya no te doy más la lata.

Gracias por todo.