建立動態公式,以便一個 Excel 表在另一個表中找到相同的列名稱

建立動態公式,以便一個 Excel 表在另一個表中找到相同的列名稱

我有兩個Excel 表,表A 和表B。建立一個公式,最好使用結構化參考因此表 B 中的列順序無關緊要,即在表 B 中查找與我在表 A 中所在行的客戶 ID 以及我所在列的列值相符的值。

例如,如果我的公式位於表 A 的第三列中且位於客戶 ID“123”的行中,我希望它檢查其自己的列名稱(客戶名稱)並查找客戶名稱的值,其中客戶 ID =表B中的「123」。

以下公式適用於「客戶名稱」欄位:

=INDEX(TableB[Customer Name], MATCH([@[Customer Number]], TableB[Customer Number], 0))

但我希望能夠建立一個公式,動態地將 [客戶名稱] 部分替換為我所在列的名稱,以便我可以將其複製到所有列中。我嘗試使用 #Headers 和間接建立引用,但收到引用錯誤:

=INDIRECT("INDEX(TableB["&[#Headers]&"], MATCH([@[Customer Number]], TableB[Customer Number], 0))")

答案1

INDEX MATCH是正確的方法,您只需要小心構建它的方式。

左邊的表A。右表B。我們將使用[客戶編號]來尋找[郵遞區號]。

這是要寫入的公式D2

=INDEX(TableB,MATCH([@[Customer Number]],TableB[Customer Number],0),MATCH(D$1,TableB[#Headers],0))

在此輸入影像描述

INDEX如您所知,傳回行和列交叉處的儲存格的值。MATCH傳回數組中值的相對位置。

因此,對於 的兩個輸入,首先我們通過輸入要查找的垂直數組來INDEX找到源表中與我們正在使用的查找值(客戶編號)(這是傳統的前半部分INDEX MATCH)相匹配的行號,然後我們通過輸入包含來源列標題行的水平數組來MATCH找到與我們所在列的名稱相符的列號。MATCH

您會注意到,如果將欄位新增至來源表,並更改公式表中的標題,您將獲得新結果,而無需更改公式。

在此輸入影像描述

..這個公式可以橫向複製也可以向下複製。

在此輸入影像描述

這裡的兩個關鍵是:

  • 知道 MATCH 會橫向計數和向下計數。
  • 手動將 Excel 為您提供的結構化引用更改為實際的 R1C1 樣式單元格引用,這樣您就可以使該單元格引用的列索引動態而不是固定(即 C$1 而不是TableA[[#Headers],[Post Code]],後者硬編碼了您所在的字段,因此不會複製,儘管如果您手動更改其中包含查找公式的列的名稱,並且您只需要在目標表中擁有一個查找字段,那麼它就會起作用) 。

注意我知道這個問題已經存在了三年多了,但這是一個很好的問題,並且很好地展示了該INDEX MATCH技術的多功能性。

相關內容