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

Question

Если я использую vlookup или функцию сопоставления, но мне нужно выполнить несколько пометок в моем поисковом материале, есть ли способ сделать это в массиве поиска без простого добавления другого столбца?

Допустим, у меня есть значение поиска "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

Может быть, я неправильно понял ваш вопрос, иначе он должен работать для вас.

= ЗАМЕНИТЬ (MATCH (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 заслуживает похвалы.