vlookup和match配合多條件查找(VlookupMatchColumn會碰出怎樣的火花)
2023-05-04 11:38:09
Hi,
Vlookup的確可以算得上是EXCEL高頻函數了。但是往往英雄孤掌難鳴,Vlookup也不例外。Vlookup配合match,column等函數,將大大提升Vlookup函數的使用效率,更適應更多的業務場景。
因此,今天我們來聊聊Vlookp match column會碰出怎樣的火花?
一、初始Match
Match字面意思是匹配,配對的意思。
Match函數的語法為:
MATCH(lookup_value, lookup_array, [match_type])MATCH(查找值, 查找範圍, 匹配模式),函數返回查找值在查找範圍內的位置查找值和查找範圍比較好理解,匹配模式需要理解一下:
匹配模式有三個可選參數:
為1,表示查找查找範圍小於或等於查找值的最大值,查找範圍必須升序排列。為0,表示精確匹配,表示查找到查找值在查找範圍中的第一個精確匹配位置,查找範圍排序無關緊要。為-1,表示查找查找範圍大於或等於查找值的最小值,查找範圍必須降序排列。舉個例子就明白了。
數據如下:
當第三個參數為1或者-1時,計算機制要理解一下。參數為0是用得最多的,這個比較好理解。和vlookup聯合用的時候,一般匹配也是精確匹配。
二、初識Column
Column是列的意思,在EXCEL中column函數非常簡單,返回所在列的序號。它和row是一對函數。Row函數返回所在行的行號。
Column,參數為空時,返回單元格所在的列號。
Coloumn(B:B),返回B列所在的列號。B列處於第二列,因此返回值為2。
三、Vlookup、Match、Column配合機制
Match和column函數可以配合vlookup實現動態區域引用,其作用機制在於可以動態返回vlookup查找區域的列的位置。
Match和column作用於Vlookup的第三個參數,成為vlookup的嵌套函數。
1.Vlookup column實現大範圍固定位置數據引用
先看需求,以下有兩張表,表1是員工基礎信息表,表格2需要從基礎信息表中,將姓名到總工資所有的內容都複製過去。
當然,你可以一條一條找,然後再複製,但這樣相當容易出錯。
這個時候我們就可以在vlookup中嵌套column了。
單元格B26的公式為:
=VLOOKUP($A26,$A$2:$I$22,COLUMN(B:B),0)
因為要往後及往下複製公式,需要將工號單元格B26列相對引用,以及查找區域$A$2:$I$22絕對引用。查找區域需要選擇一個最大的區域。
因為姓名是在查找區域的第2列,因此column參數是B:B列相對引用。在往後複製公式的時候,自動會變成C、D、E…,從而動態返回2,3,4等數,實現了動態引用返回的列。
2.Vlookup match實現大範圍不固定位置引用
先看需求,我們有一個各個部門的分月費用執行表。好心的同事,以及幫我們做好了。部門在行上,月份在列上。
現在給定部門和月份,動態查詢所產生的成本。分析一下就會發現,這個時候,vlookup返回列的位置參數就是不確定了,需要根據指定月份來確定。
D11單元格公式:
=VLOOKUP(B11,$B$2:$N$6,MATCH(C11,$C$2:$N$2,0) 1,0)
Vlookup第一個、第二個參數都好理解。
這裡match實現了拿月份在月份行中去匹配位置,這裡的月份引用要絕對引用。 1是因為,vlookup返回列是從其引用範圍的第2列開始的。這個參數需要結合實際情況予以調整。
四、總結
Vlookup還可以與其它函數結合起來實現更為複雜的功能。而match和coloumn是針對vlookup函數的第三個參數進行調整。
後續如果需要對vlookup函數的第二個參數進行動態調整的話,可能需要用到indirect或者offset等函數。後續有機會我們再專題討論。
以上,是今日分享的內容。
雖然vlookup很簡單,但是和其它函數結合在一起就可以實現我們的各種功能。
函數在Excel數據分析中非常重要,因為我們需要用它們來實現各種數據分析。
我是華哥。每日精進,不負光陰韶華。
贈人玫瑰,手留餘香。歡迎評論、點讚、關注,轉發。
,