Cuando los resultados de una fórmula de matriz son números, generalmente me resulta fácil encontrar un método apropiado para contraer la matriz en un solo resultado. Sin embargo, cuando los resultados de una fórmula de matriz son texto, me resulta difícil manipular la fórmula de una manera que proporcione un único resultado deseado. En breve,¿Hay algún método para manipular una matriz de resultados de texto que haya pasado por alto? Consulte la parte inferior de esta pregunta para obtener la fórmula final deseada que no funciona y solicite soluciones.

* Editar: después de leer esto nuevamente, puedo resumir alternativamente mi pregunta como: ¿hay alguna manera de acceder a múltiples elementos de texto desde un 'Resultado de matriz de fórmulas', sin seleccionar individualmente (por ejemplo: con ÍNDICE)?

Ejemplos donde funcionan las fórmulas de matriz, donde la matriz de resultados son valores numéricos

(1) Ejemplo 1: Suponga que la columna A filas 1-500 es una lista de ID de productos en el formato de xyz123, y la columna B filas 1-500 muestra las ventas totales de ese producto. Si quiero encontrar las ventas del producto con las ventas más altas, donde los últimos 3 dígitos de una ID están por encima de 400, podría usar una fórmula de matriz como esta (confirmada con CTRL + SHIFT + ENTER en lugar de solo ENTER):

=MAX(IF(VALUE(RIGHT(A1:A500,3))>400,B1:B500,""))

(2) Ejemplo 2 Ahora suponga que la columna B contiene nombres de productos, en lugar de Ventas. Ahora quiero simplemente devolver el primer nombre que coincida con los criterios de los últimos 3 dígitos de la identificación del producto> 400. Esto podría hacerse de la siguiente manera:

=INDEX(B1:B500,MIN(IF(VALUE(RIGHT(A1:A500,3))>400,ROW(A1:A500),"")))

Aquí, he hecho una pequeña manipulación, de modo que la parte de matriz real de la fórmula [IF (RIGHT (A1: A500,3 ...] devuelve un resultado de valor [las FILAS de las celdas A1: A500 donde están los últimos 3 dígitos por encima de 400]; por lo tanto, puedo usar MIN para mostrar solo el primer ROW # que coincida, y luego puedo usar ese resultado colapsado en una función INDEX regular.

(3) Ejemplo 3 Para un ejemplo final, vea la discusión sobre una pregunta similar aquí [Se profundiza más que mi ejemplo resumido a continuación, de una manera que no es directamente relevante para esta pregunta]:https://stackoverflow.com/a/31325935/5090027

Supongamos ahora que desea una lista de todos los nombres de productos, donde los últimos 3 dígitos de la ID del producto> 400. Que yo sepa, esto realmente no se puede hacer en una sola Celda, tendría que hacerse colocando cada resultado individual en una celda posterior. La siguiente fórmula podría colocarse, por ejemplo, en C1 y arrastrarse hacia abajo 10 filas, y luego mostraría los primeros 10 nombres de productos con los ID de producto que tienen los últimos 3 dígitos> 400.

=INDEX($B$1:$B$500,SMALL(IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($A$1:$A$500),""),ROW()))

Ejemplo donde las fórmulas de matriz no funcionarán, donde la matriz de resultados son valores de texto

Ahora suponga que quiero tomar los resultados en el Ejemplo 3 y realizar alguna manipulación de texto en ellos. Por ejemplo, suponga que quiero concatenarlos a todos en una sola cadena de texto. Lo siguiente no funciona, porque concatenate no tomará una serie de resultados como este como argumentos aceptables.

=CONCATENATE((IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($B$1:$B$500),"")))

Entonces la pregunta es: ¿Alguien sabe cómo hacer funcionar esta última fórmula? O, cómo hacer que funcione una fórmula que tome una matriz de resultados de texto, y que la convierta en un 'rango utilizable' [para que pueda conectarse a Concatenate arriba], o pueda manipularse con argumentos de texto inmediatamente [como mid , búsqueda, sustituto, etc.]? En este momento, el único método que puedo ver sería usar el ejemplo 3 anterior, y luego ir más allá y decir, por ejemplo, Concatenate (C1, C2, C3 ... C10).