Fórmula genérica
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Resumo
Para extrair todas as correspondências com base em uma correspondência parcial, você pode usar uma fórmula de matriz com base nas funções INDEX e AGGREGATE, com suporte de ISNUMBER e SEARCH. No exemplo mostrado, a fórmula em G5 é:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
com os seguintes intervalos nomeados: "pesquisa" = D5, "ct" = D8, "dados" = B5: B55.
Observação: esta é uma fórmula de matriz, mas não requer control + shift + enter, uma vez que AGGREGATE pode manipular matrizes nativamente.
Explicação
O núcleo desta fórmula é a função INDEX, com AGGREGATE usado para descobrir a "enésima correspondência" para cada linha na área de extração:
INDEX(data,nth_match_formula)
Quase todo o trabalho consiste em descobrir e relatar quais linhas em "dados" correspondem à string de pesquisa e relatar a posição de cada valor correspondente para INDEX. Isso é feito com a função AGGREGATE configurada assim:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
O primeiro argumento, 15, diz a AGGREGATE para se comportar como SMALL e retornar os valores enésimos menores. O segundo argumento, 6, é uma opção para ignorar erros. O terceiro argumento é uma expressão que gera uma matriz de resultados correspondentes (descritos abaixo). O quarto argumento, F5, atua como "k" em SMALL para especificar o valor "enésimo".
AGGREGATE opera em matrizes, e a expressão abaixo cria uma matriz para o terceiro argumento dentro de AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Aqui, a função ROW é usada para gerar uma matriz de números de linha relativos e ISNUMBER e SEARCH são usados juntos para corresponder a string de pesquisa aos valores nos dados, o que gera uma matriz de valores TRUE e FALSE.
A parte inteligente é dividir os números das linhas pelos resultados da pesquisa. Em uma operação matemática como esta, TRUE se comporta como 1 e FALSE como zero. O resultado é que os números das linhas associados a uma correspondência positiva são divididos por 1 e sobrevivem à operação, enquanto os números das linhas associados aos valores não correspondentes são destruídos e se tornam erros # DIV / 0. Como AGGREGATE está definido para ignorar erros, ele ignora os erros # DIV / 0 e retorna o menor número "enésimo" nos valores restantes, usando o número na coluna F para "enésimo".
Gerenciamento de desempenho
Como todas as fórmulas de matriz, essa fórmula é "cara" em termos de recursos com um grande conjunto de dados. Para minimizar os impactos no desempenho, toda a fórmula INDEX e MATCH é envolvida em IF assim:
=IF(F5>ct,"",formula)
onde o intervalo nomeado "ct" (D8) contém esta fórmula:
=COUNTIF(data,"*"&search&"*")
Esta verificação interrompe a execução das partes INDEX e AGGREGATE da fórmula, uma vez que todos os valores correspondentes tenham sido extraídos.
Fórmula de matriz com SMALL
Se a sua versão do Excel não tiver a função AGREGAR, você pode usar uma fórmula alternativa baseada em PEQUENO e SE:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
