LibreOffice Calc VLOOKUP vs XLOOKUP: A Beginner-Friendly Functions Guide

LibreOffice Calc gives you more than one way to find a value in a table and return related data. For years, VLOOKUP was the familiar choice. Today, Calc also includes XLOOKUP, a newer lookup function that removes several VLOOKUP limitations and is usually easier to maintain.

This guide starts with the basic mental model, then walks through practical formulas you can copy into a small spreadsheet. It also explains where INDEX/MATCH still fits, what can go wrong, and which function makes sense for a new workbook.

What should you know before using lookup functions?

A lookup searches one range for a key, such as a product ID, then returns a related value, such as the product price. The search key might be text, a number, a date, or a cell reference.

In current LibreOffice Help, XLOOKUP is documented as a modern replacement for VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP has been available since LibreOffice 24.8. The current LibreOffice 26.8 Help documents exact and approximate matching, wildcard and regular-expression matching, reverse search, and binary search. See the official LibreOffice XLOOKUP documentation.

One detail that often surprises people coming from Microsoft Excel: LibreOffice formula examples commonly use semicolons between arguments. For example, =XLOOKUP(F2;A2:A6;D2:D6). Calc may adapt separators to your locale, so follow the separator that your installation accepts.

VLOOKUP vs XLOOKUP at a glance

Prepare a small practice table

Create columns A through D with the headers Product ID, Product Name, Category, and Price. Add a few IDs such as P001 through P005. Put the ID you want to search for in F2 and place your formula in G2.

Formulas in Calc begin with an equals sign. You can type them directly into a cell or into the Formula Bar; the official LibreOffice formula-entry guide describes both methods.

Step 1: Use VLOOKUP for a basic exact match

Suppose P004 is in F2 and you want the price from column D. Enter:

=VLOOKUP(F2;$A$2:$D$6;4;0)

這些參數的意義是：在 F2 儲存格中尋找值，在 A2:D6 儲存格的第一列中尋找值，並傳回該儲存格第四列的值，並要求完全符合。美元符號表示絕對引用，這樣即使將公式複製到其他單元格，查找範圍也不會改變。

LibreOffice 文件中將 VLOOKUP 函數描述為垂直查找，其中查找值必須位於表格數組的第一列，則傳回值則來自其右側的列。文件還警告說，如果省略最後一個參數或將其設為 TRUE，則該函數假定資料已排序，並可能返回近似匹配結果。在依賴 VLOOKUP 函數的近似行為之前，請務必查閱LibreOffice 官方電子表格函數參考文件。

步驟 2：當傳回列位於左側時，使用 XLOOKUP 函數。

現在重新排列表格，使產品名稱位於 A 列，產品 ID 位於 C 列。使用 VLOOKUP 函數時，這種佈局不太方便，因為函數只會搜尋表格陣列的第一列，並將值傳回右側。

XLOOKUP 函數會將搜尋陣列與結果陣列分開。例如，要在 C 列中尋找 P003 並傳回 A 列中的產品名稱，請使用：

=XLOOKUP(F2;$C$2:$C$6;$A$2:$A$6;"Not found")

第四個參數是找不到匹配項時要傳回的值。這樣一來，公式就比為這種常見情況編寫單獨的錯誤處理函數來封裝查找操作更易於閱讀。

步驟 3：處理缺少的 ID，避免顯示 #N/A

如果使用者輸入的 ID 不存在，則普通的精確查找通常會傳回空值#N/A。在 XLOOKUP 函數中，將易於理解的查找結果放在第四個參數中：

=XLOOKUP(F2;$A$2:$A$6;$D$2:$D$6;"Not found";0)

匹配0模式明確要求精確匹配，儘管精確匹配已經是 XLOOKUP 函數的預設設定。如果您希望公式的意圖對審閱表格的人來說一目了然，那麼寫出精確匹配仍然很有用。

不要自動隱藏所有錯誤。如果某個 ID 必須始終存在，#N/A那麼它可能會暴露錯誤或不完整的來源數據，從而發揮其價值。當預期會出現缺失鍵時，應使用友善的回退機制；但如果缺失鍵顯示存在資料品質問題，則不應使用回退機制。

步驟 4：從底部開始搜索，取得最新的配對記錄

