在 Excel 函數(shù)界，名氣最大的要屬查找引用函數(shù) V 哥了。

關(guān)于它，民間還流傳著這樣一句話，它是人見人愛，花見花開，車見車爆胎的家伙。

比如，工作中最常用的數(shù)據(jù)查找引用，非它不可。

1、問題描述

如下圖：是一張集團(tuán)公司的升職加薪表。

現(xiàn)在需要將集團(tuán)公司中部分人員的崗位工資、薪級(jí)工資、崗位補(bǔ)貼和合計(jì)等內(nèi)容引用到一張新表中。

正常情況下，我們會(huì)這樣寫公式：

【L2】單元格輸入如下公式：

=VLOOKUP($K2,$B:$H,COLUMN(D1),0)

公式解析：

以【K2】單元格的內(nèi)容：「武勝」 作為查找值，在數(shù)據(jù)源區(qū)域 B 到 H 列中進(jìn)行查找，如果查找到就返回第 4 列對(duì)應(yīng)的值。

公式向右拖動(dòng)一個(gè)單元格，COLUMN (D1)（即數(shù)字 4）會(huì)變成 COLUMN (E1)（即數(shù)字 5），以此類推。

但是，大家有沒有發(fā)現(xiàn)，他只返回了每個(gè)人員第一次出現(xiàn)的值，如果，我們現(xiàn)在只需要返回升職加薪之后的數(shù)據(jù)，那該怎么辦呢？

這個(gè)時(shí)候，就輪到它的二師弟豬哥（Hlookup）上場了。

那我們一起來看看它的表演吧。

2、解決問題

Hlookup 這個(gè)函數(shù)與 Vlookup 函數(shù)是一對(duì)孿生兄弟，

Vlookup 是垂直方向查找，返回列對(duì)應(yīng)的值。

而 Hlookup 是水平方向查找，返回行對(duì)應(yīng)的值。

我們?cè)凇綥2】單元格輸入如下公式：

=HLOOKUP(L$1,$B$2:$H$14,MATCH($K2,$B$2:$B$14,0)+1,0)

公式解析：

這個(gè)函數(shù)也有四個(gè)參數(shù)，與 vlookup 完全相同。

語法如下：

第?參數(shù)是 lookup_value 查找值，

第?參數(shù)是 table_array 查找區(qū)域，

第?參數(shù)是 row_index_num 返回對(duì)應(yīng)的行值。

第?參數(shù)是 range_lookup 為精確查找。

查找值為：【L1】單元格的內(nèi)容【崗位工資】，

查找區(qū)域為：$B$2:$H$14，即首列，必須包含查找值。

返回對(duì)應(yīng)的行值，用了一個(gè) Match 來配合下，

MATCH($K2,$B$2:$B$14,0)

第一參數(shù)查找值【K2】單元格中的值「武勝」。

第二參數(shù)查找區(qū)域【$B$2:$B$14】，這個(gè)姓名所在的單元格區(qū)域中查找，并返回在這個(gè)區(qū)域中是排到第幾個(gè)單元格。比如排到第 4，就返回?cái)?shù)字 4。

第三參數(shù)為 0，表示精確查找。

因?yàn)?MATCH 函數(shù)也是返回第一查找到的值所對(duì)應(yīng)的數(shù)字，所以讓它的返回值 + 1，就返回了升職加薪之后的所在行的數(shù)字了。即 4+1=5。

于是 Hlookup 函數(shù)的結(jié)果如下：

=HLOOKUP ("崗位工資",$B$2:$H$14,5,0)

如下圖所示：

備注：Match 函數(shù)也將其中合并單元格中的空白單元格算在內(nèi)。

3、知識(shí)擴(kuò)展

某些小伙伴可能很好奇，是否一定不能用 Vlookup 函數(shù)解決這個(gè)問題，

或者有的小伙伴會(huì)很癡迷于用 Vlookup 函數(shù)，一定要用其解決。這倒也不是不行。

但是，這里需要結(jié)合 IF 函數(shù)，并進(jìn)行數(shù)據(jù)區(qū)域重新構(gòu)造。只有滿足 Vlookup 的查找要求才能讓其正確返回值。如下圖：

