【Googleスプレッドシート】XLOOKUP関数の基本と使い方!VLOOKUPより便利な検索の手順

Googleスプレッドシートでデータを検索するとき、VLOOKUP関数を使っている方は多いでしょう。しかし、VLOOKUPには左方向への検索ができないなど、いくつかの制限があります。XLOOKUP関数は、そうした制限を解消し、より柔軟な検索を可能にする新しい関数です。この記事では、XLOOKUP関数の基本的な使い方と、VLOOKUPより便利な点を具体例を交えて解説します。

XLOOKUP関数が登場した背景とVLOOKUPとの違い

XLOOKUP関数は、従来のVLOOKUPやHLOOKUP、INDEX/MATCHを置き換える目的で2020年にGoogleスプレッドシートに導入されました。VLOOKUPでは、検索値が左端になければならず、列の追加や削除で数式が崩れる問題がありました。また、右方向の検索しかできず、左方向を検索するにはINDEX/MATCHを組み合わせる必要がありました。XLOOKUPはこれらの制限をすべて解消し、1つの関数で柔軟な検索を実現します。さらに、エラー処理や一致モードの指定が引数に含まれているため、数式がシンプルで保守しやすくなっています。

XLOOKUP関数の基本的な使用方法

まずは基本的な構文を確認しましょう。XLOOKUP関数の引数は以下の通りです。

1. 検索値: 検索したい値を指定します。
2. 検索範囲: 検索値を探す範囲を指定します。
3. 戻り範囲: 一致した行から取得したい値の範囲を指定します。
4. 見つからない場合の値(省略可): 検索値が見つからなかったときに表示する値を指定します。
5. 一致モード(省略可): 0で完全一致、1で近似値(次に大きい)、-1で近似値(次に小さい)、2でワイルドカード一致を指定します。
6. 検索モード(省略可): 1で先頭から検索、-1で末尾から検索、2でバイナリ検索(昇順)、-2でバイナリ検索(降順)を指定します。

それでは、具体的な手順を見ていきましょう。サンプルデータとして、社員リストから社員番号をキーに名前を取得する例を使います。

1. データを準備する: スプレッドシートに社員番号と名前のリストを用意します。社員番号はD列、名前はE列とします。
2. XLOOKUP関数を入力する: 結果を表示したいセル(例:B2)に「=XLOOKUP(」と入力します。
3. 引数を指定する: 検索値として社員番号のセル(例:A2)、検索範囲として社員番号の列(例:$D$2:$D$100)、戻り範囲として名前の列(例:$E$2:$E$100)を指定します。絶対参照($)を使うと数式をコピーしたときに範囲がずれません。
4. 数式を完成させる: 「)」で閉じてEnterキーを押します。数式は「=XLOOKUP(A2, $D$2:$D$100, $E$2:$E$100)」のようになります。

これで、社員番号に対応する名前が表示されます。VLOOKUPと違い、検索範囲と戻り範囲が別々に指定できるため、左方向の検索も簡単に行えます。例えば、名前から社員番号を検索する場合、検索範囲に名前の列、戻り範囲に社員番号の列を指定するだけです。

エラー処理を追加する方法

1. 第4引数を追加する: 「=XLOOKUP(A2, $D$2:$D$100, $E$2:$E$100, “該当なし”)」とします。
2. 効果を確認する: 社員番号が見つからない場合、「該当なし」と表示されます。IFERROR関数を使う必要がありません。

部分一致(ワイルドカード)検索を行う方法

1. 第5引数に2を指定する: 検索値にワイルドカード文字を含めます。例えば、「=XLOOKUP(“*山*”, $D$2:$D$100, $E$2:$E$100, , 2)」とします。
2. 注意点: ワイルドカード文字として、*は任意の文字列、?は任意の1文字を表します。完全一致と異なる動作を理解しましょう。

最後の一致を取得する方法

1. 第6引数に-1を指定する: 「=XLOOKUP(A2, $D$2:$D$100, $E$2:$E$100, , , -1)」とします。
2. 使用例: 同じ社員番号が複数ある場合、最後の行のデータを取得します。売上データなどで最終レコードを取得したい場合に便利です。

複数条件を組み合わせた検索

XLOOKUP単体では複数条件の検索はできませんが、他の関数と組み合わせることで実現できます。例えば、検索値を「&」で結合したキーを作成し、検索範囲も同様に結合列を用意します。

1. ヘルパー列を作成する: C列に「=A2&B2」のようにキーを結合した列を追加します。
2. XLOOKUPで検索する: 検索値も「=検索値1&検索値2」と結合して指定します。例:「=XLOOKUP(F2&G2, $C$2:$C$100, $D$2:$D$100)」

XLOOKUP関数使用時の注意点

XLOOKUP関数は便利ですが、いくつか注意すべき点があります。

検索範囲と戻り範囲のサイズを一致させる

検索範囲と戻り範囲の行数(または列数)が異なるとエラーになります。必ず同じ数の行または列を指定してください。

ワイルドカード検索の挙動

第5引数に2を指定する場合、検索値にワイルドカード文字を含める必要があります。また、完全一致(0)と混同しないように注意しましょう。ワイルドカードを使用する場合は、意図しない部分一致が発生しないか確認が必要です。

バイナリ検索の条件

第6引数に2または-2を指定する場合、検索範囲が昇順または降順にソートされている必要があります。ソートされていないと誤った結果が返る可能性があります。

他の関数との互換性

XLOOKUPはGoogleスプレッドシートだけでなくMicrosoft Excelでも使用できますが、古いバージョン(Excel 2019以前)では利用できません。共同作業者とスプレッドシートを共有する際は、相手の環境を確認することをお勧めします。

XLOOKUP関数とVLOOKUP関数の違い

機能	VLOOKUP	XLOOKUP
検索方向	左から右のみ	左・右・上下任意
戻り列の指定	列番号(数値)	範囲を直接指定
エラー処理	IFERROR関数と組み合わせ	第4引数で標準対応
ワイルドカード	検索値に直接指定	第5引数でモード指定
複数一致	最初の一致のみ	検索モードで最初/最後を選択
近似一致	TRUE/FALSE	1/-1/2など詳細指定
列の追加・削除の影響	列番号がずれる	範囲指定のため影響を受けにくい

このように、XLOOKUPはVLOOKUPと比較して多くの点で優れています。特に列の追加・削除に対する堅牢性は、長期運用するシートで大きなメリットです。

XLOOKUP関数を使えば、VLOOKUPの制限を気にせずに柔軟な検索ができます。この記事で紹介した基本構文を押さえれば、日常のデータ検索作業が大幅に効率化するでしょう。特にエラー処理や方向の自由度は大きなメリットです。ぜひ実際のシートで試してみて、INDEX/MATCHやVLOOKUPからの移行を検討してみてください。