有時同一個鍵會出現多次。例如，交易日誌中可能包含客戶 C001 的多筆記錄。 XLOOKUP 函數有搜尋模式參數。此參數的值表示-1從最後一個元素開始，向第一個元素進行搜尋。

若要傳回 F2 中包含客戶 ID 的最後一行的金額，請使用：

=XLOOKUP(F2;$A$2:$A$8;$D$2:$D$8;"Not found";0;-1)

官方的 XLOOKUP 參考文件將搜尋模式定義1為從前到後和-1從後到前。它還提供了二分查找模式2，-2但這些模式要求資料已正確排序。對未排序的資料使用二分查找可能會傳回無效結果。

何時還應該使用 INDEX 和 MATCH 函數？

在 Calc 24.8 中引入 XLOOKUP 函數之前，一種常用的靈活替代方案是INDEX 函數加上 MATCH 函數。 MATCH 函數尋找鍵的相對位置；INDEX 函數傳回該位置的值。例如：

=INDEX($D$2:$D$6;MATCH(F2;$A$2:$A$6;0))

當您需要相容於舊版 LibreOffice 或現有工作簿已持續使用 INDEX/MATCH 函數時，此功能仍然非常有用。官方電子表格函數參考文件同時收錄了INDEX 和 MATCH 函數。

從 LibreOffice 24.8 開始，Calc 也提供了 XMATCH 函數。 XMATCH 函數增加了類似 XLOOKUP 的匹配和搜尋模式，包括反向搜尋。請參閱LibreOffice 官方 XMATCH 文件。

避免的常見錯誤

當需要精確匹配時，可以將 VLOOKUP 函數的最後一個參數留空。

使用 VLOOKUP 函數時，省略最後一個參數會啟用排序範圍匹配。對於產品 ID、員工編號、SKU 和其他離散標識符，0除非您的設計有意採用近似匹配，否則請使用 FALSE 或 NULL 來進行精確匹配。

計算錯誤的 VLOOKUP 欄位索引

VLOOKUP 函數的列索引是從表格數組的第一列開始計算的，而不是從電子表格的列字母開始計算的。在 A2:D6 單元格區域中，D 列的索引是 4。如果您之後在該表格中插入一列，則硬編碼的索引可能不再指向您想要尋找的欄位。

在 XLOOKUP 函數中使用不同長度的範圍

您的搜尋數組和結果數組應該逐行或逐列對齊。例如，將 A2:A100 與 D2:D90 這樣的搜尋範圍配對是設計錯誤，因為對應位置所涵蓋的記錄並不相同。

對未排序資料使用二分查找

XLOOKUP 搜尋模式 2 要求資料依升序排列，而 -2 要求資料依降序排列。 LibreOffice 文件明確警告，未排序的資料可能會產生無效結果。如果您無法控制排序，請使用常規的正向或反向搜尋模式。

混淆通配符和正規表示式

Calc 支援通配符匹配和正規表示式匹配，但它們是 XLOOKUP 函數中兩種不同的匹配模式。通配符模式使用諸如 `\ *` 和 `\` 之類的字元?；正規表示式模式則使用 Calc 的正規表示式引擎。請務必謹慎選擇匹配模式，不要想當然地認為程式會自動解析匹配模式。

初學者該選擇哪一種查找函數？

對於在 LibreOffice 24.8 或更高版本上執行的新電子表格，建議從 XLOOKUP 函數開始。它獨立的搜尋範圍和結果範圍更易於閱讀，預設為精確匹配，左側返回值也更直觀，並且內建的未找到匹配項和反向搜尋選項減少了使用公式變通方法的需要。

當您維護一個基於 VLOOKUP 函數構建的現有工作簿、與 XLOOKUP 函數支援不確定的環境共享數據，或處理一個簡單穩定的表格且其局限性無關緊要時，請使用 VLOOKUP 函數。當需要相容舊版 LibreOffice 或工作簿本身已遵循相關模式時，請使用 INDEX/MATCH 函數。

最重要的習慣不是死記硬背每個參數。相反，在寫公式之前，先明確四個問題：我要找的值是什麼？我要找的範圍是什麼？答案應該在哪個範圍內？如果找不到對應的鍵該怎麼辦？一旦這些問題都清楚了，在 VLOOKUP、XLOOKUP 和 INDEX/MATCH 函數之間進行選擇就容易多了。