=VLOOKUP($K2,IF({1,0},$B$3:$B$13,E$4:E$14),2,0)

公式解析：

與常規(guī)的 Vlookup 函數(shù)的第二參數(shù)不同的是，

這里用 IF 函數(shù)進(jìn)行了兩個(gè)區(qū)域的重新構(gòu)造。

IF 的第二參數(shù)是：查找的區(qū)域，即：$B$3:$B$13

IF 的第三參數(shù)是：返回的區(qū)域，即：E$4:E$14

而且是錯(cuò)行排列的。正好符合 Vlookup 函數(shù)返回第一個(gè)查找值的要求。

另外：IF 的第三參數(shù)是：返回的區(qū)域，即：E$4:E$14，是一個(gè)行絕對(duì)引用，列相對(duì)引用，這樣在向右拖動(dòng)公式的時(shí)候，可以返回正確的列。

因?yàn)檫@個(gè)公式是數(shù)組公式，所以還需要按三鍵【Ctrl+Shift+Enter】結(jié)束（PS.Office 365 按【Enter】即可）。

上面的問題到此就解決了。

But，領(lǐng)導(dǎo)們的想法是會(huì)隨時(shí)變化的。

比如，現(xiàn)在的領(lǐng)導(dǎo)的要求是：

不僅需要看升職加薪之后的明細(xì)數(shù)據(jù)，也需要同時(shí)查看升職加薪之前的數(shù)據(jù)。那該怎么辦呢？就像下圖這樣：

這種情況下，如果要用函數(shù)的話，還真是要豬哥上場了。

而且原來的公式基本不用改動(dòng)，只需要增加一個(gè)求余函數(shù) + 返回行號(hào)的函數(shù)就行。

公式如下：

公式解析：

第三參數(shù)那里增加 MOD (ROW (A2),2)。其中：

ROW (A2)，返回行號(hào) 2（即：ROW (A2)=2），作為 MOD 的函數(shù)參數(shù)。

MOD (2,2)，然后對(duì) 2 求余額數(shù)即為：0。（即：MOD (2,2)=0）

然后 MATCH 查找到的值再加上 0 值，還是返回 MATCH 的值。

公式向下拖動(dòng)即返回：MOD (ROW (A3),2)。其中：ROW (A3)，返回行號(hào) 3（即：ROW (A3)=3），作為 MOD 的函數(shù)參數(shù)。

MOD (3,2)， 然后對(duì) 3 求余額數(shù)即為：1。（即：MOD (3,2)=1）

最終 MATCH 的返回值 + 1。

這里的關(guān)鍵點(diǎn)是：

用 MOD 函數(shù)來代替原來公式中的 + 1 這個(gè)值，調(diào)節(jié)返回 0 和 1 兩個(gè)數(shù)字。

4、寫在最后

今天我們分享了 Vlookup 函數(shù)二師弟，豬哥 Hlookup 函數(shù)的用法。

在大部分情況下，Vlookup 的查找還是非常方便實(shí)用的，但是在某些情況下，真不如它的師弟 Hlookup 函數(shù)。

另外在上面的例子中，雖然 Vlookup 函數(shù)結(jié)合 IF 函數(shù)進(jìn)行數(shù)據(jù)區(qū)域重新構(gòu)造也能解決問題，但是在實(shí)際工作中，不建議使用。

因?yàn)椋且粋€(gè)數(shù)組公式，在數(shù)據(jù)比較多的情況下，會(huì)導(dǎo)致表格很卡。

所以，盡量不使用數(shù)組公式，只使用簡單公式的組合，而且組合越少越好，計(jì)算次數(shù)越少越好，這才是工作中需要實(shí)現(xiàn)的目標(biāo)。

好了，今天我們就分享到這里，如果喜歡此篇文章，歡迎點(diǎn)贊 & 轉(zhuǎn)發(fā)！

除了上面介紹的 Hlookup 函數(shù)，Excel 里還有很多的函數(shù)，比如 Lookup、Xlookup、Sumif 等等。

本文來自微信公眾號(hào)：秋葉 Excel （ID：excel100），作者：明鏡在心