Vlookup函數隔列求和

Vlookup曾是表格中最常用查找函數，但隨著新版本增加了xlookup、Filter等函數，可以輕鬆實現多條件、反向、倒查等，難道Vlookup就沒用了嗎？NO! 它還有一個絕招：隔列取數。

案例一：

Vlookup有4個參數，其中第3個參數是返回值所在的列數。正常情況下只需要返回一個值，所以常用一個整數。

=VLOOKUP(G2,B:E,4,0)

很多人不知道的是，如果第3個參數是一個組數，就可以返回多列值。

=VLOOKUP(A11,A1:E7,{2,3,5},0）

注: 非WPS和Office365版本需要按三鍵(ctrl shift enter) 輸入公式

利用這個特點可以解決工作中常見的隔列取值問題。比如最常遇到的查找後的隔列求和

【例】如下圖所示，要求根據下表的姓名，從上表中查找並隔列對實際、計劃求和。

分析：估計很多同學首先想到Sumif函數，但這是一個多列求和而且要先分產品。如果非要用它，公式為：

=SUMIF(B2:Y2,B2,OFFSET(B2:Y2,MATCH(A14,A3:A10,0),0))

或：

=SUM(SUMIF(A:A,AA2,OFFSET(A:A,,ROW(1:12)*2-1)))

用Sumproduct函數公式會簡單些，但數據量大了會很卡。

=SUMPRODUCT((A3:A10=A14)*(B2:Y2=B13)*B3:Y10)

其他函數都不理想，換作Vlookup怎麼樣？嘿嘿，很簡單！

=SUM(VLOOKUP(A14,A:Y,ROW($1:$12)*2,0))

用Vlookup查找並隔列取值，用Sum求和就可以了，其中row(1:12)生成1，2，3，4….12序號*2就變成了2，4，6…24用來取實際列的數字。

其實很多函數的參數都可以把一個數換成一組數，用來批量運算。

別怕，IF函數其實很簡單字元拆分的模式化公式，你最喜歡哪一種？

案例二：

如下圖所示，需要從上表中提取1~12月的實際數放在下表格

分析：

Vlookup4個參數，其中第3個是提取的列數，比如3就可以提取上表中第3列的值。

=VLOOKUP(A13,A:Y,3,0)

其實3可以換成一組數，提取多列，比如

=VLOOKUP(A13,A:Y,{3,5,7},0)

註：非wps和office365版本沒溢出功能，所以需要選取多列（幾個數選幾列）按Ctrl + shift+Enter輸入。

但如果有很多列，一個個輸入數字覺得麻煩，可以用Column函數生成

=COLUMN(A:L)*2+1

最終公式：

=VLOOKUP(A13,A:Y,COLUMN(A:L)*2+1,0)

如果外套 sum就可以求和了。

=SUM(VLOOKUP(A13,A:Y,COLUMN(A:L)*2+1,0))

很多excel函數的參數都可以使用數組，如sumifs、mid，實現高級用法。