XLOOKUP関数とは？使い方やVLOOKUP関数との違いを解説！

VLOOKUP関数とHLOOKUP関数の上位互換である、エクセルの「XLOOKUP関数」がついにGoogleスプレッドシートにも実装されました。今回はXLOOKUP関数の基本的な使い方やオプション要素、VLOOKUP関数との違いを分りやすく解説します。

※XLOOKUP関数は Excel 2019以前のバージョンでは使えません。ただし無料で利用できるweb版エクセルにも実装されているので、誰でも練習できます。>>web版エクセルの使い方

XLOOKUP関数の使い方

エクセルとGoogleスプレッドシートで、XLOOKUP関数の動作に特に違いはありません。基本的な構文や使い方をご紹介するので、ぜひ参考にしてみてください。

XLOOKUP関数は「検索範囲」の中から「検索キー」と一致するセルを検索し、「結果の範囲」から対応する行または列の値を返します。

例えば検索キーが「C」なら、検索範囲(A2:A6)の中から「C」と一致するセルを検索します。

返す値は「結果の範囲」からなので、抽出されるのは「10」です。

XLOOKUP関数の必須要素：(検索キー, 検索範囲, 結果の範囲)

VLOOKUP関数との最大の違いは、結果を範囲で指定できることです。VLOOKUP関数では「結果を指数(列)で選択」するため、1つの結果しか得られませんでした。

XLOOKUP関数は結果を範囲で指定するため、一度に複数の結果を得られます。

VLOOKUP関数の構成要素：(検索キー, 範囲, 列番号)
範囲の1列目の中から指定したキーを検索し、該当の行と列番号がクロスする値を返します。

また従来HLOOKUP関数でしか出来なかった、水平方向の検索も可能です。

以上のように結果を範囲で指定することにより、XLOOKUP関数はVLOOKUP関数とHLOOKUP関数の両方の機能を兼ね備えた関数となっています。

HLOOKUP関数の構成要素：(検索キー, 範囲, 行番号)
範囲の1行目の中から指定したキーを検索し、該当の列と行番号がクロスする値を返します。

4番目以降の要素はオプションのような役割があり、すべて省略可能です。要素④~⑥の役割については、後ほど詳しく解説します。

XLOOKUP関数の構成要素：(①検索キー, ②検索範囲, ③結果の範囲, ④見つからない場合の値, ⑤一致モード, ⑥検索モード)

XLOOKUP関数の省略可能な要素④[見つからない場合の値]、⑤ [一致モード]、⑥[検索モード]について、使い方や省略した場合の動作について解説します。

4番目の要素(見つからない場合の値)は、検索範囲に検索キーと一致するものがない場合の値です。

例えば見つからない場合に「”該当データなし”」をセットし、キー「F」で検索してみましょう。Fは検索範囲に存在しないため、XLOOKUP関数は「該当データなし」を返します。

省略すると「#N/A」を返します。

5番目の要素(一致モード)は、一致とみなす基準です。

例えば一致モードを「-1」、検索キーを「F」としましょう。検索範囲には「F」がないので、一致モード「-1」は検索キー「F」より小さい次の値「E」を一致とみなします。

省略すると「0」と認識され、完全一致するデータを検索します。

6番目の要素(検索モード)は、検索範囲を検索する方法です。

例えば検索モードを「-1」、検索値を「B」としましょう。検索モード「-1」は末尾から先頭に向かって検索するため、「2つのB」のうち「5行目のB」を一致とします。

バイナリ検索とは、二分探索法のことです。まず中央値と検索キーを比較し、検索範囲を2分割しながら検索していくので、データ量が多いときに処理を軽くできます。