Excel, ¿cómo usar funciones de texto en una matriz de búsqueda sin colocar otra columna?

Question

Si estoy usando una búsqueda virtual o una función de coincidencia pero necesito hacer algunas tonterías en mi material de búsqueda, ¿hay alguna manera de hacerlo en la matriz de búsqueda sin simplemente agregar otra columna?

Digamos que tengo un valor de búsqueda de "FOOBAR 123" y quiero devolver el registro llamado "FOO BAR123".

Idealmente, quiero hacer algo como

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

Pero SUBSTITUTEen la matriz de búsqueda lo rompe.

es posible hacer

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

Y agregue una =SUBSTITUTE(A1," ","")columna en la Hoja1, pero ¿hay alguna manera de evitar tener que hacer esto?

Answer 1

Tal vez no entendí bien tu pregunta; de lo contrario, debería funcionar para ti.

=SUSTITUIR(COINCIDIR(A1,'Hoja1'!$A:A,0)," ","")

Answer 2

La primera fórmula está bien, siempre que la confirme como una fórmula matricial (es decir, con CTRL+MAYÚS+ENTRAR), aunque en ese caso se le recomienda encarecidamente no hacer referencia a la totalidad de la columna A; de lo contrario, esto resultará en un recurso extremadamente -fórmula pesada.

Dividir el proceso en dos columnas también hace que el cálculo sea menos intensivo.

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

sería más intensivo que la siguiente solución de dos columnas

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

Porque en el primer caso la sustitución en la columna A debe calcularse una vez para cada fila, pero en el segundo caso la sustitución en la columna A solo debe calcularse una vez.

Respondido por @XOR LX. Publicar esto para marcar la pregunta resuelta, pero XOR LX merece el crédito.