原文信息
標題：Lookup函數(shù)使用集錦作者： Excel技巧網(wǎng)
原文大意
文中主要介紹了7種應(yīng)用場景：
-
快速返回多列查詢內(nèi)容。
使用Column()來計算Vlookup返回結(jié)果所在的列，代替手動的輸入1、2、3類似的機械工作
-
Vlookup實現(xiàn)多條件查詢
使用if(1,0)和數(shù)組公式，構(gòu)建內(nèi)存兩列數(shù)據(jù)區(qū)域，然后使用Vlookup查找，返回匹配的多調(diào)校結(jié)果
-
查詢不連續(xù)字段
作用和column()類似，vlookup第3參數(shù)中，返回結(jié)果所在列，由Match()函數(shù)動態(tài)計算得出，代替手動查找對應(yīng)的列
-
Vlookup從右向左查詢
使用if({1,0})返回動態(tài)的兩列數(shù)據(jù)區(qū)域，和多條件查詢的原理一樣。
-
去除VLOOKUP查詢結(jié)果返回的錯誤值
使用isNA()函數(shù)，首先判斷vlookup返回結(jié)果是否為N/A，然后使用if()函數(shù)來做一個判斷，如果不是N/A，則返回VLOOKUP查詢的結(jié)果。
-
VLOOKUP搞定多條件判斷（分段查詢）
即使用模糊查詢，建立分段數(shù)據(jù)表，區(qū)域段內(nèi)的查詢，返回對應(yīng)的結(jié)果。
-
提取非空單元格
使用0/($B$2:B2<>"")構(gòu)建一個包含0和#DIV!0的數(shù)組，然后使用lookup函數(shù)，查找1返回最后一個零的位置對應(yīng)的數(shù)據(jù)，即能找到的最后一個不為空的單元格
拆為己用
根據(jù)原文中的三個公式，可以簡單的總結(jié)出以下幾種，編寫公式的常用技巧：
1-IF10法
=vlookup(A9&B9&C9,IF({1,0},A3:A5:B3:B5&C3:C5,D3:D5),2,)
IF01法是指：在數(shù)組公式中，使用if語句構(gòu)建一個兩列的數(shù)據(jù)區(qū)域。其中參數(shù)1是數(shù)組{1,0}，參數(shù)2是第一列數(shù)據(jù)，參數(shù)3是第2列數(shù)據(jù)。這樣返回的結(jié)果，恰好是這兩列數(shù)據(jù)組成的數(shù)據(jù)區(qū)域。
這兩列數(shù)據(jù)區(qū)域可以用來：
- 結(jié)合&符號，實現(xiàn)多個查詢條件合并，節(jié)省了一個輔助列。
- 數(shù)據(jù)返回列順序互換，實現(xiàn)VLOOKUP反向查找，節(jié)省了輔助列。
2-列標填充法
=VLOOKUP(A1,A:E,COLUMN())
數(shù)字填充法是指：在VLOOKUP或LOOKUP中，都包含了一個參數(shù)，表示數(shù)據(jù)區(qū)域查詢后，返回第幾列的數(shù)據(jù)，通常這個參數(shù)是固定的數(shù)字。當返回數(shù)據(jù)較多是，固定的數(shù)字，不方便后續(xù)的修改和更新。
可以使用函數(shù)來代替這個數(shù)字，實現(xiàn)返回列自動更新。常用的函數(shù)有：
- COLUMN()或ROW()，對應(yīng)VLOOKUP使用COLUMN()，HLOOKUP使用ROW()，根據(jù)單元格的移動，自定更新返回列參數(shù)。
- MATCH()，根據(jù)返回數(shù)據(jù)列的標題內(nèi)容，自動計算出返回列參數(shù)。
3-LOOKUP找邊緣
=LOOKUP(1,0/(B$2:B2<>""),B$2:B2)
LOOKUP找邊緣是指，找出數(shù)組中最后一個非N/A或#DIV!0的數(shù)據(jù)。大致原理是這樣的：
- 使用0/($B$2:B2<>"")類似的公式，構(gòu)建一個只包含0和#DIV!0的數(shù)組（暫且叫做數(shù)組A）
- 使用LOOKUP(1,數(shù)組A,返回列)的公式，查找出數(shù)組A中的最后一個0，返回對應(yīng)的數(shù)據(jù)。
- 向下填充公式，找出所有不為空的數(shù)據(jù)（數(shù)據(jù)會重復填充）
這個方法的關(guān)鍵點，在步驟1中，0除以的那個數(shù)組，要值包含true和false，然后在能形成0和#DIV!0的數(shù)組。
掌握了這幾個技巧后，后續(xù)在VLOOKUP和SUMPRODUCT等復雜的函數(shù)中，都有可能會用得到。
