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

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

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

問題

粉絲朋友的問題：

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

解決方法

初解：

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

使用的公式如下：

再解：

但韓老師寫的同一個數(shù)值對應(yīng)兩個區(qū)間交叉點的值，不滿足粉絲朋友的需求，所以有再問：

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

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

使用的公式如下：

=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))

其實，兩個數(shù)，每個數(shù)各自對應(yīng)一個區(qū)間反而容易解決。

三解：

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

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

韓老師更新公式為：

如果數(shù)據(jù)超出區(qū)間范圍，效果如下：

如果數(shù)據(jù)在區(qū)間范圍內(nèi)，效果如下：

公式解析

其實完成這一系列問題的關(guān)鍵點是：

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

LOOKUP 函數(shù)用法請參考：

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所在區(qū)間的行。

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所在區(qū)間行與$C$16所有區(qū)間列交叉處的值。

INDEX+MATCH 函數(shù)用法請參考：