時間：2017年8月2日

地點：廣州市白云區(qū)國際大酒店

參賽者：『仰望~星空』、『Coffee』、『君柳』

主持人：『L-L-X』、『花花』

評委：全體圍觀吃瓜觀眾

拍攝：『蜂鳥』、『小鳥』

男：尊敬的各位領導、各位嘉賓

女：親愛的觀眾朋友、媒體朋友

合：大家晚上好！

男：非常感謝大家百忙之中來參加【愛上Excel合伙人】舉辦的一年一度函數(shù)技能PK賽，我是今晚的主持人L-L-X。

女：我是今晚的主持人花花，首先我非常榮幸的向大家介紹，光臨本次比賽的嘉賓和評委，他們是……。

男：參賽者『仰望~星空』、『Coffee』、『君柳』分別是VLOOKUP、LOOKUP和INDEX+MATCH三大查找函數(shù)的代表。

女：她們將在此次比賽中用各自代表的函數(shù)完成所有比賽。

男：此次比賽本著“學習第一，比賽第二”的原則，互相切磋，取長補短。

女：目的是為了讓大家明白這三大查找函數(shù)各自的特點，從而在遇到問題時能使用更合適的、更高效的方法去解決問題。

男：本次比賽共十場

第一場：常規(guī)查找

第二場：查找返回多列數(shù)據(jù)

第三場：通配符查找

第四場：帶“~”的查找

第五場：區(qū)間等級查找

第六場：逆向查找

第七場：交叉查詢

第八場：合并單元格的引用問題

第九場：多條件查找

第十場：一對多查找

女：誰勝誰負由全體圍觀吃瓜觀眾投票選出

男：下面我宣布

合：比賽正式開始

女：下面進入的是我們的第一場比賽：常規(guī)查找。

請看題：查找出Leaf的愛好是什么？

男：參賽者請作答

• VLOOKUP
  

公式：

=VLOOKUP(F2,A2:D8,4,0)

• LOOKUP

公式：

=LOOKUP(1,0/(F2=A2:A8),D2:D8)

• INDEX+MATCH

公式：

=INDEX(D2:D8,MATCH(F2,A2:A8,0))

女：我宣布第一場比賽結束。

男：第一場題目簡單，三大函數(shù)不費吹灰之力，輕松解決。

女：接下來是第二場比賽，查找返回多列數(shù)據(jù)。

請看題：根據(jù)姓名按順序查找相關信息

男：參賽者請作答

• VLOOKUP
  

公式：

=VLOOKUP($F2,$A2:$D8,COLUMN(B1),0)

• LOOKUP

公式：

=LOOKUP(,0/($F2=$A2:$A8),B2:B8)

• INDEX+MATCH

公式：

=INDEX(B2:B8,MATCH($F2,$A2:$A8,0))

女：我宣布第二場比賽結束。

男：第二場比賽也已經(jīng)結束，三大函數(shù)之間的差距貌似還沒有拉開。

女：即將開始我們的第三場比賽，通配符查找。

請看題：根據(jù)簡稱查找對應的應付賬款

男：參賽者請作答

• VLOOKUP
  

公式：

=IFNA(VLOOKUP('*'&D2&'*',A$2:B$6,2,),'')

• LOOKUP

公式：

=IFNA(LOOKUP(,0/FIND(D2,$A$2:$A$6),B$2:B$6),'')

• INDEX+MATCH

公式：

=IFNA(INDEX(B$2:B$6,MATCH('*'&D2&'*',A$2:A$6,0)),'')

女：我宣布第三場比賽結束。

男：這場比賽中她們都請了幫手函數(shù)IFNA進行容錯，而Lookup函數(shù)不支持通配符使用，運用了Lookup+Find組合。

女：比賽仍在火熱進行中，第四場帶“~”的查找。

請看題：根據(jù)姓名查找地區(qū)

男：參賽者請作答

• VLOOKUP
  

公式：

=VLOOKUP(SUBSTITUTE(F2,'~','~~'),A2:B7,2,0)

• LOOKUP

公式：

=LOOKUP(1,0/(F2=A2:A7),B2:B7)

• INDEX+MATCH

公式：

=INDEX(B2:B7,MATCH(SUBSTITUTE(F2,'~','~~'),A2:A7,))

女：我宣布第四場比賽結束。

男：波形符（~）作為通配符，在查找包含其本身的值時，需在“~”前鍵入“~”，函數(shù)VLOOKUP和INDEX+MATCH都借助了外援函數(shù)SUBSTITUTE將“~”替換成“~~”，而函數(shù)LOOKUP卻能不借助外援，輕松應對。

女：第五場是區(qū)間等級查找。

請看題：根據(jù)成績查找對應等級

男：參賽者請作答

• VLOOKUP
  

公式：

=VLOOKUP(B2,E$2:F$5,2)

• LOOKUP

公式：

=LOOKUP(B2,E$2:F$5)

• INDEX+MATCH

公式：

=INDEX(F$2:F$5,MATCH(B2,E$2:E$5,1))

女：我宣布第五場比賽結束。

男：該場比賽中VLOOKUP省略了第四參數(shù)，近似匹配；

LOOKUP使用了數(shù)組形式，注意數(shù)組中的值是按升序排列；

INDEX+MATCH組合中MATCH使用了模糊查找，查找小于或等于查找值的最大值，查找區(qū)域是按升序排列。

女：比賽到目前為止已經(jīng)進行了一半，前半場轟轟烈烈，后半場也將更加精彩！

