|
原文標題：《比 Vlookup 更逆天！這個強大的查詢函數，看完我就跪了！》 我們今天來講講 XLOOKUP，一個強大的查詢函數！ 在說 XLOOKUP 函數之前，咱們先看看下面這個案例，根據姓名查找對應的性別。
這個問題太常見了！首先想到的就是用 VLOOKUP 函數。 =VLOOKUP(E2,A2:C7,2,0) 在查找區域 A2:C7 的首列找到 E2 單元格的值「王五」，返回查找區域 A2:C7 第 2 列與之對應的值「男」。
初識 XLOOKUP 函數再來看看 XLOOKUP 函數的用法： =XLOOKUP（查找的值，查找范圍，結果范圍） 公式就可以這樣寫： =XLOOKUP(E2,A2:A7,B2:B7) 在查找范圍 A2:A7 中找到 E2 單元格的值「王五」，返回 B2:B7 對應的值「男」。
如果姓名這列不是在前面，用 VLOOKUP 函數似乎就不太合適了。因為 VLOOKUP 函數的規則是在查找區域的首列查找。 這種情況下，我們通常會用 INDEX 函數和 MATCH 函數組合寫公式： =INDEX(A2:A7,MATCH(E2,B2:B7,0)) MATCH 函數找出 E2 的值「王五」在 B2:B7 中是第幾行，得到結果 3，然后用 INDEX 函數將 A2:A7 的第 3 行的值引用出來，得到結果「男」。
而 XLOOKUP 函數就不一樣了，它不會像 VLOOKUP 那樣受位置的影響，依然照用不誤： =XLOOKUP(E2,B2:B7,A2:A7) 在查找區域 B2:B7 中找到 E2 單元格的值「王五」，返回 A2:A7 對應的值「男」。
XLOOKUP 函數的第 4 參數經常有小伙伴提這樣的問題，如何讓 VLOOKUP 查找不到的數據返回為空值？ 如下圖，VLOOKUP 函數在查找區域 A2:B7 的首列沒有找到單元格的值「孫二」, 就會返回錯誤值#N / A。 =VLOOKUP(D2,A2:B7,2,0)
通常我們都會在 VLOOKUP 函數外層嵌套 IFERROR 函數，或者用 IFNA 函數來容錯。 =IFNA(VLOOKUP(D2,A2:B7,2,0),'')
而 XLOOKUP 函數有它專屬的參數：它的第 4 個參數專門負責容錯。 =XLOOKUP（查找值，查找范圍，返回范圍，[容錯]） 這個參數是非必需參數，當你碰到上面這種問題，才有必要把它請出來。 公式就可以寫成： =XLOOKUP(D2,A2:A7,B2:B7,'')
在查找范圍 A2:A7 中找 E2 單元格的值「孫二」，如果有找到就返回 B2:B7 對應的值，如果沒有找到，則返回第 4 參數指定的內容「''」。 當然第 4 參數的設定并非只可以是字符串，數值。也可以嵌套其它的公式返回結果。
XLOOKUP 函數的第 5 參數以下是評定的規則： 小于 60 分不合格； 大于等于 60 小于 70 為合格； 大于等于 70 小于 80 為良好； 大于等于 80 為優秀。 先為每個等級設置分數的下限，如下圖 A 列，然后在 E2 單元格寫入公式： =XLOOKUP(D2,A2:A5,B2:B5,,-1)
XLOOKUP 函數的第 5 參數是匹配類型。 =XLOOKUP（查找值，查找范圍，返回范圍，[容錯]， [匹配類型]) 當第 5 參數的值為-1 時，表示如果「查找值」沒有在「查找范圍」中，就返回下一個較小的值。 如上面的公式中，D2 單元格的值是 75，沒有在查找區域 A2:A7 中，就找比 75 小的值，即 70。再返回 B2:B5 對應的等級「良好」。 如果把 A 列的分數下限改成上限，公式就可以這樣寫： =XLOOKUP(D2,A2:A5,B2:B5,,1)
當第 5 參數為 1 時，表示如果「查找值」沒有在「查找范圍」中，就返回下一個較大的值。 如公式中，D2 單元格的值是 75，沒有在查找區域 A2:A7 中，就找比 75 大的值，即 79。再返回 B2:B5 對應的等級「良好」。
寫到最后關于 VLOOKUP、XLOOKUP 和 LOOKUP 有哪些區別，以下 Tips 供大家參考： ? VLOOKUP 函數必須在查找區域的首列查找，而 XLOOKUP 函數不受這種位置限制； ? VLOOKUP 函數需要其它函數嵌套來容錯，而 XLOOKUP 函數有自己的參數做容錯處理，更方便； ? LOOKUP 函數在多值判斷時，需要升序排序，而 XLOOKUP 函數可以不用排序。 本文來自微信公眾號：秋葉 Excel （ID：excel100），作者：趙驕陽，編輯：竺蘭 |
|
|
