Hi，我是偏愛(ài)函數(shù)公式，愛(ài)用 Excel 圖表管理倉(cāng)庫(kù)的大叔 Mr 趙~

前段時(shí)間秋葉 Excel 發(fā)了篇講VLOOKUP 的文章，有同學(xué)留言問(wèn)為什么不講XLOOKUP?函數(shù)？

我覺(jué)得可以！

所以我們今天就來(lái)講講 XLOOKUP，一個(gè)強(qiáng)大的查詢函數(shù)！

在說(shuō) XLOOKUP 函數(shù)之前，咱們先看看下面這個(gè)案例，根據(jù)姓名查找對(duì)應(yīng)的性別。

這個(gè)問(wèn)題太常見(jiàn)了！首先想到的就是用 VLOOKUP 函數(shù)。

• =VLOOKUP(E2,A2:C7,2,0)

在查找區(qū)域 A2:C7 的首列找到 E2 單元格的值「王五」，返回查找區(qū)域 A2:C7 第 2 列與之對(duì)應(yīng)的值「男」。

初識(shí) XLOOKUP 函數(shù)


再來(lái)看看 XLOOKUP 函數(shù)的用法：

=XLOOKUP（查找的值，查找范圍，結(jié)果范圍）

公式就可以這樣寫(xiě)：

• =XLOOKUP(E2,A2:A7,B2:B7)
  

在查找范圍 A2:A7 中找到 E2 單元格的值「王五」，返回 B2:B7 對(duì)應(yīng)的值「男」。

如果姓名這列不是在前面，用 VLOOKUP 函數(shù)似乎就不太合適了。

因?yàn)?VLOOKUP 函數(shù)的規(guī)則是在查找區(qū)域的首列查找。

這種情況下，我們通常會(huì)用?INDEX 函數(shù)和 MATCH 函數(shù)組合寫(xiě)公式：

• =INDEX(A2:A7,MATCH(E2,B2:B7,0))
  

MATCH 函數(shù)找出 E2 的值「王五」在 B2:B7 中是第幾行，得到結(jié)果 3，然后用 INDEX 函數(shù)將 A2:A7 的第 3 行的值引用出來(lái)，得到結(jié)果「男」。

而 XLOOKUP 函數(shù)就不一樣了，它不會(huì)像 VLOOKUP 那樣受位置的影響，依然照用不誤：

• =XLOOKUP(E2,B2:B7,A2:A7)

在查找區(qū)域 B2:B7 中找到 E2 單元格的值「王五」，返回 A2:A7 對(duì)應(yīng)的值「男」。?

XLOOKUP 函數(shù)的第 4 參數(shù)



經(jīng)常有小伙伴提這樣的問(wèn)題，如何讓 VLOOKUP 查找不到的數(shù)據(jù)返回為空值？

?如下圖，VLOOKUP 函數(shù)在查找區(qū)域 A2:B7 的首列沒(méi)有找到單元格的值「孫二」,就會(huì)返回錯(cuò)誤值#N/A。?

• =VLOOKUP(D2,A2:B7,2,0)?
  

?通常我們都會(huì)在 VLOOKUP 函數(shù)外層嵌套 IFERROR 函數(shù)，或者用 IFNA 函數(shù)來(lái)容錯(cuò)。?

• =IFNA(VLOOKUP(D2,A2:B7,2,0),"")?
  

?而 XLOOKUP 函數(shù)有它專屬的參數(shù)：它的第 4 個(gè)參數(shù)專門(mén)負(fù)責(zé)容錯(cuò)。

?=XLOOKUP（查找值，查找范圍，返回范圍，[容錯(cuò)]）

?這個(gè)參數(shù)是非必需參數(shù)，當(dāng)你碰到上面這種問(wèn)題，才有必要把它請(qǐng)出來(lái)。?公式就可以寫(xiě)成：?

• =XLOOKUP(D2,A2:A7,B2:B7,"")
  

