Eu não acredito que haja uma maneira de fazer o tipo de pesquisa que você está procurando sem uma macro. Eu posso estar errado, mas se não estou, aqui está uma função personalizada para você.
Essa coisa parece funcionar sem nenhum pré-processamento. Você iria usá-lo como tal:
Na célula ao lado de 1, a fórmula seria
=ReverseTrace(A10,$B$1:$B$7,-1)
Assumindo:
- A10 é a localização de "1" (o valor que você está pesquisando)
- $ B $ 1: $ B $ 7 é o local da sua lista de itens (1, 2, 3 até 2, 4)
- -1 é o deslocamento da coluna desta lista de $ B $ 1: $ B $ 7 que você deseja consultar
Próximo a 2, seria, portanto, = ReverseTrace (A11, $ B $ 1: $ B $ 7, -1)
Function ReverseTrace(varValue As Variant, lookupRange As Range, intTraceOffset As Integer)
Dim rngCell As Range
For Each rngCell In lookupRange
If InStr(1, CStr(rngCell.value), CStr(varValue)) > 0 Then
If Len(ReverseTrace) > 0 Then
ReverseTrace = ReverseTrace & ", " & rngCell.Offset(0, intTraceOffset)
Else
ReverseTrace = rngCell.Offset(0, intTraceOffset)
End If
End If
Next
End Function