Quando os resultados de uma fórmula de matriz são números, geralmente acho fácil encontrar um método apropriado para recolher a matriz em um único resultado. No entanto, quando os resultados de uma fórmula de matriz são textos, acho difícil manipular a fórmula de uma maneira que forneça um único resultado desejado. Em resumo,existe um método de manipular uma matriz de resultados de texto que eu negligenciei? Consulte o final desta pergunta para a fórmula final desejada que não funciona e solicite soluções.

* Editar - depois de ler isso novamente, posso resumir alternadamente minha pergunta como: existe uma maneira de acessar vários elementos de texto a partir de um 'resultado de matriz de fórmula', sem selecionar individualmente (por exemplo, com INDEX)?

Exemplos em que as Fórmulas da matriz funcionam, em que a matriz do resultado é um valor numérico

(1) Exemplo 1: Suponha que as linhas A da coluna 1 a 500 sejam uma lista de IDs de produtos no formato xyz123, e as linhas 1 a 500 da coluna B mostrem o total de vendas desse produto. Se eu quiser encontrar as vendas do produto com as vendas mais altas, onde os últimos 3 dígitos de um ID estão acima de 400, eu poderia usar uma fórmula de matriz assim (confirmada com CTRL + SHIFT + ENTER em vez de apenas ENTER):

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

(2) Exemplo 2 Agora, suponha que a coluna B contenha nomes de produtos, em vez de Vendas. Agora, quero simplesmente retornar o primeiro nome que corresponda aos critérios dos três últimos dígitos do ID do produto> 400. Isso pode ser feito da seguinte maneira:

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

Aqui, fiz uma pequena manipulação, para que a parte Array real da fórmula [IF (RIGHT (A1: A500,3 ...] retorne um resultado de valor [as ROWs das células A1: A500 onde os últimos 3 dígitos são acima de 400]; portanto, posso usar MIN para mostrar apenas o primeiro número de linha correspondente e, em seguida, posso usar esse resultado recolhido em uma função INDEX regular.

(3) Exemplo 3 Para um exemplo final, consulte a discussão sobre uma pergunta semelhante aqui [Vai mais fundo que o meu exemplo resumido abaixo, de uma maneira que não é diretamente relevante para esta questão]:https://stackoverflow.com/a/31325935/5090027

Suponha agora que você deseja uma lista de todos os nomes de produtos, nos quais os três últimos dígitos do ID do produto> 400. Que eu saiba, isso realmente não pode ser feito em uma única célula, teria que ser feito colocando cada resultado individual em uma célula subsequente. A fórmula a seguir pode ser colocada, por exemplo, em C1 e arrastada para baixo 10 linhas e, em seguida, mostraria os 10 primeiros nomes de produtos com os IDs do produto com os ú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()))

Exemplo onde fórmulas de matriz não funcionará, onde a matriz de resultados é valores de texto

Agora suponha que eu queira pegar os resultados no Exemplo 3 e executar alguma manipulação de texto neles. Por exemplo, suponha que eu queira concatená-los todos em uma única sequência de texto. O abaixo não funciona, porque a concatenação não aceita uma matriz de resultados como argumentos aceitáveis.

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

Então a questão é: alguém sabe como fazer com que esta última fórmula funcione? Ou como obter uma fórmula que funcione, que obtenha uma matriz de resultados de texto e a converta em um 'intervalo utilizável' [para que possa ser conectada ao Concatenate acima] ou que possa ser manipulada com argumentos de texto imediatamente [como meados , pesquisa, substituto etc.]? No momento, o único método que posso ver seria usar o exemplo 3 acima e, em seguida, avançar e dizer, por exemplo, concatenar (C1, C2, C3 ... C10).