男：大家可以稍事休息，聽聽歌，左手土豆，右手L-L-X，喝著咖啡，頂著星空，左手搭右手喝著咖啡再抬頭仰望星空

女：一段時間的休息之后請大家回到各自的座位，接下來是第六場比賽，逆向查找。

請看題：根據(jù)愛好查找相關信息

男：參賽者請作答

• VLOOKUP
  

公式：

=VLOOKUP($F2,CHOOSE({1,2},$D2:$D7,A2:A7),2,0)

• LOOKUP

公式：

=LOOKUP(,0/($F2=$D2:$D7),A2:A7)

• INDEX+MATCH

公式：

=INDEX(A2:A7,MATCH($F2,$D2:$D7,))

女：我宣布第六場比賽結束。

男：該場賽題似乎給函數(shù)VLOOKUP出了難題，她不得不借助外援，比如請出了CHOOSE（{1,2}）。

女：第七場比賽，交叉查詢。

請看題：根據(jù)業(yè)務員和月份查找對應的銷售額

男：參賽者請作答

• VLOOKUP
  

公式：

=VLOOKUP(F2,A2:D7,MATCH(G2,A1:D1,0),0)

• LOOKUP

公式：

=LOOKUP(,0/(A2:A7=F2),OFFSET(A2:A7,,MATCH(G2,B1:D1,0)))

• INDEX+MATCH

公式：

=INDEX(B2:D7,MATCH(F2,A2:A7,),MATCH(G2,B1:D1,))

女：我宣布第七場比賽結束。

男：INDEX+MATCH組合似乎就是為該種查詢而生。

女：第八場是合并單元格的引用問題。

請看題：根據(jù)客服引用對應的直播課程

男：參賽者請作答

• VLOOKUP
  

公式：

=VLOOKUP('座',OFFSET(A2,,,MATCH(G2,B2:B16,)),1,1)

• LOOKUP

公式：

=LOOKUP('座',OFFSET(A2,,,MATCH(G2,B2:B16,)))

• INDEX+MATCH

公式：

=INDEX(A2:A16,MATCH('座',OFFSET(A2,,,MATCH(G2,B2:B16,)),1))

女：我宣布第八場比賽結束。

男：該場比賽中她們三心有靈犀似的都用了幫手OFFSET與MATCH。

女：比賽越來越激烈了，第九場多條件查找。

請看題：根據(jù)業(yè)務員和區(qū)域查找對應的銷售額

男：參賽者請作答

• VLOOKUP
  

公式：

=VLOOKUP(E2&F2,IF({1,0},A$2:A$9&B$2:B$9,C$2:C$9),2,0)

數(shù)組公式，按Ctrl+Shift+Enter>三鍵結束。

• LOOKUP

公式：

=LOOKUP(,0/(E2&F2=A$2:A$9&B$2:B$9),C$2:C$9)

• INDEX+MATCH

公式：

=INDEX(C$2:C$9,MATCH(E2&F2,A$2:A$9&B$2:B$9,))

數(shù)組公式，按Ctrl+Shift+Enter>三鍵結束。

女：我宣布第九場比賽結束。

男：多條件查找都運用了連接符“&”，用VLOOKUP和INDEX+MATCH都是數(shù)組公式。

女：接下來是我們的最后一場比賽，一對多查找。

請看題：根據(jù)條件查找出對應的所有地區(qū)

男：參賽者請作答

• VLOOKUP
  

公式：

=IFERROR(VLOOKUP(D$2&ROW(A1),IF({1,0},A$2:A$10&COUNTIF(INDIRECT('A2:A'&ROW($2:$10)),D$2),B$2:B$10),2,),'')

數(shù)組公式，按Ctrl+Shift+Enter>三鍵結束。

• LOOKUP

公式：

=IFERROR(LOOKUP(,0/(D$2&ROW(A1)=A$2:A$10&COUNTIF(INDIRECT('A2:A'&ROW($2:$10)),D$2)),B$2:B$10),'')

• INDEX+MATCH

公式：

=IFERROR(INDEX(B$2:B$10,MATCH(D$2&ROW(A1),A$2:A$10&COUNTIF(INDIRECT('A2:A'&ROW($2:$10)),D$2),)),'')

數(shù)組公式，按Ctrl+Shift+Enter>三鍵結束。

女：我宣布第十場比賽結束。

男：果然好戲在后頭，這一場比賽看把我緊張的汗都流出來了，沒想到她們都能解決。

女：LOOKUP面對此題仍面不改色，輕松拿下，而VLOOKUP和INDEX+MATCH都是數(shù)組公式，要按三鍵結束。

男：不管黑貓白貓，抓到老鼠就是好貓，雖然歷經(jīng)波瀾，借助外援，但總歸還是解決了問題。

女：到目前為止，十場比賽都已經(jīng)圓滿結束，接下來是我們的投票環(huán)節(jié)。

男：請所有評委本著公平公正公開的原則為本次比賽投上您神圣的一票，選出您心目中認為能高效解決您問題的一個。

有關函數(shù)VLOOKUP、LOOKUP和INDEX+MATCH的詳細介紹，請點擊下面鏈接跳轉：

函數(shù)篇：人見人愛的VLOOKUP，你真的會用他嗎？

函數(shù)篇：查找函數(shù)哪家強，LOOKUP我最強！

函數(shù)篇：一對好基友INDEX+MATCH

也可以通過如下方法查找你想要的資料：