這個案例來自網友提問：如何在一列數據中查找大于自己的最小值，或者小于自己的最大值？

這種需求其實解法很多，不要想復雜了，公式越短越好。

案例：

下圖 1 是各銷售人員當月獲客數列表，請根據 D 列指定的最低標準，查找出 B 列中低于最低標準的最大值及其行號。

效果如下圖 2 所示。

解決方案：

比如我們將最小獲客數設置為 6，來看一下兩組不同的公式用法。

1. 在 E2 單元格中輸入以下公式 --> O365 直接回車，低版本按 Ctrl+Shift+Enter 三鍵回車：

=MAX(IF(B2:B13-D2<0,B2:B13))

公式釋義：

• IF(B2:B13-D2<0,B2:B13)：查找 B2:B13 區(qū)域中小于 D2 的所有值

• MAX(...)：提取上述查找結果中的最大值
  

2. 在 F2 單元格中輸入以下公式 --> O365 直接回車，低版本按 Ctrl+Shift+Enter 回車：

=MATCH(MAX(IF(B2:B13-D2<0,B2:B13)),B:B,0)

公式釋義：

• 用 match 函數匹配出上一個公式查找的結果在 B 列中的行號
  

如果用的是 O365 版本，其實還可以用一個函數將公式簡化一下。

3. 在 E3 單元格中輸入以下公式 --> 回車：

=MAXIFS(B:B,B:B,'<'&D2)

開始公式釋義前，我們先學習一下 maxifs 函數。

MAXIFS 函數詳述

作用：

• 返回一組給定條件或標準指定的單元格中的最大值。

語法：

• MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

參數：

• max_range：必需， 確定最大值的實際單元格區(qū)域。

• criteria_range1：必需，一組用于條件計算的單元格。

• criteria1：必需，用于確定哪些單元格是最大值的條件，格式為數字、表達式或文本。一組相同的條件適用于 MINIFS、SUMIFS 和 AVERAGEIFS 函數。

• [criteria_range2, criteria2], ...：可選，附加區(qū)域及其關聯條件。最多可以輸入 126 個區(qū)域/條件對。
  

說明：

• max_range 和 criteria_rangeN 參數的大小和形狀必須相同，否則這些函數會返回 #VALUE! 錯誤。

公式釋義：

• MAXIFS(B:B,B:B,'<'&D2)：在 B 列中查找小于 D2 單元格的最大值

4. 在 F3 單元格中輸入以下公式 --> 回車：

=MATCH(MAXIFS(B:B,B:B,'<'&D2),B:B,0)

公式釋義：

• 用 match 函數查找出前一個公式結果在 B 列中的行號