指定した範囲の中から特定のデータに対応する値を取り出す関数であるVLOOKUP関数。非常に便利な関数のため使っている方も多いと思います。一方で、VLOOKUP関数を使っていてこんなことを思ったことはありませんか?
・検索値より左側の列も出力できたらいいのにな。。
・複数の検索値を適用できたらいいのにな。。
本記事では、そんなVLOOKUP関数で解決できないお悩みを解決できる便利な関数、XLOOKUP関数の特徴と使い方について紹介します!
VLOOKUP関数とXLOOKUP関数の違い
まずはそれぞれの関数の書き方について見ていきたいと思います。
=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])なんとなく構成要素は似ていますが、XLOOKUP関数の方が引数が多いですね。
続けて機能面の比較をしていきます。VLOOKUP関数とXLOOKUP関数には大きく分けて下記のような違いがあります。
| 比較項目 | VLOOKUP関数 | XLOOKUP関数 |
|---|---|---|
| 検索値の位置 | 検索範囲の一番左でないと× | 任意の位置を設定可能 |
| 検索方向 | 検索値が縦(列)方向に並んでいる場合のみ利用可能 | 検索値が縦(列)・横(行)のいずれの方向に並んでいても利用可能 |
| 完全一致の検索 | [検索方法]にFALSEの指定が必要 | 既定で完全一致(指定を省略できる) |
| エラー発生時 | 代替値の設定不可 | 代替値の設定が可能 |
| 列の挿入・削除 | 列番号を数字で指定するため、範囲内に列を挿入・削除すると列番号の修正が必要 | 戻り範囲を直接指定するため、列を挿入・削除しても修正不要 |
| 利用可能なバージョン | すべてのバージョンで利用可能 | Excel 2019以前のバージョンでは利用不可 |
それでは各項目について詳しく解説していきます。
検索値の位置
<VLOOKUP関数の場合>
VLOOKUP関数を利用する場合、下記の画像のように検索値として利用する列が対象範囲の一番左側に存在する必要があります。検索値の列が左端以外にある場合は、列を追加したり位置を修正するといった対応が必要になります。

<XLOOKUP関数の場合>
XLOOKUP関数はVLOOKUP関数と異なり、検索範囲と戻り範囲(出力範囲)を別々の引数として設定する関数になっています。(VLOOKUP関数は、『範囲』の引数の中に検索範囲と戻り範囲を含める必要があります。)
そのため、検索値が一番左側の列でないといけないといった制約はなく、検索範囲より左側の列も戻り範囲として設定することが可能です。
上記の通り、出力範囲の柔軟性ではXLOOKUP関数はVLOOKUP関数に勝る関数であると言えそうです。
検索方向
<VLOOKUP関数の場合>
VLOOKUP関数を利用する場合は、検索値が縦(列)方向に並んでいる場合しか利用ができません。
<XLOOKUP関数の場合>
XLOOKUP関数は検索値が縦(列)方向でも横(行)方向でも利用することが可能です。
月ごとの集計表のように項目が横(行)方向に並んだ表でも同じ関数で検索できるため、検索方向の柔軟性においてもXLOOKUP関数はVLOOKUP関数に勝る関数であると言えそうです。
完全一致の検索
<VLOOKUP関数の場合>
VLOOKUP関数で完全一致検索をしたい場合、[検索方法]の引数に「FALSE」を明示的に指定する必要があります。この指定を忘れると、意図せず近似一致で検索されてしまい、誤った値が返ってくることがあります。
<XLOOKUP関数の場合>
XLOOKUP関数は既定の設定が完全一致になっているため、[一致モード]の引数を省略しても自動的に完全一致で検索してくれます。VLOOKUPで起こりがちな「FALSEの指定忘れ」によるミスを防げるのも、地味ながら嬉しいポイントです。
エラー発生時
<VLOOKUP関数の場合>
VLOOKUP関数を利用する場合、出力結果がエラーになった場合は、『#N/A』や『#REF!』といったエラー表示が出力されてしまいます。エラー表示を無くすためには、IFERROR関数といった別の関数を組み合わせた入力が必要になります。
<XLOOKUP関数の場合>
XLOOKUP関数については、[見つからない場合]の引数に表示させたい文言を入力することで、エラー発生時の出力結果を入力した文言に変えることができます。
エラー発生時の表示まで、一つの関数でケアできるという点でもXLOOKUP関数はVLOOKUP関数より使いやすい関数だと言えそうです。
列の挿入・削除
<VLOOKUP関数の場合>
VLOOKUP関数は、取り出す列を「範囲の左から何列目か」という数字(列番号)で指定します。そのため、範囲の中に列を挿入・削除したときは、列番号も自分で直す必要があります。
例えば「=VLOOKUP(“A103”,A2:D5,4,FALSE)」で4列目の価格を取り出している表に、C列を1列挿入したとします。範囲は自動でA2:E5に広がりますが、列番号は4のままなので、価格ではなく、挿入前にC列にあった項目の値が返ってきてしまいます。
<XLOOKUP関数の場合>
XLOOKUP関数は戻り範囲をセル範囲で直接指定するため、列を挿入・削除しても参照先が自動で調整され、正しい値を返し続けます。
後から列を追加する可能性がある表では、XLOOKUP関数のほうが安心して使えます。
利用可能なバージョン
<VLOOKUP関数の場合>
VLOOKUP関数については全バージョンのExcelで利用可能です。
<XLOOKUP関数の場合>
XLOOKUP関数はMicrosoft 365、Excel 2021・2024以降で使える関数で、Excel 2019以前のバージョンでは利用できません。
古いバージョンのエクセルを利用している方や、仕事で関わる相手が古いバージョンのエクセルを利用している場合などは、XLOOKUP関数よりもVLOOKUP関数を利用することをおすすめします。
XLOOKUP関数の基本的な使い方(具体例つき)
違いが分かったところで、実際にXLOOKUP関数を入力する手順を具体例で見ていきましょう。ここでは、商品IDから価格を検索する例で説明します。
- 結果を表示したいセルを選択し、「=XLOOKUP(」と入力する
- 検索値(探したい商品IDが入っているセル)をクリックし、カンマを入力する
- 検索範囲(商品IDが並んでいる列)をドラッグして選択し、カンマを入力する
- 戻り範囲(取り出したい価格が並んでいる列)をドラッグして選択し、「)」を入力してEnterキーを押す

