Excel, как использовать текстовые функции в массиве поиска, не добавляя еще один столбец?

Question

Если я использую функцию ВПР или функцию сопоставления, но мне нужно сделать что-то невообразимое с моим искомым материалом, есть ли способ сделать это в массиве поиска, не добавляя просто еще один столбец?

Допустим, у меня есть искомое значение «FOOBAR 123», и я хочу вернуть запись с именем «FOO BAR123».

В идеале я хочу сделать что-то вроде

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

Но SUBSTITUTEмассив поиска его ломает.

Это возможно сделать

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

И добавьте =SUBSTITUTE(A1," ","")столбец в Sheet1, но есть ли способ избежать этого?

Answer 1

Возможно, я неправильно понял ваш вопрос, в противном случае вам это должно подойти.

=ПОДСТАВИТЬ(ПОИСКПОЗ(A1;'Лист1'!$A:A;0)," ","")

Answer 2

Первая формула подойдет, если вы зафиксируете ее как формулу массива (т. е. с помощью CTRL+SHIFT+ENTER), хотя в этом случае вам настоятельно рекомендуется не ссылаться на весь столбец A, иначе это приведет к получению чрезвычайно ресурсоемкой формулы.

Разделение процесса на два столбца также делает вычисления менее трудоемкими.

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

будет более интенсивным, чем следующее решение с двумя колонками

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

Потому что в первом случае подстановка в столбце A должна быть рассчитана один раз для каждой строки, а во втором случае подстановка в столбце A должна быть рассчитана только один раз.

Ответил @XOR LX. Публикую это, чтобы отметить, что вопрос решен, но XOR LX заслуживает похвалы.