Excel, como usar funções de texto na matriz de pesquisa sem colocar outra coluna?

Question

Se eu estou usando um vlookup, ou uma função de correspondência, mas eu preciso fazer um pouco de mumbo jumbo no meu material de pesquisa, existe uma maneira de fazer isso no array de pesquisa sem simplesmente adicionar outra coluna?

Digamos que eu tenha um valor de pesquisa de "FOOBAR 123" e quero retornar o registro chamado "FOO BAR123"

Idealmente, quero fazer algo como

=MATCH(SUBSTITUTE(A1," ",""),SUBSTITUTE('Sheet1'!$A:A," ",""),0)

Mas o SUBSTITUTE na matriz de pesquisa o quebra.

É possível fazer

=MATCH(SUBSTITUTE(A1," ",""),'Sheet1'!$B:B,0)

E adicione uma coluna =SUBSTITUTE(A1," ","") na Planilha1, mas existe uma maneira de evitar fazer isso?

    

Answer 1

A primeira fórmula é válida, desde que você a confirme como uma fórmula de matriz (isto é, com CTRL + SHIFT + ENTER), embora nesse caso seja altamente recomendável não referenciar a totalidade da coluna A, caso contrário isso resultará em uma fórmula extremamente rica em recursos.

A divisão do processo em duas colunas também torna o cálculo menos intensivo.

={MATCH(SUBSTITUTE(A1," ",""),SUBSTITUTE($A:$A8000," ",""),0)}

seria mais intensivo do que a solução de duas colunas a seguir

=SUBSTITUTE(A1," ","")                                   //in column B
=MATCH(SUBSTITUTE(A1," ",""),$B1:$B8000,0)

Porque no primeiro caso a substituição na coluna A deve ser calculada uma vez para cada linha, mas no segundo caso a substituição para a coluna A deve ser calculada apenas uma vez.

Respondido por @XOR LX. Postando isso para marcar a questão resolvida, mas o XOR LX merece o crédito.

    

Answer 2

Talvez eu interprete mal sua pergunta, caso contrário, isso deve funcionar para você.

= SUBSTITUTO (MATCH (A1, 'Folha1'! $ A: A, 0), "", "")