使用多條件查找最接近的匹配值

在某些情況下，您可能需要根據多個條件查找最接近或近似的匹配值。通過結合 INDEX、MATCH 和 IF 函數，您可以快速在 Excel 中完成此操作。

如何使用多個條件查找最接近的匹配值？

如下方截圖所示，您需要根據兩個條件“專業是 計算機”和“工作經驗是 15 年”找到適合這份工作的人選。

注意：為了確保正確運行，如果包含重複的專業，這些重複專業的工作經驗應該按升序排序。

1. 選擇一個空白單元格輸出結果，然後將以下公式複製到其中並按下 Ctrl + Shift + Enter 鍵以獲取結果。

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

注意：在此公式中：

• D3:D8 是包含您要查找結果的列範圍；
• G5 包含第二個條件（經驗數字 15），您將根據該條件查找值；
• G4 包含第一個條件（計算機），您將根據該條件查找值；
• B3:B8 是與第一個條件匹配的單元格範圍；
• C3:C8 是與第二個條件匹配的單元格範圍；
• 數字 1 表示近似匹配，這意味著如果找不到確切值，它將找到小於查找值的最大值；
• 此公式必須作為陣列公式使用 Ctrl + Shift + Enter 鍵輸入。

此公式的運作方式

這個公式可以分解為幾個部分：

• IF(B3:B8=G4,C3:C8)：這裡的 IF 函數返回結果為 {9;13;FALSE;FALSE;FALSE;FALSE}，這是通過測試 B3:B8 中的值來查看它們是否與 G4 中的值匹配得出的。如果有匹配項，則返回相應的值，否則返回 FALSE。這裡找到了兩個匹配項和四個不匹配項。
• 陣列公式 =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1)：MATCH 函數在範圍 C3:C8 中查找數字 15（G5 中的值）的位置。由於找不到數字 15，因此匹配到下一個最小的值 13。所以這裡的結果是 2。
• 並且 =INDEX(D3:D8,2)：INDEX 函數返回範圍 D3:D8 中第二個單元格的值。所以最終結果是 Amy。

相關函數

相關文章