計算 VLOOKUP 與 XLOOKUP：選擇正確的查找公式

如果您的工作簿將在 LibreOffice 24.8 或更高版本中打開，大多數新的查找操作請使用 XLOOKUP 函數；它預設進行精確匹配，並允許您選擇單獨的搜尋範圍和結果範圍。當需要與舊版 Calc 或查找值相容於緊湊型表格的第一列時，請使用 VLOOKUP 函數。如果使用 VLOOKUP 函數，請將其第四個參數設為 0 或 FALSE 以進行精確匹配。

這些函數解決的是同一個基本問題：尋找某個鍵值（例如產品代碼），並從另一個儲存格傳回相關資訊。它們的主要差異在於：每個公式定義其範圍的方式不同，省略某個選項時所採用的假設，以及該檔案是否必須相容於舊版軟體。

實際差異是什麼？

VLOOKUP 函數在矩形表格的第一列向下查找，並傳回其右側編號列中的值。 XLOOKUP 函數將搜尋範圍和返回範圍分開，因此傳回值可以位於鍵的兩側。 XLOOKUP 函數也接受自訂的缺失匹配值，並且可以反向搜尋。

例如，假設 A2:A5 儲存格包含產品 ID，B2:B5 儲存格包含價格。 F2 儲存格包含要尋找的 ID。在 Calc 中，VLOOKUP 函數的精確匹配公式為：

=VLOOKUP(F2;A2:B5;2;0)

XLOOKUP 函數的等效公式為：

=XLOOKUP(F2;A2:A5;B2:B5;"Not found")

兩者都返回匹配的價格。 Calc 通常會根據區域設置，在函數參數之間顯示分號；如果您的安裝中公式使用逗號分隔符，請使用 Calc 期望的分隔符。範例使用單一單元格結果和一維查找範圍。

何時應該選擇 XLOOKUP 函數？

如果您知道收件者使用的是 LibreOffice 24.8 或更高版本，請在新工作簿時選擇 XLOOKUP 函數。根據XLOOKUP 函數的官方說明頁面，該函數已在 LibreOffice 24.8 中提供。其預設匹配模式為精確匹配，因此如果缺少精確匹配的鍵，它不會自動傳回近似值。

此函數模式為=XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode])：。前三個參數分別指定鍵、要搜尋的一維範圍、返回範圍。第四個參數（例如）用於"Not found"替換通常的 #N/A 結果（當沒有匹配項時）。

傳回左側列中的值。

VLOOKUP 函數無法傳回其第一列左側的值。 XLOOKUP 函數可以，因為它的搜尋範圍和結果範圍是獨立的。例如，如果 ID 位於 C2:C100 儲存格區域，則部門名稱位於 A2:A100 儲存格區域，則可以使用：

=XLOOKUP(F2;C2:C100;A2:A100;"ID not found")

這種佈局還能避免脆弱的列索引。如果有人在 VLOOKUP 表中插入一列，硬編碼的索引可能會指向不同的欄位。 XLOOKUP 的明確結果範圍使得檢查預期傳回的欄位更加容易。

傳回最近符合的條目

當存在重複鍵且需要尋找最後一個符合項目時，XLOOKUP 函數的搜尋模式支援反向搜尋。對於垂直範圍，最後一個參數表示-1從最後一個匹配項開始搜索，直到第一個匹配項為止：

=XLOOKUP(F2;A2:A100;B2:B100;"Not found";0;-1)

此操作傳回與最後一次完全符合結果關聯的資料。僅當行順序具有明確含義時才使用此操作，例如最新記錄顯示在最後。如果應將重複項視為錯誤，則應清理或驗證數據，而不是隱式地選擇一個匹配項。

VLOOKUP 函數何時仍然是更好的選擇？

如果檔案必須在不包含 XLOOKUP 的舊版 LibreOffice 中開啟，或者目標應用程式可能無法辨識 XLOOKUP，則應保留 VLOOKUP 函式。官方 Calc 文件介紹了 VLOOKUP 的表格參數、索引參數及其可選的排序範圍參數；請參閱官方電子表格函數參考文件。

對於精確匹配，模式為 `<name> =VLOOKUP(key;table;column_index;0)`。索引從所選表格的左側邊緣開始計數列：1 表示第一列，2 表示第二列。查找鍵必須位於第一列，傳回的欄位必須位於其右側。

第四個參數至關重要。如果省略此參數或將其設為 TRUE，VLOOKUP 函數將使用近似匹配，並要求第一列按升序排列。這種行為對於諸如稅級之類的範圍非常有用，因為其中每一行都代表一個下限。但對於 ID、姓名和未排序的列表，這種做法有風險。對於這些類型，請指定 0 或 FALSE。

匹配和錯誤行為有何不同？

精確匹配： XLOOKUP 預設使用精確匹配。 VLOOKUP 要求第四個參數設定為 0 或 FALSE 才能保證精確查找。
缺少關鍵資訊： XLOOKUP 函數可以在其第四個參數中傳回選定的訊息或值。 VLOOKUP 函數通常傳回 #N/A；如果需要自訂訊息，請使用 IFNA 將其括起來。
近似匹配：兩者都支援基於範圍的匹配，但資料和所選模式必須符合預期規則。根據需要對資料進行排序並驗證邊界情況。
搜尋方向： XLOOKUP 函數可以透過其搜尋模式參數從下往上搜尋。 VLOOKUP 函數在通常的自上而下使用時，傳回第一個匹配的行。

若要為 VLOOKUP 函數提供自訂的缺失鍵響應，請使用 ` =IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found").`。這樣可以保持精確匹配設定可見，並且僅處理不可用錯誤。除非您還想隱藏其他無關問題（例如無效的範圍參考），否則請避免將公式包裝在通用的錯誤處理程序中。

分享工作簿前應該檢查哪些內容？

LibreOffice 的說明文件指出，XLOOKUP 函數自 24.8 版本起可用。文件中還指出，XLOOKUP 函數並非 OpenDocument 1.3 版 OpenFormula 標準的一部分，並指定了 Microsoft 函數命名空間以實現互通性。這表示僅憑文件格式無法保證所有舊版或第三方電子表格應用程式都能計算函數。對於混合軟體環境，請在目標應用程式中測試工作簿，或使用佈局適當的成熟公式，例如精確匹配的 VLOOKUP 函數。

另請注意一個已記錄的陣列公式條件：當 XLOOKUP 函數的結果陣列是一個儲存格區域時，必須將該函數輸入為陣列公式。上面的簡單範例傳回單一對應值。如果您希望公式傳回多個儲存格，請查閱特定版本的說明文檔，並測試目標應用程式如何處理陣列結果。