Fórmula genérica
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Resumen
Para extraer todas las coincidencias basadas en una coincidencia parcial, puede utilizar una fórmula de matriz basada en las funciones INDICE y AGREGAR, con el apoyo de ISNUMBER y SEARCH. En el ejemplo que se muestra, la fórmula en G5 es:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
con los siguientes rangos con nombre: "búsqueda" = D5, "ct" = D8, "datos" = B5: B55.
Nota: esta es una fórmula de matriz, pero no requiere control + shift + enter, ya que AGGREGATE puede manejar matrices de forma nativa.
Explicación
El núcleo de esta fórmula es la función ÍNDICE, con AGREGAR utilizado para calcular la "enésima coincidencia" para cada fila en el área de extracción:
INDEX(data,nth_match_formula)
Casi todo el trabajo consiste en averiguar y reportar qué filas en "datos" coinciden con la cadena de búsqueda, y reportar la posición de cada valor coincidente a INDEX. Esto se hace con la función AGREGAR configurada así:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
El primer argumento, 15, le dice a AGGREGATE que se comporte como PEQUEÑO y devuelva enésimo los valores más pequeños. El segundo argumento, 6, es una opción para ignorar errores. El tercer argumento es una expresión que genera una matriz de resultados coincidentes (descritos a continuación). El cuarto argumento, F5, actúa como "k" en PEQUEÑO para especificar el valor "n-ésimo".
AGGREGATE opera en matrices y la siguiente expresión crea una matriz para el tercer argumento dentro de AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Aquí, la función FILA se usa para generar una matriz de números de fila relativos, y ISNUMBER y SEARCH se usan juntos para hacer coincidir la cadena de búsqueda con los valores en los datos, lo que genera una matriz de valores VERDADERO y FALSO.
Lo más inteligente es dividir los números de fila por los resultados de la búsqueda. En una operación matemática como esta, VERDADERO se comporta como 1 y FALSO se comporta como cero. El resultado es que los números de fila asociados con una coincidencia positiva se dividen por 1 y sobreviven a la operación, mientras que los números de fila asociados con valores que no coinciden se destruyen y se convierten en errores # DIV / 0. Debido a que AGGREGATE está configurado para ignorar los errores, ignora los errores # DIV / 0 y devuelve el "n-ésimo" número más pequeño en los valores restantes, usando el número en la columna F para "n-ésimo".
Gestionar el rendimiento
Como todas las fórmulas de matriz, esta fórmula es "cara" en términos de recursos con un gran conjunto de datos. Para minimizar los impactos en el rendimiento, toda la fórmula de ÍNDICE y PARTIDO está envuelta en SI así:
=IF(F5>ct,"",formula)
donde el rango nombrado "ct" (D8) contiene esta fórmula:
=COUNTIF(data,"*"&search&"*")
Esta verificación detiene la ejecución de la parte INDICE y AGREGATE de la fórmula una vez que se han extraído todos los valores coincidentes.
Fórmula de matriz con PEQUEÑO
Si su versión de Excel no tiene la función AGREGAR, puede usar una fórmula alternativa basada en PEQUEÑO e SI:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
