Lookup函式功能很強大,它可以區間查詢、最後一個符合條件查詢、多條件查詢等。
【例】
在G4中設定公式,根據G2的入庫時間和G3的產品名稱,從左表中查詢對應的入庫單價。
G2公式:
=LOOKUP(1,
0/
((B3:B8=G2)*(C3:C8=G3)),D3:D8)
有很多同學提問,為什麼要用
0/
的結構?其實該結構廣泛應用於lookup查詢公式中,為了幫助同學們理解以便能靈活應用。蘭色今天就此進行詳細的剖析:
首先,我們先看看(B3:B8=G2)*(C3:C8=G3)運算後是什麼個結果?
在excel公式中如果:
A和B的值相等,=A=B 會返回結果True,True在四則運算中相於數字1
A和B的值不相等,=A=B 會返回結果False,False在四則運算中相於數字0
所以(B3:B8=G2)的結果是由false和true構成的一組值,如果放在單元格中,結果如F7:F12區域值所示:
同樣(C3:C8=G3)的結果也是由True和Fasle組成的一組數值,而2個相同大小的一組值相乘,True*True=1,True*False=,False*False=,相乘的最終結果是由1和組成的一組數。如下圖 H7:H12 所示。
由上圖可以看出,相乘結果中值為1的行(H9所示),正是符合兩個條件的行。那麼怎麼把這個1的位置提取出來呢?
Lookup函式的查詢原理是
二分法
。按二分法原理,lookup函式會在在二分位處查詢,要想準確查詢到,這組值需要按升序排列,而只是公式
(B3:B8=G2)*(C3:C8=G3)
的結果是不符合要求的。
(回覆“二分法”檢視相關詳細教程)
於是高手們想出了用
0除
的方法,把結果由1和變成了由1和錯誤值構成的一組值。唯一符合條件的值為,其他的均為錯誤值#DIV/0!
Lookup函式還有一個關鍵的特徵,查詢時可以忽略錯誤值,這樣一組數值忽略後只剩下一個值,這時只需要使用任一個大於等於0的值查詢即可。即:
=LOOKUP(
1
,
0/
((B3:B8=G2)*(C3:C8=G3)),D3:D8)
蘭色說:0/的目的就是把符合條件的變成,其他的變成錯誤值,利用lookup查詢
忽略錯誤值
的特徵查詢到符合條件的值。