第一節(jié)：VLOOKUP按指定次數(shù)重復(fù)數(shù)據(jù)
如下圖，有這樣一道題，要求按照B列的指定次數(shù)，重復(fù)C列的班級(jí)名稱(chēng)，結(jié)果放入E列。
看到這道題，好學(xué)的表親們，大概會(huì)立刻想起祝老師的某個(gè)基礎(chǔ)操作教程動(dòng)畫(huà)，函數(shù)了得的親們，會(huì)立刻想起某個(gè)多維數(shù)組套路。
但咱們這里只想VLOOKUP。
如果用VLOOKUP，這題怎么做？
很簡(jiǎn)單。
只需要兩步。
第一步，A2輸入公式：=A1+B2，向下填充
第二步，E2輸入公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&""，向下拖動(dòng)。
然后……結(jié)果……
如下圖：
我們結(jié)合兩個(gè)公式，解釋下其中過(guò)程。
第一個(gè)公式：A1+B2，是計(jì)算相關(guān)次數(shù)的累計(jì)值，比較好理解。
第二個(gè)公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&""，看起來(lái)是常用的VLOOKUP套路，但其實(shí)有兩個(gè)很有意思的地方。
其一，VLOOKUP的查找值——ROW(A1)。在公式的下拉過(guò)程中，通過(guò)查找1，2，3，4，5，（電腦配音，12345，上~山~打~老~虎）……來(lái)返回結(jié)果。
其二，屏蔽VLOOKUP錯(cuò)誤值的方式。如果VLOOKUP查找不到相關(guān)數(shù)值，比如此例中的1和2，通常會(huì)返回錯(cuò)誤值#N/A，而我們通過(guò)IFERROR，使它返回公式所在單元格的下一個(gè)單元格的值。
比如，我們?cè)贓2輸入公式，VLOOKUP函數(shù)的錯(cuò)誤值則返回E3，公式向下拖動(dòng)，E3的錯(cuò)誤值返回E4……如此類(lèi)推，直至VLOOKUP函數(shù)返回正確值——則之前通過(guò)IFERROR函數(shù)判斷為錯(cuò)誤值的單元格，自然統(tǒng)一更新為相應(yīng)的正確值（……腦海里播放多米諾骨牌從依次跌倒到依次站起的畫(huà)面）。
然后再進(jìn)行新一輪循環(huán)判斷、數(shù)據(jù)更正。
最后的&””，是函數(shù)里常用的屏蔽零值的技法，以便在VLOOKUP公式下拉過(guò)界時(shí)，返回的零值顯示為空白。
以上兩個(gè)公式，除了VLOOKUP(ROW(A1)……)的技巧外，還利用了函數(shù)的另外一個(gè)技巧，我們姑且稱(chēng)之為上下其手。
何謂上下其手？簡(jiǎn)而言之，便是拿公式所在單元格的上下單元格結(jié)果為己用。
第一個(gè)公式，=A1+B2,是上手，拿公式所在單元格的上一個(gè)單元格的值為己用。很多人比較熟悉常用，已經(jīng)很了解了。
第二個(gè)公式，=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&""，是下手，拿公式所在單元格的下一個(gè)單元格的值為己用。大家用的可能就比較少了。因?yàn)樯儆茫圆棚@得比較有意思。大家有閑時(shí)，不妨多想下，興許別有收獲哈。
==========我是似水溫柔的分割線(xiàn)==========
通過(guò)輔助列的方式，我們實(shí)現(xiàn)了按指定次數(shù)重復(fù)數(shù)據(jù)。
下面咱們要做的，便是丟掉輔助列，直接用一個(gè)公式得出結(jié)果。
即，我們需要把A列累計(jì)次數(shù)求和的數(shù)據(jù)，放入VLOOKUP公式的查找范圍中，以便直接得出所需要的結(jié)果。
我們可以使用這樣的公式：
SUMIF(OFFSET($B$2,,,ROW($1:$4),),"<>")
這是一個(gè)累計(jì)求和的多維數(shù)組套路，類(lèi)似的套路還有MMULT、INDIRECT、SUBTOTAL等。
這個(gè)公式，是通過(guò)OFFSET函數(shù)，制作多維求和統(tǒng)計(jì)范圍，比如B2:B2,B2:B3,B2:B4……
最后使用SUMIF進(jìn)行求和。
我們把這一段放入VLOOKUP函數(shù)中：
E2=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET($B$2,,,ROW($1:$4),),"<>"),$C$2:$C$5),2,0),E3)&""
如此，這個(gè)公式便正式寫(xiě)完了。
當(dāng)然，如果用LOOKUP，公式可以簡(jiǎn)潔：
=LOOKUP(ROW(A1),SUMIF(OFFSET($B$1,,,ROW($1:$5),),"<>")+1,$C$2:$C$6)&""
真是暈菜了，這一節(jié)，我發(fā)了N次，一直提示我有不良信息，我開(kāi)始以為是上下其手，但最后發(fā)現(xiàn)……
是上山打~老~虎。。
好吧，愛(ài)~老~虎油，不讓說(shuō)。
老~虎也不行。第二節(jié)：VLOOKUP查詢(xún)符合條件的多個(gè)結(jié)果。
通過(guò)第一節(jié)的內(nèi)容，我們初步認(rèn)識(shí)了VLOOKUP(ROW(A1),……)的技巧。
這一節(jié)，我們需要利用這個(gè)技巧，回答開(kāi)篇所提到的第一個(gè)問(wèn)題。
VLOOKUP能否查詢(xún)符合條件的多個(gè)數(shù)值？就像經(jīng)典數(shù)組套路INDEX+SMALL+IF那樣？
如上圖，我們需要提取C列符合F1班級(jí)的姓名，放入E4:E15。
通常我們會(huì)使用INDEX+SMALL+IF的數(shù)組套路：
E4=INDEX(C:C,SMALL(IF($B$1:$B$15=F$1,ROW($1:$15),4^8),ROW(A1)))&""
如果使用VLOOKUP，我們應(yīng)該怎么做？
其實(shí)也簡(jiǎn)單。
我們還是如第一節(jié)那般，先采用輔助列的方式。
A2=COUNTIF(B$2:B2,F$1)
向下填充。
E4=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")
向下填充。
結(jié)果……如下：
這里，咱們依然利用了VLOOKUP(ROW(A1)……)的技巧。
第一個(gè)公式：=COUNTIF(B$2:B2,F$1)
我們使用COUNTIF函數(shù)，配合相對(duì)引用的原理，統(tǒng)計(jì)班級(jí)的累計(jì)重復(fù)次數(shù)。
第二個(gè)公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")
我們通過(guò)VLOOKUP查詢(xún)ROW(a1)（1，2，3，4，5，上山打老……），來(lái)返回與之相對(duì)應(yīng)的C列姓名結(jié)果，最后外套IFERROR函數(shù)，屏蔽VLOOKUP查詢(xún)不到結(jié)果而返回的錯(cuò)誤值，使之返回空白。
在數(shù)據(jù)量大時(shí)，我們使用INDEX+SMALL數(shù)組查詢(xún)數(shù)據(jù)，難免卡機(jī)，此時(shí)不妨使用VLOOKUP+輔助列的方式，當(dāng)然，輔助列我們不能再使用低效函數(shù)COUNTIF了，我們可以使用這樣的公式：
=(B2=$F$1)+A1
==========我是往事如煙的分割線(xiàn)==========
理解了輔助列的意義，加深了VLOOKUP(ROW(A1),……)技巧的理解，我們下面要做的，依然是丟掉輔助列，把輔助列的內(nèi)容，放到公式中，直接使用一個(gè)公式得出結(jié)果。
我們依然可以使用OFFSET對(duì)COUNTIF的統(tǒng)計(jì)范圍進(jìn)行多維引用，比如：
=COUNTIF(OFFSET(B$2,,,ROW($1:$14)),F$1)
這個(gè)公式的意思，是使用COUNTIF對(duì)B2:B2,B2:B3,B2:B4……直至B2:B15的范圍內(nèi)，分別統(tǒng)計(jì)F1數(shù)值的重復(fù)次數(shù)，得出來(lái)的結(jié)果，自然是和輔助列是一致的。
我們將這一段公式，放入VLOOKUP函數(shù)公式中：
=IFERROR(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(OFFSET($B$2,,,ROW($1:$14)),$F$1),$C$2:$C$15),2,0),"")
如此，這個(gè)公式也便正式寫(xiě)完了。
==========我是如煙往事的分割線(xiàn)==========
當(dāng)然，如果您確實(shí)了解透徹了VLOOKUP的心，關(guān)于VLOOKUP查詢(xún)符合條件的多個(gè)數(shù)值，我們其實(shí)也可以寫(xiě)成這樣：
=IFERROR(VLOOKUP($F$1,OFFSET($B$1:$C$1,SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)-1),ROW(1:1)),,15),2,0),"")
或者這樣：
=IFERROR(VLOOKUP($F$1,INDIRECT("b"&SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)),ROW(1:1))&":c15"),2,0),"")
我們結(jié)合第二個(gè)函數(shù)套路來(lái)稍微解釋下此中過(guò)程。
SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)),ROW(1:1))
IF函數(shù)判斷B1:B15的值，是否等于F1，并返回相對(duì)應(yīng)的行數(shù)序號(hào)ROW(1:15)，或者FALSE。(為什么將IF的假值留白，而不是像許多學(xué)友那樣習(xí)慣性的輸入4^8之類(lèi)？因?yàn)檫@里沒(méi)有必要撒，邏輯值天生就比數(shù)值大不是……)
SMALL函數(shù)，按IF函數(shù)的結(jié)果，在公式下拉的過(guò)程中，依次從小到大取數(shù)，即ROW(1:1),ROW(2:2)，取得最小值，第二小值……。
INDIRECT函數(shù)，搭配SMALL所取得的結(jié)果，完成對(duì)VLOOKUP查找范圍從大到小的限定。
比如此例中的INDIRECT(“B”&13&”:C15”),INDIRECT(“B”&14&”:C15”)……。
由于VLOOKUP天生只取首個(gè)匹配結(jié)果，所以咱們通過(guò)查找范圍的精確限定，便可以使它依次取得所有符合條件的結(jié)果……
最后外套IFERROR函數(shù)，屏蔽錯(cuò)誤值，使之返回空白。
...
..
.
好啦，現(xiàn)在，咱們可以很清楚的知道，關(guān)于VLOOKUP無(wú)法提取符合條件多個(gè)數(shù)值的說(shuō)法，是不正確的。呵呵。（我每次發(fā)呵呵，都會(huì)想起胡劍么么噠，唉）第三節(jié)：VLOOKUP條件求和以及T/N+IF{1}技巧建立內(nèi)存數(shù)組的一個(gè)應(yīng)用小例。
這一節(jié)，我們來(lái)回答開(kāi)篇所提到的第二個(gè)以及第三個(gè)問(wèn)題：
VLOOKUP能否進(jìn)行條件求和？就像SUMIF那樣？
VLOOKUP第一個(gè)參數(shù)能否支持數(shù)組引用？
如下圖，有這樣一道題，需要在E1，求出A列存在的D3:D6班級(jí)的成績(jī)之和。
解這道題的方法有很多種，我們通常使用SUMIF：
數(shù)組：=SUM(SUMIF(A1:B5,D4:D6,B1))
或者：
數(shù)組：=SUM((A2:A5=TRANSPOSE(D4:D6))*B2:B5)
如果用VLOOKUP，又怎么做呢？
我們可以寫(xiě)成這樣：
E1=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))
這個(gè)公式不需要按數(shù)組三鍵。
我們來(lái)簡(jiǎn)單了解下這個(gè)公式。
重點(diǎn)是VLOOKUP的查找值，T(IF({1},D4:D6))。
我們知道D4:D6，是需要進(jìn)行查找統(tǒng)計(jì)的班級(jí)名稱(chēng)，那么為什么要在其外套T和IF函數(shù)？或者，我們反過(guò)來(lái)想，為什么不套T和IF函數(shù)，VLOOKUP就只對(duì)查詢(xún)范圍的第一個(gè)數(shù)值（金庸班）進(jìn)行查詢(xún)呢？
我們可以這么簡(jiǎn)單的理解。
T/N+IF組合，是讓VLOOKUP函數(shù)的第一參數(shù)，接受數(shù)組形式，因此返回相應(yīng)的內(nèi)存數(shù)組。
如此，VLOOKUP方能對(duì)每一個(gè)查找值進(jìn)行查詢(xún)統(tǒng)計(jì)。
如果為了避免錯(cuò)誤值的問(wèn)題，比如D4:D6出現(xiàn)了查詢(xún)范圍不曾出現(xiàn)的班級(jí)名稱(chēng)：天仙班，公式可以修改為：
數(shù)組：=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))
=========我是溫暖恰春的分割線(xiàn)=========
再看一道題。
如上圖，對(duì)A列存在的D4:D6的班級(jí)進(jìn)行求和，班級(jí)重復(fù)的只計(jì)算一次，答案是305。
我們通常使用這樣的數(shù)組公式：
=SUM(SUMIFS(B:B,A:A,D4:D6)/COUNTIF(A:A,D4:D6))
或者：
=SUM(N(INDIRECT("b"&MATCH(D4:D6,A1:A9,))))
其實(shí)我們也可以使用VLOOKUP：
=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))
依然由于VLOOKUP天生就只取首個(gè)匹配的結(jié)果的緣故，所以咱們也就不需要對(duì)重復(fù)數(shù)據(jù)進(jìn)行二次處理。
如果要屏蔽錯(cuò)誤值，依然要增加IFERROR：
數(shù)組=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))
=========我是恰春溫暖的分割線(xiàn)=========
綜合以上兩個(gè)問(wèn)題，咱們不難發(fā)現(xiàn)，在條件求和方面，VLOOKUP和SUMIF還是有所不同的。
如果未加以處理，VLOOKUP只對(duì)第一次出現(xiàn)的數(shù)據(jù)進(jìn)行計(jì)算，這是它的短處，當(dāng)然，未必不是它的長(zhǎng)處。
如果未加以處理，SUMIF會(huì)對(duì)所有數(shù)據(jù)進(jìn)行求和，不論重復(fù)與否，這是它的長(zhǎng)處，當(dāng)然，未必不是它的短處。
=========我只是分割線(xiàn)=========
T/N+IF{1}技巧建立內(nèi)存數(shù)組的一個(gè)應(yīng)用小例
如圖，判定D列姓名的相對(duì)累計(jì)重復(fù)次數(shù)（中文名和英文名如果是同一個(gè)人的名字，則同樣視為重復(fù)）
這道題如果用輔助列，會(huì)很簡(jiǎn)單。
直接把名字統(tǒng)一轉(zhuǎn)換為中文或者英文，再使用COUNTIF進(jìn)行重復(fù)次數(shù)計(jì)算。
比如E2公式：=IFERROR(VLOOKUP(D2,A:B,2,0),D2)，下拉后將名字統(tǒng)一更換為中文；再使用公式=COUNTIF($E$2:E2,E2),下拉后便可得出正確結(jié)果。
但如果不用輔助列呢？
如果我們繼續(xù)之前的解題思路，將查詢(xún)的名字，統(tǒng)一更換為中文或者英文，再進(jìn)行重復(fù)次數(shù)的計(jì)算，我們依然可以使用VLOOKUP函數(shù)。
比如公式：F2=SUM(N(IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2)=IF(CODE(D2)<91,VLOOKUP(D2,A:B,2,),D2)))
IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2)，是在公式下拉的過(guò)程中，將第二行到公式所在行的D列姓名，統(tǒng)一轉(zhuǎn)換為中文，并以可以計(jì)算的內(nèi)存數(shù)組的形式保存相關(guān)值。
IF(CODE(D2)<91,VLOOKUP(D2,A:B,2,),D2),是將D列需要判定重復(fù)次數(shù)的值，統(tǒng)一轉(zhuǎn)化為中文。
SUM(N……)是統(tǒng)計(jì)第一個(gè)公式的內(nèi)存數(shù)組值等于第二個(gè)公式返回值的次數(shù)，即相關(guān)名字的重復(fù)次數(shù)。第四節(jié)：VLOOKUP在字符串提取中的使用小例。
我們依然用題來(lái)說(shuō)事哈。
下面這道題，我們需要提取A列單元格內(nèi)第一個(gè)數(shù)值，結(jié)果如B列。
我們通常使用數(shù)組公式：
B2=MID(A2,MATCH(1=1,ISNUMBER(-MID(A2,ROW($1:$99),1)),),1)
上面這個(gè)公式，通過(guò)ISNUMBER和MID組合，來(lái)判斷單元格內(nèi)每一個(gè)字符是否是數(shù)值，再通過(guò)MATCH函數(shù)，對(duì)首個(gè)數(shù)值的位置進(jìn)行定位，最后通過(guò)MID函數(shù)來(lái)取值。
如果我們用VLOOKUP來(lái)處理呢？
我們可以寫(xiě)成這樣：
數(shù)組：=VLOOKUP(,MID(A2,ROW($1:$99),1)*{0,1},2,)
這個(gè)公式，依然利用MID函數(shù)，把單元格內(nèi)的字符拆成個(gè)體，分別乘以0和1，如此則產(chǎn)生兩列數(shù)據(jù)，一列由MID(A2,ROW($1:$99),1)*0得來(lái)，另外一列由MID(A2,ROW($1:$99),1)*1得來(lái)。
我們知道文本*0，是錯(cuò)誤值，數(shù)值*0，結(jié)果為0。
于是當(dāng)我們利用VLOOKUP，查找第一列的0值，得出來(lái)的結(jié)果，便是首個(gè)0值所對(duì)應(yīng)的數(shù)值——即我們所需要的結(jié)果。
這個(gè)技巧，并不僅僅局限于提取首個(gè)數(shù)字的使用，比如一個(gè)稍微復(fù)雜的示例：
如上圖。數(shù)據(jù)區(qū)域是一些數(shù)據(jù)，有的人名后有電話(huà)號(hào)碼，有的人名后沒(méi)有電話(huà)號(hào)碼，現(xiàn)在要求把沒(méi)有電話(huà)號(hào)碼的人名增補(bǔ)電話(huà)號(hào)碼，增補(bǔ)的電話(huà)號(hào)碼從哪來(lái)呢？向下數(shù)，從距離最近的擁有電話(huà)號(hào)碼的人名那兒來(lái)，（關(guān)系再遠(yuǎn)，比如表大爺，畢竟也是一家人不是？）
結(jié)果如C列。
我們可以使用這樣的公式：=B3&IF(COUNT(--MID(B3,ROW($1:$52),11)),,VLOOKUP(,MID(PHONETIC(B3:$B$36),ROW($1:$201),11)*{0,1},2,0))
IF(COUNT(--MID(B3,ROW($1:$52),11))，是判斷單元格內(nèi)是否有電話(huà)號(hào)碼。
VLOOKUP(,MID(PHONETIC(B3:$B$36),ROW($1:$201),11)*{0,1},2,0))
PHONETIC函數(shù)，將數(shù)據(jù)區(qū)域捏合為一個(gè)數(shù)據(jù)，MID函數(shù)，從中提取手機(jī)號(hào)碼，最后通過(guò)VLOOKUP(,數(shù)據(jù)*{0,1},2,)的技巧，將MID的提取結(jié)果，分別乘以0和1，如此前所言，文本乘0，為錯(cuò)誤值，數(shù)值乘0，結(jié)果為0，最后通過(guò)VLOOKUP來(lái)取得首個(gè)匹配結(jié)果，便是距離最近的手機(jī)號(hào)碼。最后有B3黏合提取的電話(huà)號(hào)碼。
…
..
.
后記：
這篇帖子，只是分享思路和技巧，并不是建議每類(lèi)問(wèn)題用vlookup去解決。術(shù)業(yè)有專(zhuān)攻，每個(gè)函數(shù)，均有長(zhǎng)處和短處，而且，數(shù)據(jù)應(yīng)該適應(yīng)函數(shù)，而不是函數(shù)來(lái)適應(yīng)數(shù)據(jù)，不管什么時(shí)候，數(shù)據(jù)錄入的規(guī)范性，都是最重要的哈。
再后記：
第一次寫(xiě)這類(lèi)分享文，從早上9點(diǎn)鐘動(dòng)筆時(shí)的信心滿(mǎn)滿(mǎn)，到中午11.30草草結(jié)束時(shí)的垂頭喪氣，這中間的過(guò)程，真他媽的苦。如果不是忌憚旁邊MM的心理承受能力，俺真想砸桌子罵臟話(huà)。妹的，俺果然還是適合講故事，不適合玩技術(shù)分析……嗯，只希望這篇破爛東西，可以開(kāi)拓大家對(duì)于VLOOKUP函數(shù)的視野，拓展下思維方式，嗯，祝安。。。