?在查找范圍 A2:A7 中找 E2 單元格的值「孫二」，如果有找到就返回 B2:B7 對(duì)應(yīng)的值，如果沒(méi)有找到，則返回第 4 參數(shù)指定的內(nèi)容「""」。

?當(dāng)然第 4 參數(shù)的設(shè)定并非只可以是字符串，數(shù)值。也可以嵌套其它的公式返回結(jié)果。

XLOOKUP 函數(shù)的第 5 參數(shù)

如果根據(jù)得分評(píng)定等級(jí)，用 XLOOKUP 函數(shù)就能輕松解決

以下是評(píng)定的規(guī)則：

小于 60 分不合格；

大于等于 60 小于 70 為合格；

大于等于 70 小于 80 為良好；

大于等于 80 為優(yōu)秀。?

先為每個(gè)等級(jí)設(shè)置分?jǐn)?shù)的下限，如下圖 A 列，然后在 E2 單元格寫(xiě)入公式：?

• =XLOOKUP(D2,A2:A5,B2:B5,,-1)?
  

XLOOKUP 函數(shù)的第 5 參數(shù)是匹配類(lèi)型。

=XLOOKUP（查找值，查找范圍，返回范圍，[容錯(cuò)]，?[匹配類(lèi)型])?

當(dāng)?shù)?5 參數(shù)的值為-1 時(shí)，表示如果「查找值」沒(méi)有在「查找范圍」中，就返回下一個(gè)較小的值。

?如上面的公式中，D2 單元格的值是 75，沒(méi)有在查找區(qū)域 A2:A7 中，就找比 75 小的值，即 70。再返回 B2:B5 對(duì)應(yīng)的等級(jí)「良好」。?

如果把 A 列的分?jǐn)?shù)下限改成上限，公式就可以這樣寫(xiě)：

• =XLOOKUP(D2,A2:A5,B2:B5,,1)?
  

?當(dāng)?shù)?5 參數(shù)為 1 時(shí)，表示如果「查找值」沒(méi)有在「查找范圍」中，就返回下一個(gè)較大的值。?

如公式中，D2 單元格的值是 75，沒(méi)有在查找區(qū)域 A2:A7 中，就找比 75 大的值，即 79。再返回 B2:B5 對(duì)應(yīng)的等級(jí)「良好」。

關(guān)于 VLOOKUP、XLOOKUP 和 LOOKUP 有哪些區(qū)別，以下 Tips 供大家參考：

?? VLOOKUP 函數(shù)必須在查找區(qū)域的首列查找，而 XLOOKUP 函數(shù)不受這種位置限制；

? VLOOKUP 函數(shù)需要其它函數(shù)嵌套來(lái)容錯(cuò)，而 XLOOKUP 函數(shù)有自己的參數(shù)做容錯(cuò)處理，更方便；

? LOOKUP 函數(shù)在多值判斷時(shí)，需要升序排序，而 XLOOKUP 函數(shù)可以不用排序。

對(duì)了，如果你想系統(tǒng)性學(xué)習(xí) Excel。

正好，我們秋葉家的《秋葉 Excel 3 天集訓(xùn)營(yíng)》專為職場(chǎng)人準(zhǔn)備，全部基于職場(chǎng)真實(shí)表格案例設(shè)計(jì)，還有很多超實(shí)用 Excel 技巧教學(xué)。

每天學(xué)習(xí)大概?30 分鐘，從日常的功能出發(fā)，全程演示，一課一練，夯實(shí)進(jìn)階每一步。

秋葉 Excel 3 天集訓(xùn)營(yíng)

原價(jià)?99 元??

現(xiàn)在?只需 1?元?

每天學(xué)習(xí)?30 分鐘

你也有可能成為 Excel 高手！

現(xiàn)在就掃碼報(bào)名吧！

??????

關(guān)注我【秋葉 Excel】

還能免費(fèi)領(lǐng)

40+實(shí)用圖表和300+函數(shù)公式學(xué)習(xí)

還有不定期小禮物小福利，歡迎持續(xù)關(guān)注呀~

??

*廣告