Excel一對多查詢,少不了這兩刷子
我們對數據進行查詢時,經常會使用VLOOKUP函數。
但有時,我們會碰到這樣的問題,提取符合條件的結果是多個,而不是一個,這時候VLOOKUP就犯難了。
舉個例子,如下圖,左側A1:C10是一份學員名單表,現在需要根據F1單元格的「EH圖班」這個指定的條件,在F2:F10單元格區域中,提取該班級全部學員名單。
今天說一個函數查詢方面的萬金油套路:Index+Small。
F2單元格輸入以下數組公式,按住Ctrl+Shift鍵不放,再按回車鍵,然後向下填充:
=INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10),4^8),ROW(A1))),"")
這個公式看起來可就比上面那個VLOOKUP的解法苗條養眼多了,坦白的說,很搭俺星光十年後的匪號——小清新。
公式講解
IF(A$1:A$10=F$1,ROW($1:$10),4^8)
這部分,先判斷A1:A10的值是否等於F1,如果相等,則返回A列班級相對應的行號,否則返回4^8,也就是65536,一般情況下,工作表到這個位置就沒有數據了。
結果得到一個內存數組:
SMALL函數對IF函數的結果進行取數,隨著公式的向下填充,依次提取第1、2、3……n個最小值,由此依次得到符合班級條件的行號。
隨後使用INDEX函數,以SMALL函數返回的行號作為索引值,在B列中提取出對應的姓名結果。
當SMALL函數所得到的結果為65536時,意味著符合條件的行號已經被取之殆盡了,此時INDEX函數也隨之返回B65536單元格的引用,結果是一個無意義的0,為了避免這個問題,可以在公式後面加上一個小尾巴&""
利用&」」的方法,很巧妙的規避了無意義0值的出現,只是當查找結果為數值或日期時,這個方法會把數值轉變為文本值,並不利於數據的準確呈現以及再次統計分析。
圖文製作:看見星光
易學寶微視頻教程,1290個Office技巧精粹,每個技巧都與實際工作密切相關。輕鬆學習技巧,練就職場達人,淘寶搜索關鍵字:ExcelHome易學寶
![](https://pic.pimg.tw/zzuyanan/1488615166-1259157397.png)
![](https://pic.pimg.tw/zzuyanan/1482887990-2595557020.jpg)
※市場、銷售部如何辦公更高效?
※這樣的Excel日期計算思路,23.6%的人沒想到
※SUBSTITUTE的常用套路,都給你了
※展示計劃和進度,這個Excel圖表剛剛好
※漂亮的Excel瀑布圖,竟然如此簡單
TAG:Excel之家ExcelHome |
※學Excel,誰還沒有兩把刷子!
※我一打開Excel就頭疼的毛病,終於被這位Excel大神治好了
※懶人專用,這個Excel圖表老闆只需看一眼!
※有多少人的職場進階,卡在了用不好Excel?
※Excel太low了,解決一單採購審批只需5秒鐘
※用了這麼久的Excel,這些小技巧你知道嗎?
※那些不加班的人,都是怎麼做Excel的?
※Python讀寫Excel表格,就是這麼簡單粗暴又好用
※別讓Excel拖了你的後腿
※Excel小趣聞:你知道excel一共有多少行嗎?
※兩則Excel小技巧,知道的人不多,但很實用!
※神了!Excel更新後還能這麼用!
※Excel中的這個小問題,曾經是否將你迷惑?
※為了Excel,你沒少花心思吧,為什麼還是用不好?
※從Excel到Python,我到底經歷了什麼
※對不起,我們不招25歲還用不好Excel的人!
※windows 10再出bug Excel直接崩潰 不把老用戶當人
※PDF文件轉word、excel、PPT、解除密碼限制,這些都不失問題,一起來看看
※「百曉生」一言不合,向我扔來一份阿里安全大牛的Excel
※5秒就能解決的Excel問題,你竟然花了9個多小時!