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

Question

Se estou usando um vlookup ou uma função de correspondência, mas preciso fazer alguma besteira no meu material de pesquisa, existe uma maneira de fazer isso na matriz de pesquisa sem simplesmente adicionar outra coluna?

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

Idealmente, eu quero fazer algo como

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

Mas o SUBSTITUTEarray de pesquisa o quebra.

É possível fazer

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

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

Answer 1

Talvez eu tenha entendido mal sua pergunta, caso contrário, deveria funcionar para você.

=SUBSTITUIR(MATCH(A1,'Planilha1'!$A:A,0)," ","")

Answer 2

A primeira fórmula é adequada, desde que você a confirme como uma fórmula de matriz (ou seja, com CTRL+SHIFT+ENTER), embora nesse caso seja altamente recomendável não fazer referência à totalidade da coluna A, caso contrário, isso resultará em um recurso extremamente -fórmula pesada.

Dividir o 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 seguinte solução de duas colunas

=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 na coluna A deve ser calculada apenas uma vez.

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