【エクセル】XLOOKUP関数の使い方やVLOOKUP関数との違いを解説！

エクセル初心者

XLOOKUP関数の使い方ってどうやるの？

今までよく使われていたVLOOKUP関数とは何が違うのかな？

XLOOKUP関数は文字や数値を検索するための関数だよ。

VLOOKUP関数とよく似ているけど、新しい機能が増えてもっと便利で使いやすくなっているよ！

Dr.オフィス

XLOOKUP関数は、表の中から文字や数値などを検索することができる新しい関数です。

中身としてはVLOOKUP関数の上位互換のような位置づけで、より効率的な検索ができるようになっています。

この記事を読めば、XLLOKUP関数の使い方や特徴、そしてVLOOKUP関数との違いがよくわかります。

とても便利な関数なので、ぜひ新しく覚えて使いこなせるよう一緒に勉強していきましょう。

そして本記事は下記の動画でも解説しているので、ぜひ合わせてご覧ください。

事務職歴10年以上の私が、XLOOKUP関数の使い方や、VLOOKUP関数との違いを詳しく解説していくよ！

Dr.オフィス

XLOOKUP関数は基本さえわかってしまえば、たったの4ステップでとても簡単に使うことができます。

使いこなせるようになれば作業効率が格段にアップしますので、これからあなたがよく使う関数のひとつに加えてもらえたらと思います。

VLOOKUP関数の詳しい使い方については、こちら≫VLOOKUP関数で別シートから参照の記事で解説していますので参考にしてみてください。

※本記事は『OS：Windows11』画像は『Excelのバージョン：Microsoft365』を使用しています。

XLOOKUP関数とは

XLOOKUP関数は、文字列や数値を検索するために使用する関数です。

たとえば、商品番号を使ってリストから特定の商品の情報を探し出す、社員番号を使って名簿から特定の社員の情報を探し出す、などの場面で活躍します。

今回は上の図にある簡単な表を使ってXLOOKUP関数の使い方を解説します。

例として、商品番号を入力するだけで商品名を抜き出せるように関数を入力してみましょう。

XLOOKUP関数の基本的な使い方

XLOOKUP関数の書き方は以下の通りです。

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])

引数が6つ並んでいるので長いと思われるかもしれませんが、後ろの3つは省略して使うことが多いと思いますので、それほど難しく考えなくても大丈夫です。

1. STEP

   =XLOOKUPと入力する

   

   検索結果を表示したいセルをクリックし、はじめに=XLOOKUPと入力します。

   今回は商品名を検索したいので、H3セルに関数を入力していきます。

2. STEP

   検索値の指定

   

   検索値を指定します。

   今回はF3セルに商品番号を入力すると商品データが検索できるように数式を書きたいので、(F3,と入力します。

3. STEP

   検索範囲の指定

   

   続いて検索範囲を指定します。

   今回は商品番号から商品を検索したいので、検索範囲はA2セルからA8セルになります。

   したがって、関数の続きにはA2:A8,と入力します。

   範囲指定をするときは直接番号を入力してもいいし、指定したい範囲をドラッグしても楽に入力できるよ！

   

   Dr.オフィス

4. STEP

   戻り範囲の指定

   

   最後に、戻り範囲を指定してカッコで閉じます。

   戻り範囲とは、検索結果として抜き出したい部分を指します。

   今回は商品名を抜き出したいので、C2セルからC8セルが戻り範囲になります。

   関数の最後にC2:C8)と入力し、Enterを押しましょう。

5. STEP

   商品番号を入力する

   

   関数が間違いなく入力できたか確かめるため、商品番号を入力してみましょう。

   ためしにF3セルに3と入力し、Enterを押します。

6. STEP

   商品名が表示される

   

   商品名の欄に「石鹸」と表示されました。

   商品番号3の商品は石鹸なので、きちんと関数が機能していることが確認できました。

XLOOKUP関数とVLOOKUP関数の違い

