XLOOKUP 入門：用品名查價格，也對照 VLOOKUP

今天要來聊聊 XLOOKUP，用一個值去表格裡找它對應的另一個值，比如拿品名去查價格。

如果你以前學過 VLOOKUP，文章後面也會把兩個函式的寫法放在一起對照。

手動查有多麻煩

我手上有一張手搖飲的菜單，一共 5 個品項，有編號、品名跟單價：

我想知道「烏龍奶茶」賣多少錢。5 列的話眼睛掃一下就找到了，不過如果菜單有幾百列、每天都有人來問不同的品項，一個一個用 Ctrl+F 找就有點太麻煩了吧！這時候就可以把 XLOOKUP 派上場。

XLOOKUP

XLOOKUP 會在一個範圍裡找你給的值，找到之後，回傳另一個範圍中同一個位置的值。

語法

=XLOOKUP(查找值, 查找範圍, 結果範圍, [找不到時的值], [比對模式], [搜尋模式])

• 查找值：你手上有的那個值，例如「烏龍奶茶」，可以直接打，也可以指定儲存格。
• 查找範圍：要去哪裡找這個值。官方說明寫的是必須是單列或單欄。
• 結果範圍：找到之後，要從哪個範圍把值帶回來。大小要和查找範圍對得起來，位置才會一一對應。
• 找不到時的值：選填，查不到的時候要顯示什麼。不填的話會是 #N/A。
• 比對模式、搜尋模式：選填，這篇先不用，放著不填就是預設的「完全一樣才算找到、從上往下找」。

最後兩個選填參數可以做的事很多，也有幾個容易踩到的地方，我留到 XLOOKUP 進階陷阱 再講。

如果你的檔案之後還要拿到 Excel 用：Excel 的 XLOOKUP 只有 Microsoft 365、Excel 2021、Excel 2024 才有，Excel 2016、2019 沒有這個函式，會顯示 #NAME?。參數的順序和 Google 試算表一樣，第四個參數（Excel 名稱為 if_not_found）省略時同樣回 #N/A。

第一個查找

我想找「烏龍奶茶」的單價，把品名打在 E2。查找範圍是品名那一欄 B2:B6，結果範圍是單價那一欄 C2:C6。

如果要用 XLOOKUP 寫，就是：

• E2：查找值，裡面是「烏龍奶茶」。
• B2:B6：查找範圍，去品名欄找。
• C2:C6：結果範圍，找到之後帶單價欄對應的那一格回來。

先手算一下：「烏龍奶茶」在 B 欄的第 2 個位置，那就拿單價欄的第 2 個，是 55。來看看結果：

你應該會得到 55！把 E2 換成別的品名，單價就會跟著換。

往左也能查

我再想一個題目：知道品名，想反過來查編號。編號在 A 欄，品名在 B 欄，也就是結果在查找範圍的「左邊」。

只要把結果範圍換成 A2:A6 就好，E2 一樣是「烏龍奶茶」，來看看結果：

找不到的時候

如果 E2 打了菜單裡沒有的「珍珠奶茶」，XLOOKUP 會回 #N/A。自己看是沒關係，要給別人用的話，一整片紅紅的錯誤會讓人以為壞掉了。

這時候就用到第四個參數：

第四個參數填文字要加雙引號，填數字就不用。官方說明沒有特別限制它能填什麼，我習慣填一句人看得懂的話，這樣別人一看就知道是「查不到」，不是公式壞了。

對照 VLOOKUP

先把 E2 從上一節的「珍珠奶茶」改回「烏龍奶茶」，確認查找結果是 55；下面兩種函式比較都使用這個查找值。

VLOOKUP 是比較早就有的查找函式，舊檔案裡很常看到，所以還是要讀得懂。

語法

=VLOOKUP(查找值, 範圍, 欄號, [是否排序])

• 查找值：要找的值。它必須在「範圍」的第一欄，不然拿不到正確的結果。
• 範圍：要搜尋的區域，VLOOKUP 只會搜尋這個範圍的第一欄。
• 欄號：結果在「範圍」裡的第幾欄，從 1 開始數，是範圍內的第幾欄，不是試算表的 A、B、C 欄。
• 是否排序：選填。FALSE 是完全一樣才算找到，TRUE 是近似比對。官方說明寫的是不填就當作 TRUE，同一頁也建議用 FALSE。

同樣的題目，用 VLOOKUP 寫：

• E2：「烏龍奶茶」。
• B2:C6：範圍。品名要放在第一欄，所以從 B 欄開始框。
• 2：單價是 B2:C6 的第 2 欄。
• FALSE：要完全一樣才算找到。

結果一樣是 55。兩個函式放在一起看，差別是這樣：

我個人現在新寫的都是 XLOOKUP，不用數欄號，插入一欄也不會讓欄號跑掉。不過接手別人的檔案時，VLOOKUP 還是會遇到，看得懂就夠了，不需要特地全部改掉。

練習

小測驗

想知道不是完全一樣的比對、從最後一筆往回找，或是一次帶回好幾欄，可以接著看 XLOOKUP 進階陷阱；要兩個條件同時符合才查，看 XLOOKUP 兩個條件一起找。想看實際的任務，像是訂單補價格、兩張表對照、查不到的處理，可以看 訂單補價格與兩張表對照。

我是喜特先生，Mr. Sheet，我們下個教學見！