內容
我們之前已經給初學者講解過如何使用VLOOKUP的基本功能（英文VLOOKUP，縮寫代表“垂直查找功能”）。 並且向有經驗的用戶展示了幾個更複雜的公式。
在本文中，我們將嘗試提供有關使用垂直搜索的另一種方法的信息。
您可能想知道：“為什麼需要這樣做？”。 為了顯示所有可能的搜索方法，這是必要的。 此外，許多 VLOOKUP 限制通常會阻止獲得所需的結果。 在這方面，INDEX( ) MATCH( ) 功能更加豐富多樣，而且它們的限制也更少。
索引匹配
由於本指南的目的是展示此功能有多好，我們 讓我們看一下有關其操作原理的基本信息。 我們將展示示例，並考慮為什麼它比 VLOOKUP() 更好。
INDEX 函數語法和用法
此功能有助於根據列或行號在指定的搜索區域中找到所需的值。 語法：
=INDEX（數組，行號，列號）：
- 數組——搜索將發生的區域；
- 行號——要在指定數組中搜索的行號。 如果行號未知，則必須指定列號；
- 列號——要在指定數組中找到的列號。 如果值未知，則需要行號。
一個簡單公式的例子：
=索引（A1：S10,2,3）
該函數將在 A1 到 C10 的範圍內進行搜索。 數字顯示要顯示所需值的行 (2) 和列 (3)。 結果將是單元格 C2。
很簡單，對吧？ 但是，當您使用真實文檔時，您不太可能擁有有關列號或單元格的信息。 這就是 MATCH() 函數的用途。
MATCH 函數語法和用法
MATCH() 函數搜索所需的值並在指定的搜索區域顯示其近似值。
searchpos() 語法如下所示：
=MATCH（要查找的值，要查找的數組，匹配類型）
- 搜索值——要找到的數字或文本；
- 搜索數組——搜索將發生的區域；
- 匹配類型——指定是查找確切的值還是最接近它的值：
- 1（或未指定值）——返回等於或小於指定值的最大值；
- 0 – 顯示與搜索值完全匹配。 在組合 INDEX() MATCH() 中，您幾乎總是需要完全匹配，所以我們寫 0；
- -1 – 顯示大於或等於公式中指定值的最小值。 排序按降序進行。
例如，在 B1:B3 範圍內註冊了紐約、巴黎、倫敦。 下面的公式將顯示數字 3，因為倫敦在列表中排名第三：
=EXPOSE(倫敦,B1:B3,0)
如何使用 INDEX MATCH 函數
您可能已經開始理解構建這些功能的聯合工作的原理。 簡而言之，那麼 INDEX() 在指定的行和列中搜索所需的值。 MATCH() 顯示這些值的數量：
=INDEX（返回值的列，MATCH（要搜索的值，要搜索的列，0））
仍然很難理解它是如何工作的？ 也許一個例子會更好地解釋。 假設您有一份世界首都及其人口的列表：
為了找出某個首都的人口規模，例如日本的首都，我們使用以下公式：
=INDEX(C2:C10, MATCH(日本, A2:A10,0))
說明：
- MATCH() 函數在數組 A2:A10 中查找值“Japan”並返回數字 3，因為 Japan 是列表中的第三個值。
- 這個數字去行號”在 INDEX() 公式中，並告訴函數從該行打印一個值。
所以上面的公式就變成了標準公式 索引（C2：C10,3）。 該公式從 C2 到 C10 單元格搜索，並從該範圍內的第三個單元格（即 C4）返回數據，因為倒計時是從第二行開始的。
不想在公式中指定城市名稱？ 然後將其寫入任何單元格，例如 F1，並將其用作 MATCH() 公式中的參考。 最後你會得到一個動態搜索公式：
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
重要！ 中的行數 排列 INDEX() 必須與中的行數相同 考慮數組 在 MATCH() 中，否則會得到錯誤的結果。
等一下，為什麼不直接使用 VLOOKUP() 公式？
=VLOOKUP(F1, A2:C10, 3, 假)
浪費時間試圖弄清楚 INDEX MATCH 的所有這些複雜性有什麼意義？
在這種情況下，使用哪個函數並不重要。 這只是了解 INDEX() 和 MATCH() 函數如何協同工作的示例。 其他示例將展示這些功能在 VLOOKUP 無能為力的情況下的功能。
索引匹配或 VLOOKUP
在決定使用哪種搜索公式時，許多人同意 INDEX() 和 MATCH() 比 VLOOKUP 優越得多。 但是，很多人仍然使用 VLOOKUP()。 首先，VLOOKUP() 更簡單，其次，用戶並不完全理解使用 INDEX() 和 MATCH() 的所有優點。 如果沒有這些知識，沒有人會同意花時間研究一個複雜的系統。
以下是 INDEX() 和 MATCH() 相對於 VLOOKUP() 的主要優勢：
- 從右到左搜索。 VLOOKUP() 不能從右向左搜索，所以要查找的值必須始終在表的最左側列中。 但是 INDEX() 和 MATCH() 可以毫無問題地處理這個問題。 本文將告訴你它在實踐中的樣子：如何在左側找到所需的值。
- 安全添加或刪除列。 VLOOKUP() 公式在刪除或添加列時顯示不正確的結果，因為 VLOOKUP() 需要準確的列號才能成功。 自然，當添加或刪除列時，它們的數量也會改變。
並且在 INDEX() 和 MATCH() 公式中，指定了列的範圍，而不是單個列。 因此，您可以安全地添加和刪除列，而無需每次都更新公式。
- 搜索量沒有限制. 使用 VLOOKUP() 時，搜索條件的總數不得超過 255 個字符，否則您將獲得 #VALUE！ 因此，如果您的數據包含大量字符，則 INDEX() 和 MATCH() 是最佳選擇。
- 處理速度高。 如果您的表相對較小，那麼您不太可能注意到任何差異。 但是，如果表包含成百上千行，並且相應地有成百上千個公式，則 INDEX() 和 MATCH() 將比 VLOOKUP() 處理得快得多。 事實上，Excel 將只處理公式中指定的列，而不是處理整個表格。
如果您的工作表包含大量公式，如 VLOOKUP() 和 SUM()，則 VLOOKUP() 的性能影響將特別明顯。 解析數組中的每個值需要單獨檢查 VLOOKUP() 函數。 因此 Excel 必須處理大量信息，這大大減慢了工作速度。
公式範例
我們已經弄清楚了這些功能的用處，所以我們可以進入最有趣的部分：知識在實踐中的應用。
從右到左搜索的公式
如前所述，VLOOKUP 無法執行這種形式的搜索。 所以，如果想要的值不在最左邊一列，VLOOKUP() 就不會產生結果。 INDEX() 和 MATCH() 函數更通用，值的位置對它們的工作沒有太大的作用。
例如，我們將在表格的左側添加一個排名列，並嘗試找出我國首都在人口方面的排名。
在單元格 G1 中，我們寫入要查找的值，然後使用以下公式在 C1:C10 範圍內搜索，並返回 A2:A10 中的對應值：
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
迅速的。 如果您打算將此公式用於多個單元格，請確保使用絕對尋址來修復範圍（例如， $A$2：$A$10 和 $C$2：4C$10).
指數 更多曝光 更多曝光 在列和行中搜索
在上面的示例中，我們使用這些函數作為 VLOOKUP() 的替代，以從預定義的行範圍內返回值。 但是，如果您需要進行矩陣或雙邊搜索怎麼辦？
聽起來很複雜，但是這種計算的公式與標準的 INDEX() MATCH() 公式相似，只有一個區別： MATCH() 公式必須使用兩次。 第一次獲取行號，第二次獲取列號：
=INDEX(array, MATCH(垂直搜索值, 搜索列, 0), MATCH(水平搜索值, 搜索行, 0))
我們看下表，試著做一個公式 索引（） 快遞（） 快遞（） 以顯示特定國家/地區在選定年份的人口統計數據。
目標國家位於單元格 G1（垂直查找）中，目標年份位於單元格 G2（水平查找）中。 公式將如下所示：
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
這個公式如何運作
與任何其他復雜的公式一樣，通過將它們分解為單獨的方程，它們更容易理解。 然後您可以了解每個單獨的功能的作用：
- 比賽（G1，A2：A11,0，XNUMX） – 在 A1:A2 範圍內查找值 (G11) 並顯示該值的編號，在我們的示例中為 2；
- 搜索（G2，B1：D1,0，XNUMX） – 在 B2:D1 範圍內查找值 (G1)。 在這種情況下，結果為 3。
將找到的行號和列號發送到 INDEX() 公式中的相應值：
=索引（B2：D11,2,3）
結果，我們在 B2:D3 範圍內的 2 行和 11 列的交叉點處的單元格中得到了一個值。 該公式顯示了所需的值，該值位於單元格 D3 中。
使用 INDEX 和 MATCH 按多個條件搜索
如果您已閱讀我們的 VLOOKUP() 指南，您可能已經嘗試過多種搜索公式。 但是這種搜索方法有一個明顯的限制——需要添加一個輔助列。
但好消息是 使用 INDEX() 和 MATCH() 您可以搜索多個條件，而無需編輯或更改工作表。
以下是 INDEX() MATCH() 的通用多條件搜索公式：
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
那個筆記： 此公式必須與鍵盤快捷鍵一起使用 CTRL+SHIFT+ENTER。
假設您需要根據 2 個條件找到您要查找的值： 買方 и 產品。
這需要以下公式：
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
在這個公式中，C2:C10 是搜索發生的範圍， F1 – 這種情況， A2：A10 — 是比較條件的範圍， F2 – 條件 2， V2：V10 – 比較條件 2 的範圍。
不要忘記在工作結束時使用公式按下組合 CTRL+SHIFT+ENTER – Excel 會自動用大括號關閉公式，如示例所示：
如果您不想在工作中使用數組公式，則在公式中添加另一個 INDEX() 並按 ENTER，它將如下例所示：
這些公式是如何工作的
此公式的工作方式與標準 INDEX() MATCH() 公式相同。 要搜索多個條件，您只需創建多個 False 和 True 條件來表示正確和不正確的單個條件。 然後這些條件適用於數組的所有相應元素。 該公式將 False 和 True 參數分別轉換為 0 和 1，並輸出一個數組，其中 1 是在字符串中找到的匹配值。 MATCH() 將找到第一個匹配 1 的值並將其傳遞給 INDEX() 公式。 反過來，它將從所需列的指定行中返回已經所需的值。
沒有數組的公式取決於 INDEX() 自己處理它們的能力。 公式中的第二個 INDEX() 匹配 falsy (0)，因此它將具有這些值的整個數組傳遞給 MATCH() 公式。
這是對該公式背後邏輯的相當冗長的解釋。 欲了解更多信息，請閱讀文章“多個條件的索引匹配“。
INDEX 和 MATCH 中的 AVERAGE、MAX 和 MIN
Excel 有自己的特殊功能，用於查找平均值、最大值和最小值。 但是，如果您想從與這些值關聯的單元格中獲取數據怎麼辦？ 在這種情況下 AVERAGE、MAX 和 MIN 必須與 INDEX 和 MATCH 一起使用。
索引匹配和最大值
要在 D 列中找到最大值並將其顯示在 C 列中，請使用以下公式：
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
索引匹配和最小值
要在 D 列中找到最小值並將其顯示在 C 列中，請使用以下公式：
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
搜索索引和 SERPENT
要在 D 列中找到平均值並在 C 中顯示該值：
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
根據數據的寫入方式，MATCH() 的第三個參數是 1、0 或 -1：
- 如果列是按升序排序的，設置1（那麼公式會計算最大值，小於等於平均值）；
- 如果排序是降序，則-1（公式將輸出大於或等於平均值的最小值）；
- 如果查找數組包含一個與平均值完全相等的值，則將其設置為 0。
在我們的示例中，總體按降序排序，因此我們輸入 -1。 結果是東京，因為人口值 (13,189) 最接近平均值 (000)。
VLOOKUP() 也可以執行此類計算，但只能作為數組公式：VLOOKUP with AVERAGE, MIN 和 MAX.
索引匹配和 ESND/IFERROR
您可能已經註意到，如果公式找不到所需的值，則會引發錯誤 ＃N / A. 您可以用更多信息替換標準錯誤消息。 例如，在公式中設置參數 在第 XNUMX 個:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
使用此公式，如果您輸入的數據不在表格中，表格將為您提供指定的消息。
如果你想捕獲所有錯誤，那麼除了 在第 XNUMX 個 可以使用 錯誤:
=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “出了點問題！”)
但請記住，以這種方式掩蓋錯誤並不是一個好主意，因為標準錯誤會報告公式中的違規行為。
我們希望您發現我們的使用 INDEX MATCH() 函數的指南對您有所幫助。