商品IDが「A103」の価格を調べたい場合、数式は次のようになります。
=XLOOKUP(検索値のセル, 商品IDの列範囲, 価格の列範囲)[見つからない場合]・[一致モード]・[検索モード]の引数はすべて省略可能です。まずはこの3つの引数だけのシンプルな形で使ってみて、慣れてきたらエラー時の代替値などを追加していくのがおすすめです。
なお、数式は正しいはずなのに検索値が見つからない場合、検索値と検索範囲でデータ型(数値か文字列か)が食い違っていることがあります。特に型番や電話番号を検索キーにしていると、入力時に数字が勝手に日付に変換されてしまっているケースも。心当たりがある場合はエクセルで数字が勝手に日付になる時の直し方も確認してみてください。
複数の条件で検索する方法
冒頭で挙げた「複数の検索値を使いたい」というお悩みも、XLOOKUP関数なら1つの数式で解決できます。例えば、商品名がB列、サイズがC列、価格がD列に並んだ表で、F1セルの商品名とG1セルのサイズの両方に合う価格を取り出したい場合は、次のように入力します。
=XLOOKUP(1, (B2:B5=F1)*(C2:C5=G1), D2:D5)「(B2:B5=F1)」の部分は、行ごとに条件に合うかどうか(TRUE/FALSE)を判定します。これを「*」で掛け合わせると、TRUEは1、FALSEは0として計算されるため、すべての条件に合う行だけが1になります。検索値に「1」を指定することで、その行の価格を取り出せる仕組みです。
条件を3つ以上にしたいときは、「*(範囲=セル)」を追加していきます。また、条件に合う行がないと#N/Aエラーになるので、基本の使い方と同じく、4つ目の引数[見つからない場合]に「”該当なし”」などを指定しておくと安心です。
=XLOOKUP(1, (B2:B5=F1)*(C2:C5=G1), D2:D5, "該当なし")まとめ
ここまで、XLOOKUP関数とVLOOKUP関数の機能を比較し、実際の使い方も紹介してきましたが、基本的には、XLOOKUP関数はVLOOKUP関数の上位互換と呼べる関数だと言えそうです。
特に、これからExcelの勉強を本格的に始める方はぜひXLOOKUP関数を覚えておきましょう!XLOOKUP以外にも仕事でよく使う関数は、仕事で本当によく使うExcel関数10選でまとめています。
