Excel，如何在尋找數組上使用文字函數而不放入另一列？

Question

如果我使用 vlookup 或匹配函數，但需要在查找材料上執行一些繁瑣的操作，是否有一種方法可以在查找數組中執行此操作，而無需簡單地添加另一列？

假設我有一個查找值“FOOBAR 123”，我想傳回名為“FOO BAR123”的記錄

理想情況下，我想做類似的事情

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

但是SUBSTITUTE查找數組上的 打破了它。

這是可以做的

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

並在 Sheet1 中新增一=SUBSTITUTE(A1," ","")列，但有沒有辦法避免這樣做呢？

Answer 1

也許我誤解了你的問題，否則它應該對你有用。

=SUBSTITUTE(MATCH(A1,'Sheet1'!$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 值得讚揚。