☰

Excel INDEX MATCH 檢查多列

tag-icon • tag-icon microsoft-excel vlookup array
Excel INDEX MATCH 檢查多列

我本質上試圖解決的問題是一個 VLOOKUP，它檢查 A:E 列中的值，並返回 F 列中保存的值（如果在其中任何一個中找到）。

由於 VLOOKUP 無法勝任任務，我研究了 INDEX-MATCH 語法，但我正在努力思考如何為值數組（而不是單一列）完成此任務。我在下面建立了一個範例資料集來嘗試解釋這一點：

A------B------C------D------E------F

1------2------3------4------5------Apple

12-----13--------------------------Banana

14---------------------------------Carrot

如果被檢查的儲存格包含 1、2、3、4 或 5，則公式的結果應為 Apple。如果它是 12 或 13，它應該返回 Banana，最後如果它包含 14，它應該返回 Carrot。

後半部來自這樣一個事實：被引用的單元格不是單一值，而是一個完整的表格本身。這樣，這個搜尋就會根據不同的值完成很多次。

因此，為了演示，其他地方還有另一個表（如下所示）包含這些值。該表如下所示

你好 - - - - - -

1------(蘋果)----

2-----(蘋果)----

12-----（香蕉）-

ETC。

括號中的值是公式計算這些值的位置。

答案1

您有許多不同的案例。讓我們考慮一個案例：

列中的某處A透過乙有一個且唯一包含 13 的儲存格，傳回該列中該儲存格的內容F在同一行。

我們將使用“助手”列。在G1進入：

=COUNTIF(A1:E1,13)

並抄下來。這使我們能夠識別該行：

在此輸入影像描述
現在我們可以使用匹配()/索引():

選擇一個儲存格並輸入：

=INDEX(F:F,MATCH(1,G:G,0))

在此輸入影像描述

如果「規則」發生變化且一行中可能有多個 13 或多行包含 13，我們將修改輔助列。

編輯#1：

根據您的更新，第一步是提取硬編碼13從「helper」列中的公式中取出並將其放入自己的儲存格中，（說H1）。然後，您只需更改單個單元即可運行不同的案例。

如果表中有大量案例，您可以建立一個巨集來設定每個案例（更新H1）並記錄結果。

答案2

根據我自己的研究以及與 @Gary'sStudent 的討論，我使用的解決方案是為每個可能包含該值的列創建一個 MATCH 公式，以及一個空白捕獲“IFERROR”語句。

I1 =IFERROR(MATCH($H1,A$1:A$3,0),"")     
J1 =IFERROR(MATCH($H1,B$1:B$3,0),"")     
K1 =IFERROR(MATCH($H1,C$1:C$3,0),"")    
L1 =IFERROR(MATCH($H1,D$1:D$3,0),"")    
M1 =IFERROR(MATCH($H1,E$1:E$3,0),"")
etc.

現在可以隱藏這些列以防止用戶混淆/互動。

然後我創建了一個索引，將這些累積到一個值中，該值應該與相關的 ROW 相符。同樣，如果在表中找不到該值，則會進行檢查（第一個 SUM），將其輸入為空白值。

N1 =IF(SUM(I1:M1)=0,"",INDEX($A$1:$F$3,SUM(I1:M1),6))

索引匹配數組 最後，我輸入了一些條件格式公式，以確保使用者識別並取代/刪除任何重複的資料。

A1:E3 Cell contains a blank value                [Formatting None Set, Stop if True]
A1:E3 =COUNTIF($A$1:$E$3,A1)>1                   [Formatting Text:White, Background:Red]

H1:N1 =COUNTIF($A$1:$E$3,H1)>1       [Formatting Text:Red, Background:Red]

這只是提示使用者刪除這些重複資料。

在此輸入影像描述

答案3

對於 H1 中的單一公式：

=INDEX($F$1:INDEX(F:F,MATCH("ZZZ",F:F)),AGGREGATE(15,6,ROW($A$1:INDEX(E:E,MATCH("ZZZ",F:F)))/($A$1:INDEX(E:E,MATCH("ZZZ",F:F))=H1),1))

這是一個陣列公式，因此我們需要將引用限制在資料集的大小。都INDEX(E:E,MATCH("ZZZ",F:F))這樣做。這將傳回 F 列中包含文字的最後一行。然後它將其設定為要迭代的最後一行。

@Gary'sStudent 方法避免了陣列公式，可能是所需的方法。隨著資料集和公式數量的增加，計算時間也會增加。甚至在某個時候，Excel 崩潰了。通常這需要幾千美元，但我想發出警告。

在此輸入影像描述

編輯

為了避免使用陣列公式並且仍然是一個公式：

=IFERROR(INDEX(F:F,MIN(IFERROR(MATCH($H1,A:A,0),1050000),IFERROR(MATCH($H1,B:B,‌​0),1050000),IFERROR(MATCH($H1,C:C,0),1050000),IFERROR(MATCH($H1,D:D,0),1050000),I‌​FERROR(MATCH($H1,E:E,0),1050000))),"")

這是基於OP的答案，只是將方法合併為一個公式。

此公式將忽略重複的條目並傳回找到該數字的第一行。

而且因為它是非數組，所以全列引用不會影響計算時間。

![在此輸入影像描述

答案4

另一種方法是基於輔助表，該表表示「應該」最初是如何建構的。這將避免令人厭煩的調試和事後更改的龐大方程，並且它能夠乾淨地求解不同數量的列，這與擁有 5 個查找列的想法不同。

如果以上在Sheet1中，則新增Sheet2。那個地方有四根柱子；列、列、ID、名稱

公式Row應該是（在偽代碼中，“Last”表示“對於sheet2中上面的行”）

=IF(Column = 1, Last row + 1 , Last row)

公式為Column：

=IF(OR(Last Column = 5; INDEX(StartTable, last row, last column + 1) = ""), 1, Last column+1)

ID和中的公式Name：

=INDEX(StartTable, Row, Column)    
=INDEX(NameColumn, Row, 1)

然後將其填充（基本上直到row>原始表中的行數）。

最後，您可以將新表與普通的 vlookup 或索引/匹配一起使用。

PRO：公式更簡單，更易於使用和理解。

缺點：需要額外的表，必須保持表的長度。效能方面存在風險，因為這幾乎需要單一執行緒來處理整個「字串」值。

另外，如果有幾個錯誤行沒問題，則程式碼可能會更簡單，而且效能可能會更高，然後我們可以假設列數始終為 5，同時給出 row 和 column 。

相關內容