Excel | 同時滿足兩個區間的返回值問題

老師自己的書，獲評國家十三五規劃教材：

　　該書共分為4個部分，從數據采集與整理，到數據統計分析，再到數據可視化，并輔以常用函數與公式的使用詳解，基本涵蓋了Excel使用中的數據輸入與規范、數據查找統計、數據條件輸出與分析等重要操作實用技能。
　　該書可作為行業白領數據處理與分析的參考用書?............","author":["韓春玲"],"publisher":"電子工業出版社"},"appuin":"3208869061","isNewCpsKOL":0}">

問題

粉絲朋友的問題：

"想在excel中實現雙區間查找，然后返回所對應的值，如區間A在300-400， 區間B90-100， 那么返回81，請問如何可以實現?" 如下圖：

解決方法

初解：

因粉絲朋友沒有給我返回值區域，所以韓老師自己寫了一個數據，返回該數據對應的區間A與區間B的交叉值，如下：

使用的公式如下：

再解：

但韓老師寫的同一個數值對應兩個區間交叉點的值，不滿足粉絲朋友的需求，所以有再問：

“但是我的表格是兩個區間A和B， 有兩個不同的參數，如分別對應不同的區間300-400， 區間90-100， 首先定義在不同的區間，然后再返回交叉點的值。”

韓老師提供的解決方法，如下圖：

使用的公式如下：

=INDEX($D$4:$I$11,MATCH(LOOKUP($B$16,$B$4:$B$11),$B$4:$B$11,0),MATCH(LOOKUP($C$16,$D$2:$I$2),$D$2:$I$2,0))

其實，兩個數，每個數各自對應一個區間反而容易解決。

三解：

此時，朋友又遇到一個問題：

”如果當數值分別為95 和 720， 超出了表中的范圍，這時候仍會顯示結果，沒有提示有誤，這種情況有解嗎？“

韓老師更新公式為：

如果數據超出區間范圍，效果如下：

如果數據在區間范圍內，效果如下：

公式解析

其實完成這一系列問題的關鍵點是：

利用了LOOKUP函數返回區間下限值的性質。比如，如果查找數據85，在區間80-90之間查找，沒有85這個確切的值，那么LOOKUP函數返回區間下限80。

LOOKUP 函數用法請參考：

LOOKUP($B$16,$B$4:$B$11)：在$B$4:$B$11中查找$B$16，返回最接近$B$16的比$B$16小的值。

MATCH(LOOKUP($C$17,$B$4:$B$12),$B$4:$B$12,0)：返回$B$16所在區間的行。

INDEX($D$4:$I$11,MATCH(LOOKUP($B$16,$B$4:$B$11),$B$4:$B$11,0),MATCH(LOOKUP($C$16,$D$2:$I$2),$D$2:$I$2,0)))：返回$B$16所在區間行與$C$16所有區間列交叉處的值。

INDEX+MATCH 函數用法請參考：