VLOOKUP經(jīng)典用法12例
?
作者:小窩來源:部落窩教育發(fā)布時間:2023-09-27 17:11:58點擊:1085
VLOOKUP是幾乎所有Excel用戶都會的一個函數(shù),本教程總結了它的12例經(jīng)典用法,看看你會多少。
凡是求職簡歷中寫Excel技能的,80%的人都寫了熟練操作VLOOKUP函數(shù)。VLOOKUP函數(shù)似乎成了求職的一個敲門磚或者試金石了。
但是你真的熟練使用VLOOKUP嗎?看看下面的用法你知道有多少。
第1例:查找同一產品的多個列值
1)各值順序與查找區(qū)域保持一致,搭配COLUMN
譬如下方,不需要笨些些地分別去修改第3參數(shù)獲取各值,搭配COLUMN函數(shù)自動搞定。
低版本
=VLOOKUP($A21,$C$2:$G$17,COLUMN(B1),0),右拉填充
高版本(2021及365版本,下同)
=VLOOKUP($A21,$C$2:$G$17,COLUMN(B1:E1),0)
2)各值順序與查找區(qū)域不一致,搭配MATCH函數(shù)
低版本
=VLOOKUP($A21,$C$2:$G$17,MATCH(B20,$C$1:$G$1,0),0),右拉填充。
高版本
=VLOOKUP($A21,$C$2:$G$17,MATCH(B20:E20,$C$1:$G$1,0),0)
第2例:多條件查找
直接把多個條件合并作為一個條件進行查找。
=VLOOKUP(A27&B27,IF({1,0},$A$2:$A$23&$C$2:$C$23,$F$2:$F$23),2,0)
第3例:反向查找
可以搭配經(jīng)典的IF函數(shù){1,0}結構,也可以搭配CHOOSE函數(shù)進行選擇。
=VLOOKUP(A27,IF({1,0},C2:C23,B2:B23),2,0)
或者
=VLOOKUP(A27,CHOOSE({1,2},C2:C23,B2:B23),2,0)
第4例:包含查找(使用通配符查找)
查找包含了某某字符的數(shù)據(jù),可以使用通配符進行查找。
通配符“*”,表示任意個數(shù)的任意字符;通配符“?”,表示一個任意字符。
譬如查找包含“雞蛋”的品名。
=VLOOKUP("*"&A27&"*",C2:D23,2,0)
再譬如查找包含了“雞蛋”并且“雞蛋”前只有3個字符的品名:
=VLOOKUP("???"&B27,D2:D23,1,0)
第5例:查找值包含了通配符的查找
1)查找值含有波浪號~
波浪號“~”是一種特殊通配符,作用是將其他通配符轉化為普通符號。如果查找包含波浪號,必須在波浪號前再添加一個波浪號將其轉化為普通符號使用,否則查找會出錯。
正確的公式:
=VLOOKUP("86~~",A2:B8,2,0)
或者
=VLOOKUP(SUBSTITUTE(D2,"~","~~"),A2:B8,2,0)
2)查找值含有通配符*或者?
如果查找值含有通配符*或者?,同樣需要在通配符前添加波浪號~將其轉化為普通符號,否則可能出現(xiàn)錯誤。
第6例:一對多查找
VLOOKUP默認只返回第一個符合條件的結果。如果需要返回所有符合條件的結果,可以添加輔助列為每個結果編上不同的序號,然后再用VLOOKUP查找序號返回結果。
譬如查所有含“雞蛋”兩字的品名。
Step 01 插入空列,輸入公式=IF(IFERROR(FIND($B$27,D2),0)>0,A1+1,A1)并向下填充。
Step 02 在C27中輸入公式=IFERROR(VLOOKUP(ROW(A1),$A$2:$D$23,4,0),"")并向下拖動填充直到出現(xiàn)空單元格為止。
因為原始數(shù)據(jù)中品名存在重復,所以得到的品名也有重復。如果需要不重復,則修改序號公式即可。
=IF(COUNTIF($D$2:D2,D2)>1,"",IF(IFERROR(FIND($B$27,D2),0)>0,A1+1,A1))
第7例:區(qū)間或等級查找
運用VLOOKUP的近似匹配功能可以實現(xiàn)區(qū)間或等級查找。兩個條件:
(1)省略第四參數(shù),或者將其設置為1;
(2)查找區(qū)域首列升序排列。
譬如求銷售業(yè)績的等級。
=VLOOKUP(B2,$E$2:$F$5,2,1)或者=VLOOKUP(B2,$E$2:$F$5,2)
第8例:提取15位以內統(tǒng)一位數(shù)的數(shù)字,如手機號
=VLOOKUP(0,MID(A2,ROW($1:$50),11)*{0,1},2,0)
注:由于Excel的最大精度是15位,所以此法只能提取15位及以內的數(shù)字,不能用來提取銀行卡號、身份證號等。
第9例:核對數(shù)據(jù)
1)核對是否一致
譬如核對兩份數(shù)據(jù)中籍貫是否一致。
=IFERROR(IF(VLOOKUP(A2,$E$2:$F$9,2,)=B2,"相同","籍貫不同"),"姓名不同")
2)核對數(shù)字相差多少
=TEXT(VLOOKUP(A13,$E$13:$F$20,2,0)-B13,"少0.0;多0.0;相同")
第10例:合并單元格查找
1)合并單元格內容作為查找結果
=VLOOKUP("咗",INDIRECT("a1:a"&MATCH(E2,$B$2:$B$14,0)),1)
為何用“咗”查?點此了解Excel數(shù)據(jù)大小排序。
2)合并單元格內容作為查找值
=VLOOKUP(VLOOKUP("咗",$A$1:A2,1),$E$6:$F$8,2,)
第11例:查找最后一次報價
低版本:
=VLOOKUP(1,IF({1,0},(MAX(IF($B$2:$B$11=E2,$A$2:$A$11))=$A$2:$A$11)*($B$2:$B$11=E2),$C$2:$C$11),2,0)
高版本:
=VLOOKUP(E2,SORTBY($B$2:$C$11,$A$2:$A$11,-1),2,0)
第12例:跨表查找
可以跨單表、多表進行查找,具體見《VLOOKUP 的人生巔峰:跨多表多文件查找!》。
本文配套的練習課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇!
掃下方二維碼關注公眾號,可隨時隨地學習Excel:
相關推薦:
版權申明:
本文作者小窩;部落窩教育享有稿件專有使用權。若需轉載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復,Excel卻判定數(shù)據(jù)重復,這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!