【Excel】XLOOKUP関数を徹底解説｜VLOOKUPとの違いも紹介

Excelでのセル検索といえば「VLOOKUP関数」が有名ですが、最近ではより柔軟で使いやすい「XLOOKUP関数」が注目を集めています。
この記事では、XLOOKUP関数の基本から応用、VLOOKUPとの違いまで、初心者にもわかりやすく解説いたします。

－著者情報－
名前：Utatane
Excel使用歴：15年以上
ひとこと：初心者だった当時に戻った気持ちになって解説いたします

※わかりやすさを重視しております。厳密には解釈が異なる場合がありますことをご了承ください。

実演パソコンのスペック（ソフトやバージョンによっては、表示や結果が異なる場合があります）

OS：Windows11 Home 64bit
Excel：Microsoft Office Home and Business 2021

スポンサードリンク

【Excel】XLOOKUP関数の使い方
よくあるエラーと対処法
XLOOKUP関数が使えない時の代替関数
VLOOKUP関数との違い
最後に

【Excel】XLOOKUP関数の使い方

XLOOKUPとは？

「XLOOKUP」は、Excelでデータを検索・取得するための関数で、VLOOKUPやHLOOKUPの上位互換と言えます。
主な特徴は以下のとおりです。

検索がヒットした場合、検索値そのものか、同じ行の別の値を返すことができる
検索結果が複数存在する場合は、最初にヒットしたものを返す
上から下への検索だけでなく、下から上への検索も可能
左側の列の値を抽出できる
返す値の列番号を指定しなくてよい
範囲の挿入・削除に強い
見つからない場合の処理を指定できる

XLOOKUP関数の基本構文

まずはXLOOKUP関数の基本構文と引数の役割を見ていきましょう。
引数や返り値については下記記事をご参考ください。

▼基本構文
=XLOOKUP(検索ワード, 検索範囲, 返す値の範囲, [見つからない場合の処理], [一致モード], [検索モード])
※[]内の引数は省略可能

▼使用例
=XLOOKUP("Utatane", A:A, C:C)
※A列の中から「Utatane」と完全一致するセルを検索し、ヒットしたら同じ行のC列の値を返す

検索ワード：検索する値。直接入力するか、セルを指定。
検索範囲：検索ワードを検索する範囲（列）を指定。
返す値の範囲：検索ワードが見つかった場合に返す値の範囲（列）を指定。
見つからない場合の処理：エラーの代わりに表示させるメッセージを指定。
一致モード：検索方法
0なら完全一致（規定）
1なら近似一致
2なら部分一致（検索ワードにワイルドカード「?」や「*」を使用する必要あり）
検索モード：検索方向
1なら上から下へ検索（規定）
-1なら下から上へ検索

XLOOKUP関数のシンプルな使い方

まずは下図をご覧ください。

A～C列に商品一覧があります。
今回はE2にある商品コード「A0003」の価格をXLOOKUPを使って表示させてみましょう。
F2に下記の通り入力してみてください。

=XLOOKUP("A0003", A:A, C:C)

検索ワードを文字列で指定する場合は、「”（ダブルクォーテーション）」で囲ってください。

指定する列は直接入力してもよいのですが、Excelの列を選択（参照）することでも指定できます。
詳しくはこちらの記事をご参考ください。

検索ワードもセル参照で指定することができます。

=XLOOKUP(E2, A:A, C:C)

検索ワードをセル参照で指定する場合は、「”」で囲う必要はありません。

これで商品コード「A0003」（せんべい）の価格である「50」を表示させることができました。

これは、検索範囲である「A列」の上から検索が開始され、検索ワードである「A0003」が見つかった場合、
返す値の範囲であるC列にある同じ行の値を返すためです。

今度は一覧に無い「A0009」を検索ワードに指定してみましょう。

「A0009」は一覧に無いため、エラーが返ってきました。
検索結果が見つからなかった場合は、「#N/A」と表示されることを覚えておきましょう。
（検索結果が見つからない場合の処理をする方法は後述）

【数値と文字列の違いに注意】

上図を見てみると、あきらかにコード「2」が一覧に存在するはずなのにエラーが出てしまっています。
これは型違い（数値と文字列）による不一致と判定されたためのエラーです。
よく見ると右側の検索ワードの「2」は左上に三角印があるため文字列なのに対し、一覧の商品コードは数値です。