XLOOKUP関数とVLOOKUP関数には、大きな違いが4つあります。

データを列ごと抜き出せる

例のような表から複数の項目を検索し抜き出したい場合、VLOOKUP関数では項目ごとにそれぞれ列番号を変えて関数を入力する必要がありました。

しかしXLOOKUP関数では、関数を1か所入力するだけで列ごとデータを抜き出すことができるようになっています。

1. STEP

   全ての項目を戻り範囲に指定する

   

   品目、商品名、単価をまとめて一気に抜き出してみましょう。

   B2からD8までを戻り範囲に指定します。

2. STEP

   商品番号を入力してみる

   

   商品番号の欄に5と入力しEnterを押します。

   すると、3つの項目にまとめて検索結果が表示されました。

   このように、該当のセルの後ろに続くセルも一緒に抽出してくれる機能をスピルというよ！

   

   Dr.オフィス

   スピルの機能については、こちら≫エクセルのスピルを徹底解説の記事で解説していますので参考にしてみてください。

列番号・検索方法の指定が不要

VLOOKUP関数では、以下のように列番号と検索方法を指定する必要がありました。

=VLOOKUP(検索値,範囲,列番号,検索方法)

列番号では検索値の列から数えて何番目かを、検索方法では検索値と完全一致かどうかを指定していました。

XLOOKUP関数では、単純に使いたいセルの番号を範囲指定すればよく、検索方法も自動的に完全一致として処理してくれます。

検索値より左も検索できる

VLOOKUP関数では、検索値は必ず表の一番左側になければいけませんでした。

XLOOKUP関数ではこれが解消され、検索値が検索対象より右側にあっても検索できるようになっています。

ためしに商品名を検索値として、左側にある商品番号を検索するよう関数を入力してみましょう。

1. STEP

   商品名から商品番号を検索する

   

   ①が検索値、②が商品番号の範囲です。

   F3セルに=XLOOKUP(H3,C2:C8,A2:A8)と入力し、Enterを押します。

2. STEP

   商品名を入力し検索する

   

   商品名の欄（H3セル）に「鉛筆」と入力し、Enterを押します。

   商品番号の欄（F3セル）に「1」と検索結果が表示されました。

見つからない場合の表示内容を設定できる

検索値が検索範囲に見つからなかった場合、VLOOKUP関数ではエラーになってしまいます。

XLOOKUP関数では、検索値が見つからなかった場合に表示する内容を設定できるようになりました。

ここでは仕事上よく使うと思われる空欄になるように設定してみましょう。

1. STEP

   見つからない場合の引数（4番目）に「””」を入力

   

   商品番号から品目以下の情報を検索する関数を作り、見つからない場合の引数に「””」と空白を入力します。

   最後をカッコで閉じてEnterを押します。

   =XLOOKUP(F3,A2:A8,B2:B8,””)

2. STEP

   存在しない商品番号を入力してみる

   

   表には存在しない「10」を商品番号の欄に入力してみます。

   Enterを押すと、結果が表示されるG3セルは空欄になりました。

   「””」の空欄部分を「”該当なし”」など任意の文字に置き換えれば好きなようにメッセージを設定できるよ！

   

   Dr.オフィス

XLOOKUP関数の使い方＋α

XLOOKUP関数は「検索値」「検索範囲」「戻り範囲」の3つのみでも使うことができますが、ここでは「一致モード」と「検索モード」について解説します。

一致モード

「一致モード」とは、VLOOKUP関数にあった「検索方法」と同じようなものです。

完全一致で検索を行うか、それとも検索値に近い値でも検索を行うのかを指定することができます。

「一致モード」で選択できる数値の意味は以下の通りです。

0…完全一致で検索する。入力を省略した場合も同じになる。
-1…完全一致するものがない場合、次に小さい値を検索する
1…完全一致するものがない場合、次に大きい値を検索する
2…検索値にワイルドカードを使う（あいまい検索）

