在 Excel 函數(shù)界，名氣最大的要屬查找引用函數(shù) V 哥了。
關(guān)于它，民間還流傳著這樣一句話，它是人見人愛，花見花開，車見車爆胎的家伙。
比如，工作中最常用的數(shù)據(jù)查找引用，非它不可。
1、問題描述
如下圖：是一張集團(tuán)公司的升職加薪表。
現(xiàn)在需要將集團(tuán)公司中部分人員的崗位工資、薪級工資、崗位補(bǔ)貼和合計(jì)等內(nèi)容引用到一張新表中。
正常情況下，我們會這樣寫公式：
【L2】單元格輸入如下公式：
=VLOOKUP($K2,$B:$H,COLUMN(D1),0)
公式解析：
以【K2】單元格的內(nèi)容：「武勝」 作為查找值，在數(shù)據(jù)源區(qū)域 B 到 H 列中進(jìn)行查找，如果查找到就返回第 4 列對應(yīng)的值。
公式向右拖動一個(gè)單元格，COLUMN (D1)（即數(shù)字 4）會變成 COLUMN (E1)（即數(shù)字 5），以此類推。
但是，大家有沒有發(fā)現(xiàn)，他只返回了每個(gè)人員第一次出現(xiàn)的值，如果，我們現(xiàn)在只需要返回升職加薪之后的數(shù)據(jù)，那該怎么辦呢？
這個(gè)時(shí)候，就輪到它的二師弟豬哥（Hlookup）上場了。
那我們一起來看看它的表演吧。
2、解決問題
Hlookup 這個(gè)函數(shù)與 Vlookup 函數(shù)是一對孿生兄弟，
Vlookup 是垂直方向查找，返回列對應(yīng)的值。
而 Hlookup 是水平方向查找，返回行對應(yīng)的值。
我們在【L2】單元格輸入如下公式：
=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 返回對應(yīng)的行值。
第?參數(shù)是 range_lookup 為精確查找。
查找值為：【L1】單元格的內(nèi)容【崗位工資】，
查找區(qū)域為：$B$2:$H$14，即首列，必須包含查找值。
返回對應(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ù)也是返回第一查找到的值所對應(yīng)的數(shù)字，所以讓它的返回值 + 1，就返回了升職加薪之后的所在行的數(shù)字了。即 4+1=5。
于是 Hlookup 函數(shù)的結(jié)果如下：
=HLOOKUP ("崗位工資",$B$2:$H$14,5,0)
如下圖所示：
備注：Match 函數(shù)也將其中合并單元格中的空白單元格算在內(nèi)。
3、知識擴(kuò)展
某些小伙伴可能很好奇，是否一定不能用 Vlookup 函數(shù)解決這個(gè)問題，
或者有的小伙伴會很癡迷于用 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è)行絕對引用，列相對引用，這樣在向右拖動公式的時(shí)候，可以返回正確的列。
因?yàn)檫@個(gè)公式是數(shù)組公式，所以還需要按三鍵【Ctrl+Shift+Enter】結(jié)束（PS.Office 365 按【Enter】即可）。
上面的問題到此就解決了。
But，領(lǐng)導(dǎo)們的想法是會隨時(shí)變化的。
比如，現(xiàn)在的領(lǐng)導(dǎo)的要求是：
不僅需要看升職加薪之后的明細(xì)數(shù)據(jù)，也需要同時(shí)查看升職加薪之前的數(shù)據(jù)。那該怎么辦呢？就像下圖這樣：
這種情況下，如果要用函數(shù)的話，還真是要豬哥上場了。
而且原來的公式基本不用改動，只需要增加一個(gè)求余函數(shù) + 返回行號的函數(shù)就行。
公式如下：
公式解析：
第三參數(shù)那里增加 MOD (ROW (A2),2)。其中：
ROW (A2)，返回行號 2（即：ROW (A2)=2），作為 MOD 的函數(shù)參數(shù)。
MOD (2,2)，然后對 2 求余額數(shù)即為：0。（即：MOD (2,2)=0）
然后 MATCH 查找到的值再加上 0 值，還是返回 MATCH 的值。
公式向下拖動即返回：MOD (ROW (A3),2)。其中：ROW (A3)，返回行號 3（即：ROW (A3)=3），作為 MOD 的函數(shù)參數(shù)。
MOD (3,2)， 然后對 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ù)比較多的情況下，會導(dǎo)致表格很卡。
所以，盡量不使用數(shù)組公式，只使用簡單公式的組合，而且組合越少越好，計(jì)算次數(shù)越少越好，這才是工作中需要實(shí)現(xiàn)的目標(biāo)。
好了，今天我們就分享到這里，如果喜歡此篇文章，歡迎點(diǎn)贊 & 轉(zhuǎn)發(fā)！
除了上面介紹的 Hlookup 函數(shù)，Excel 里還有很多的函數(shù)，比如 Lookup、Xlookup、Sumif 等等。
本文來自微信公眾號：秋葉 Excel （ID：excel100），作者：明鏡在心
廣告聲明：文內(nèi)含有的對外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時(shí)間，結(jié)果僅供參考，IT之家所有文章均包含本聲明。