Excel で、別の列を入れずにルックアップ配列でテキスト関数を使用するにはどうすればよいでしょうか?

Question

vlookup または match 関数を使用しているが、検索マテリアルで複雑な処理を実行する必要がある場合、別の列を追加せずに、検索配列でこれを行う方法はありますか?

「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

おそらく私はあなたの質問を誤解しているのでしょうが、そうでなければうまくいくはずです。

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

Answer 2

最初の数式は、配列数式としてコミットすれば (つまり、CTRL + SHIFT + ENTER を使用) 問題ありませんが、その場合、列 A 全体を参照しないことを強くお勧めします。そうしないと、リソースを非常に消費する数式になります。

プロセスを 2 つの列に分割すると、計算の負荷も軽減されます。

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

次の2列のソリューションよりも集中的になります

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

最初のケースでは、列 A の置換は各行に対して 1 回計算する必要がありますが、2 番目のケースでは、列 A の置換は 1 回だけ計算する必要があります。

@XOR LX が回答しました。質問が解決済みとしてマークするためにこれを投稿しましたが、功績は XOR LX に帰属します。