数値・文字列を統一させるか、「VALUE関数（数値に変換）」などを使って対処しましょう。

検索範囲にB列、返す値の範囲にA列を指定することで、左方向の列の値を抽出することができます。

VLOOKUP関数には無かった機能で、非常に便利ですので覚えておいてください。

XLOOKUP関数の少し高度な使い方

見つからない場合の処理を指定

XLOOKUP関数では、検索ワードが見つからない場合の処理を指定することができます。
下図は検索ワードが見つからなかった例です。

検索ワードであるE2の「A0009」がA列に存在しなかったため、見つからずエラーが表示されました。
では、見つからない場合の処理をしてみましょう。
第4引数に「”該当なし”」と入力してみます。

セルにも「該当なし」と表示させることができました。

部分一致検索

第5引数に「2」を指定することで、部分一致検索が可能です。
その場合、第1引数である検索ワードは、「*」や「?」などのワイルドカードを使用してください。

第4引数（見つからない場合の処理）を省略して第5引数（一致モード）を指定することはできません。
第4引数を指定したくない場合は、下図のように「半角スペース＋カンマ」を入力しましょう。
（カンマのみでもOKです）

B列にある「丼」が含まれた値が検索され、それに対応した価格が表示されました。
今回のように「カツ丼」「親子丼」など、該当する値が複数ある場合、は一番上のもの（カツ丼）がヒットします。
下のものを抽出したい場合は次項をご参考ください。

下から上へ検索

第6引数に「-1（下から上へ検索）」を指定すると、下から上へ検索が走ります。
下図は、前項と比べて第6引数を追加しただけです。

今度は「500」と表示されました。
これは下から上へ検索したため、最初にヒットした親子丼の価格が表示されるためです。

スポンサードリンク

よくあるエラーと対処法

#N/A

XLOOKUP関数を使用したセルに「#N/A」が表示された場合は、検索ワードが見つからなかったことを示しています。
明らかに存在しているのにエラーが出る場合は、数値と文字列などの型違いが原因かもしれません。
VALUE関数を使うなどして、型を一致させましょう。

目に見えないスペースやTABなどが入っていて、検索ワードと一致しないと判断されている場合もあります。
検索ワードや一覧データのセル内をよく調べてみましょう。

検索ワードが見つからなくてもいいから、エラーを表示させたくない場合は、第4引数に「見つからない場合の処理」を指定しましょう。

#VALUE!

XLOOKUP関数を使用したセルに「#VALUE!」が表示された場合は、検索範囲と返す値の範囲が一致していないことを示しています。

下図のように、検索範囲を列ではなくセル単位で指定した場合に、範囲がズレていてエラーとなるケースがあります。

できるだけ範囲は列単位で指定しましょう。

#NAME?

XLOOKUP関数を使用したセルに「#NAME?」が表示された場合は、関数名が間違っていたり、引数の指定が適切でない可能性があります。
「XLOKUP」や「XLOOOKUP」など、入力間違いが多い関数ですのでよく見てみましょう。
また、引数の指定が適切かも併せて確認しましょう。

Excelのバージョンによっては、「XLOOKUP」が使えないので、「#NAME?」と表示されることがあります。
その場合は、次項を参考に代替関数を試してみてください。

スポンサードリンク

XLOOKUP関数が使えない時の代替関数

XLOOKUP関数は、Excel2019以降に搭載されている関数のため、古いバージョンでは使うことができません。
しかしながら、「INDEX関数」と「MATCH関数」を組み合わせることで代替できますので、紹介いたします。

=INDEX(返す値の範囲, MATCH(検索ワード, 検索範囲))

まずセルに「=INDEX()」と入力したら、()内に「返す値の範囲」を指定します。
その後、「,（カンマ）」に続けて「MATCH()」と入力し、()内に「検索ワード, 検索範囲」を指定すれば完了です。

スポンサードリンク

VLOOKUP関数との違い

XLOOKUP関数とVLOOKUP関数の主な違いをまとめてみました。

比較項目	VLOOKUP	XLOOKUP
左の列の値を抽出	×	○
返す列番号の指定	必要	不要（列自体を指定）
見つからない場合の処理	×（IFERROR関数を使えば可能）	○
列の挿入・削除	弱い（エラーが発生）	強い（影響なし）
下から上への検索	×	○