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セルの最初の列を検索し、その表の4列目の値を返し、完全一致を要求します。ドル記号は絶対参照を作成するため、数式を他のセルにコピーしても検索範囲は固定されます。

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")

4番目の引数は、一致する値が存在しない場合に返される値です。この一般的なケースでは、検索を別のエラー処理関数で囲むよりも、この引数を使うことで数式が読みやすくなります。

ステップ3：#N/Aを表示せずに、欠落しているIDを処理する

ユーザーが存在しないIDを入力した場合、通常の完全一致検索ではエラーが発生します#N/A。XLOOKUPでは、4番目の引数に分かりやすい結果を指定します。

=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の両方が記載されています。

Calc は、LibreOffice 24.8 以降で XMATCH 機能を提供しています。XMATCH は、XLOOKUP と同様のマッチングおよび検索モードを追加し、逆検索も可能です。詳細については、LibreOffice の公式XMATCH ドキュメントを参照してください。

避けるべきよくある間違い

完全一致が必要な場合、VLOOKUP関数の最後の引数を空欄にしてください。

VLOOKUP関数では、最後の引数を省略すると、ソートされた範囲での動作になります。製品ID、従業員番号、SKU、その他の個別の識別子については、0近似一致が意図的に設計に含まれている場合を除き、完全一致の場合はFALSEを使用してください。

VLOOKUP列のインデックスが間違っている

VLOOKUP関数の列インデックスは、スプレッドシートの列文字ではなく、テーブル配列の最初の列からカウントされます。A2:D6では、D列のインデックスは4です。後でそのテーブル内に列を挿入すると、ハードコードされたインデックスが意図したフィールドを参照しなくなる可能性があります。

XLOOKUPで長さの異なる範囲を使用する

検索範囲と結果範囲は、行単位または列単位で一致している必要があります。A2:A100 と D2:D90 のような検索範囲の組み合わせは、対応する位置が同じレコードをカバーしていないため、設計上の誤りです。

ソートされていないデータに対して二分探索を使用する

XLOOKUPの検索モード2は昇順を、-2は降順を想定しています。LibreOfficeのドキュメントには、ソートされていないデータでは無効な結果が生じる可能性があると明記されています。ソート順を制御できない場合は、通常の順方向または逆方向の検索モードを使用してください。

ワイルドカードと正規表現の混同

Calcはワイルドカードと正規表現によるマッチングをサポートしていますが、XLOOKUP関数ではこれらは別々のマッチングモードです。ワイルドカードモードでは、` a`や*`b`などの文字を使用します?。正規表現モードでは、Calcの正規表現エンジンを使用します。パターンが自動的に解釈されると想定するのではなく、モードを意図的に選択してください。

初心者はどの検索関数を選択すべきでしょうか？

LibreOffice 24.8以降で新しいスプレッドシートを作成する場合は、XLOOKUP関数から始めることをお勧めします。検索範囲と結果範囲が分かれているため読みやすく、完全一致がデフォルト設定になっており、左側の戻り値も簡単に指定できます。また、組み込みの「見つからない」オプションと逆引き検索オプションにより、数式による回避策の必要性が軽減されます。

VLOOKUP 関数は、既存のワークブックを VLOOKUP 関数に基づいて構築している場合、XLOOKUP 関数のサポートが不確かな環境と共有する場合、または制限が問題にならない単純な安定したテーブルを扱う場合に使用します。古い LibreOffice との互換性が重要な場合、またはワークブックが既にそのパターンに従っている場合は、INDEX/MATCH 関数を使用します。

最も重要な習慣は、すべての引数を暗記しないことです。代わりに、数式を書く前に、次の4つの点を明確にしましょう。検索する値は何か、検索範囲はどこか、結果の範囲はどこか、そしてキーが見つからない場合はどうするか。これらが明確になれば、VLOOKUP、XLOOKUP、INDEX/MATCHの中から最適なものを選ぶのがずっと簡単になります。