それぞれ具体例と一緒に使い方を見ていきましょう。

一致モード「-1」

一致するものがない場合、検索した値の次に小さいものを検索します。

1. STEP

   一致モードを「-1」に指定する

   

   上の図で、得点から氏名を検索してみましょう。

   一致モードの引数に「-1」を入力し、Enterを押します。

2. STEP

   検索値の次に小さい値が検索される

   

   得点欄に80点と入力し、Enterを押します。

   80点の人はいないので、次に小さい点数である76点の小林恵さんが結果として表示されました。

一致モード「1」

完全一致するものがない場合、検索した値の次に大きいものを検索します。

1. STEP

   一致モードを「1」に指定する

   

   一致モードの引数に「1」を入力し、Enterを押します。

2. STEP

   検索値の次に大きい値が検索される

   

   80点の次に大きい点数である85点の佐藤太郎さんが検索されました。

一致モード「2」

ワイルドカードを使ってあいまい検索をすることができます。

例えば、田中さんのデータを探したいけれど、下の名前がわからない…という場合などにあいまい検索が役に立ちます。

1. STEP

   一致モードを「2」に指定する

   

   一致モードの引数に「2」を入力し、Enterを押します。

2. STEP

   田中*と入力し検索する

   

   田中さんの下の名前がわからないので、後ろにアスタリスクをつけて「田中*」で検索します。

   Enterを押すと、G2セルに田中裕子さんの得点が表示されました。

ワイルドカードの詳しい使い方については、こちら≫特定の文字やワイルドカードで探す方法の記事で解説していますので参考にしてみてください。

検索モード

「検索モード」では、どのような順番で表から検索してくるかを指定することができます。

VLOOKUP関数では上からの検索しかできませんでしたが、XLOOKUP関数で検索モードを指定すれば下からも検索することができます。

「検索モード」で選択できる数値の意味は以下の通りです。

1 …上（先頭）から検索する。入力を省略した場合も同じになる。
-1 …下（末尾）から検索する。
2 …バイナリ検索で上（先頭）から検索（昇順に並べ替えした場合有効）
-2 …バイナリ検索で下（末尾）から検索（降順に並べ替えした場合有効）

検索モード「1」と「-1」

「1」なら通常通り上から順に、「-1」なら下から順に検索をします。

具体的に「-1」を使った検索の例を見てみましょう。

1. STEP

   検索モードを「-1」に指定する

   

   E2セルにクラスを入力すると、氏名と得点が検索される関数を作ります。

   検索モードの引数に「-1」を入力し、Enterを押します。

2. STEP

   Bクラスを下から検索する

   

   Bクラスの生徒が下から検索され、結果に小林恵さんの氏名と得点が表示されました。

検索モード「2」と「-2」

検索モードの「2」と「-2」の基本的な機能は「1」「-1」と同じですが、バイナリ検索といって高速検索をするモードなので、データ量が膨大な時に使用します。

「2」は表の上から順に、「-2」は表の下から順に検索をします。

ただし、それぞれ昇順、降順に並べ替えてからでないと使用できないので注意しましょう。

XLOOKUP関数に関するQ＆A

【エクセル】XLOOKUP関数で効率よく検索しよう！

今回は、XLOOKUP関数の使い方とその概要、さらにVLOOKUP関数との違いについて解説しました。

XLOOKUP関数は「検索値」「検索範囲」「戻り範囲」の3つの指定だけで簡単に表からデータを抜き出せる便利な関数でした。

それでは最後に、XLOOKUP関数の基本の使い方をおさらいしておきましょう。

XLOOKUP関数を使いこなせるようになれば、VLOOKUP関数よりもさらに便利で効率的な作業ができるようになります。

この機会に使い方をマスターし、これからの検索作業にぜひ活用してもらえたらと思います。

VLOOKUP関数ついて紹介した記事もありますので、こちらもぜひ参考にしてみてください。