必須引数と任意引数
XLOOKUP関数の基本的な使い方は、XLOOKUP関数｜基本編の記事でご紹介していますが、実はXLOOKUP関数には、まだ引数が何個かあります。
XLOOKUP関数｜基本編で紹介したのが必須引数と呼ばれる、XLOOKUP関数を使用する上で絶対に必要な引数となります。ここではXLOOKUP関数の応用編として任意引数を使用したテクニックをご紹介していきます。
- 必須引数（第1引数～第3引数）
XLOOKUP関数を作成する上で絶対に指定必要な引数となり、第1引数の「検索値」と第2引数の「検索範囲」、第3引数の「戻り範囲」が必須引数となります。 - 任意引数（第4引数～第6引数）
XLOOKUP関数を作成する上で、指定しなくても良い引数ですが、指定することで、より高度にXLOOKUP関数を活用することが可能です。
第4引数の「見つからない場合」、第5引数の「一致モード」、第6引数の「検索モード」が任意引数となります。
練習用エクセルファイルをご用意したので、必要な方は下記よりダウンロードしてくださいね。
第4引数「見つからない場合」
XLOOKUP関数_応用編_練習用ファイルの「応用1」シートを使用して、第4引数の「見つからない場合」を見ていきましょう。
このシートではB列の商品名の中に、検索範囲にない「マウスC」をいくつか入れています。
このまま普通にXLOOKUP関数を組むと、検索範囲にない「マウスC」の商品番号セルには、当然「#N/Aエラー」が返ってきます。
返す値が見つからずに「#N/Aエラー」が出た場合、どのように表示するかを自分で指定するのが、この「見つからない場合」の引数になります。
実際に関数を入れていきましょう。
まずC2セルにXLOOKUP関数の第3引数まで入力します。
第3引数を入力したら、カンマ , を打って第4引数を入力しましょう。
第4引数は、検索範囲に検索値がなかった場合の戻り値を指定するので、今回は「該当なし」と表示させることにします。
「該当なし」は文字列なので、ダブルクォーテーションで囲むのを忘れないように注意が必要です。
戻り値を入力したら Enter を押して関数を完成させましょう。
オートフィルで最終行まで関数を反映させれば完了です。
マウスCの商品番号が表示されるC3セルには「該当なし」の表示がされました。
ここでは分かりやすく「該当なし」としましたが、文字列ではなくダブルクォーテーションを2つ打って空白を指定することも可能です。
これまでVLOOKUP関数などで、検索範囲に戻り値が見つからない場合はIFERROR関数を一緒に組むことで、戻り値を指定していましたが、XLOOKUP関数では引数で指定することが可能になったので、より簡潔に関数を組むことができて凄く便利ですね！
任意の引数なので、もちろん省略することも可能です。
省略した場合は「#N/A」が返ってきます。
=XLOOKUP(検索値,検索範囲,戻り範囲,見つからなかった場合の表示)
=XLOOKUP(B2,$E$2:$E$7,$F$2:$F$7,"該当なし")
第5引数「一致モード」
一致モードとは、検索方法と同じような意味合いになります。
デフォルト指定の完全一致で検索を行うのか、VLOOKUP関数の「TRUE検索」のように近似値一致で検索するかを指定できます。更にXLOOKUP関数では近似値一致の詳細な指定が可能になりました。
それが第5引数の「一致モード」になります。
VLOOKUP関数の「TRUE検索」については、後日記事をUP予定です
一致モードには全部で下記5つの選択肢があります。（※2025年4月現在）
- 0 完全一致
- -1 完全一致または次に小さい項目
- 1 完全一致または次に大きい項目
- 2 ワイルドカード文字との一致
- 3 正規表現に一致する
3つめの「正規表現に一致する」は2025年1月に新しく追加された選択肢のため、ご使用のExcelバージョンによっては表示されない場合があります。
それでは上でご紹介した第5引数の選択肢を、ひとつずつ順番に見ていきましょう。
0 完全一致
これはそのままですが、完全一致となります。
第5引数を省略した場合は、この完全一致で検索されます。
-1 完全一致または次に小さい項目
「完全一致または次に小さい項目」と聞くと、ややこしく感じますが、要するに
実際に操作しながら見ていきたいと思います。
XLOOKUP関数_応用編_練習用ファイルの「応用2」シートを使用して第5引数の「一致モード」を見てみましょう。
まずC2セルにXLOOKUP関数の第3引数まで入力します。
次に第4引数ですが、ここでは省略したいと思います。
第4引数を省略して第5引数を入力したい場合、必ず第4引数のカンマ , を打ってから第5引数に進みましょう。
カンマ , を打たずに省略だけしてしまうと、エクセルは第5引数を第4引数と認識してしまうので注意が必要です！
最後に入力した引数以降、すべての引数を省略する場合はカッコを閉じてしまえば良いですが、途中の引数を省略して、まだ後ろに引数を入れる場合は、必ず省略した引数分のカンマ , が必要です。
第4引数の省略カンマ , を打ったら、いよいよ第5引数です。
-1と入力するか、キーボードの ↓ キーで上から2つ目の「-1 完全一致または次に小さい項目」を選択します。
第5引数を入力したら Enter を押して関数を完成させましょう。
B3セルの点数は「75」なので、一致モードが完全一致だと「#N/Aエラー」が返ってきますが、今回は完全一致または次に小さい項目を指定したので、エラーにはならず「B」が返ってきました。
- ●以上●未満に該当する値が返ってくる。
- 検索値の「75」は評価基準の「70点以上90点未満」
- 「70点以上90点未満」の小さい項目である「70点」の評価「B]が返ってくる
=XLOOKUP(検索値,検索範囲,戻り範囲,第4引数を省略,一致モード)
=XLOOKUP(B3,$E$3:$E$8,$F$3:$F$8,,-1)
1 完全一致または次に大きい項目
次は「完全一致または次に大きい項目」を見てみましょう。考え方は「完全一致または次に小さい項目」と同じです。
第4引数の省略カンマ , を打ったら、第5引数を入力します。
1と入力するか、キーボードの ↓ キーで上から3つ目の「1 完全一致または次に大きい項目」を選択します。
第5引数を入力したら Enter を押して関数を完成させましょう。
B3セルの点数は「75」なので、一致モードが完全一致だと「#N/Aエラー」が返ってきますが、今回は完全一致または次に大きい項目を指定したので、エラーにはならず「A」が返ってきました。
- ●を超える●までに該当する値が返ってくる。
- 検索値の「75」は評価基準の「70点を超える90点まで」
- 「70点を超える90点まで」の大きい項目である「90点」の評価「A]が返ってくる
=XLOOKUP(検索値,検索範囲,戻り範囲,第4引数を省略,一致モード)
=XLOOKUP(B3,$E$3:$E$8,$F$3:$F$8,,1)
2 ワイルドカード文字との一致
「ワイルドカード文字との一致」についてやってみましょう。
基本的な考え方は「完全一致または次に小さい項目」と同じでが、今回はワイルドカードを使用して、検索値を部分一致のあいまい検索で値を返していきます。
XLOOKUP関数_応用編_練習用ファイルの「応用2」シートL列～P列を使用して、もう少し詳しく見てみます。
L2セル～M8セルにお弁当名の一覧があり、O3セルにお弁当名を入力するとP3セルに単価が返ってくる仕様になっています。その際、O3セルにお弁当の正式名称ではなく、部分一致でも単価を返してほしいです。
「おろしハンバーグ弁当」でも「ハンバーグ」でも「ハンバーグ弁当」でも単価が返ってくるように関数を組むということですね！
さっそくXLOOKUP関数を入れていきましょう。
単価はP3セルに返ってくるので、P3セルにXLOOKUP関数を入力します。
= XLOOKUPと入れたらTab キーを押して使用する関数を確定させます。続けて第1引数を入力します。
今回の検索値はO3セルに入力された文字列なので、O3セルを選択しますが、ただ選択しただけではO3セルに入力された文字列と完全一致でないと値が返ってきません。
そのため、まずここで検索値をワイルドカード文字にします。
「〇〇を含む」や「〇〇から始まる」などの任意の文字列を指定する際に使用（部分一致の文字列）
- 0文字以上の場合
任意の部分にアスタリスク * を入力
例1：「プリン*」→ 「プリン」「プリンパフェ」などプリンから始まる文字列に一致
例2：「*プリン*」→ 「プリン」「BIGプリンパフェ」など文字列の中にプリンがあれば一致
例3：「*プリン」→ 「プリン」「チョコプリン」などプリンで終わる文字列に一致 - 1文字の場合
任意の部分にクエスチョンマーク ? を入力
例1：「プリン?」→ 「プリン1」などプリンの後に任意の文字列が1文字ある文字列に一致
例2：「?プリン?」→ 「・プリン1」などプリンの前後に任意の文字列が1文字ずつあれば一致
例3：「?プリン」→ 「1プリン」などプリンの前に任意の文字列が1文字ある文字列に一致
今回は「0文字以上の部分一致」のワイルドカード文字としたいので、アスタリスク * を使用しますが、 * は文字列なので必ずダブルクオテーション “ でアスタリスク * を囲むのを忘れないようにしましょう。
次に検索値が入っているO3セルとアスタリスクをつなぐために & を入力してからO3セルを選択、またO3と入力します。
そして、前だけでなく後ろもあいまい検索にしたいので、再度 & とダブルクオテーション “ で挟んだアスタリスク * を入れてワイルドカード文字とします。ここまでが第1引数となります。
第1引数を入力したら、第2引数の検索範囲、第3引数の戻り範囲を入力し、第4引数を省略してカンマ , を打つところまで進めましょう。
第4引数を省略しカンマ , を打ったら第5引数を入力します。
今回は「ワイルドカード文字との一致」なので、キーボードの矢印キーで上から4つ目を選択するか、2と入力します。
ひとまず第5引数までで完成させるので、あとは Enter をXLOOKUP関数を完成させましょう。
O3セルに「チキン」と入力すると、チキンを含む「チキン南蛮弁当」の単価が返ってきました。
「幕の内」と入力しても「デラックス幕の内弁当」の単価が返ってきました。
ワイルドカード文字を使用すれば、あいまい検索(部分一致検索)が可能となるため、使用頻度が高いので、この機会にぜひ覚えておきましょう
ワイルドカードについての記事は準備中なので、少しお待ちください。
もっと詳しく知りたいよという方は、下記のCOUNTIF関数の動画でご紹介していますので、動画をご確認ください。
=XLOOKUP("*"&検索値&"*",検索範囲,戻り範囲,第4引数を省略,一致モード)
=XLOOKUP("*"&O3&"*",$L$3:$L$8,$M$3:$M$8,,2)
3 正規表現に一致する
「正規表現に一致」についてやってみましょう。
この一致モードは2025年1月に追加された新しい一致モードになります。
そのため、Microsoft365以外のバージョンをお使いの方は、「正規表現に一致」は選択肢に出てこないと思いますが、そういう一致モードがあるんだなと覚えておいてくださいね。
これも基本的な考え方は「ワイルドカード文字との一致」と同じですが、正規表現を使用することで、より複雑なパターンのあいまい検索が可能です。
文字列のパターンを表現するための特殊な言語で「いずれかを含む」検索が可能です。プログラミングで使用する機会が多く、Excelでは主に高度な検索や高度なフィルターなどに使用します。
XLOOKUP関数の一致モードでは「文字列を指定する際にワイルドカードよりも複雑にかつ複数の条件を組み合わせてあいまい検索する」ことになります。
正規表現を使用すれば、ワイルドカード文字の一致より高度な検索が可能になります。
正規表現については別記事で改めてご紹介したいと思いますので、ここではXLOOKUP関数_応用編_練習用ファイルの「応用2」シートR列～V列を使用して、簡単な使用例をご紹介します。
まず先ほどと同じようにワイルドカード文字の一致を使用して、お弁当単価を検索できるようにしてから「幕の内」と検索してみましょう。
V列の単価には、「デラックス幕の内弁当」の単価860が返ってきました。
では、今度は「幕ノ内」と入力してみてください。
表にある幕の内の「の」はひらがななので、カタカナの「ノ」を使って幕ノ内とした場合、表にはないと判断し#N/Aエラーが返ってきました。
今回は「幕の内」と「幕ノ内」でしたが、他にも例えば氏名検索で「渡辺」さんだと他にも「渡邊」「渡邉」や「斉藤」さんだと「斎藤」「齋藤」「齊藤」など複数のケースが考えられます。
このように、複数の候補があり、どの場合でも結果を返してほしい場合、この正規表現が役に立ちます。
今回は使用目的と分かりやすさから、数式内ではなく検索文字を入力するセルで検索条件を指定する方法をご紹介します。
先ほど入力したV3セルの数式を編集するので、まずV3セルを選択して F2 キーを押してV3セルをアクティブにします。続けて第1引数のワイルドカードを削除して検索値はU3セルとしましょう。
第1引数の修正が終わったら、第5引数を修正します。
現在、第5引数はワイルドカード文字の一致「2」が指定されていますので、ここを正規表現に一致するである「3」に修正します。
これで正規表現に一致する場合も、単価を返してくれるようになりました。
が、これだけではまだエラーが返ってきます。
今回は数式内ではなく、検索セルで正規表現を入力すう仕様にしたので、検索の際に正規表現を入力する必要があります。
ここでは「幕の内」「幕ノ内」に対応するよう入力してみましょう。
まず共通する「幕」を入力します。
次に角カッコ [ ] を入力します。後でこの中に条件を入れるのですが、先にもう一つの共通する文字である「内」を入力しましょう。
キーボードの矢印キーで先ほどの角カッコ [ ] に戻って、複数の検索候補を区切りなどは使用せず、続けて全て入力し、最後に Enter を押して完成です。これで正しく単価が返ってきました。
商品名が「幕ノ内」に変わっても問題なく単価が返ってきました。
では、商品名のカタカナの「ノ」を全角から半角の「ﾉ」に変えてみるとどうでしょう。
これは検索候補に入っていないので、当然#N/Aエラーが返ってきました。
では、この半角カタカナの「ﾉ」も候補に追加しておきましょう。
商品名が入っているU3セルを選択して F2 キーでアクティブにしたら、角カッコ内のどこでも良いので半角カタカナの「ﾉ」を追加して Enter を押せば追加完了です。
これで正しく単価が返ってきました。
今回は正規表現を使って検索するということがどういうことかのご紹介のため、シンプルな例をセルに入力する形でご紹介しましたが、正規表現を使用すれば、もっと複雑で高度な検索が可能になります。
エクセルでもXLOOKUP関数含め3つほど正規表現を使用できるようになったので、正規表現については、また別記事でご紹介したいと思います。
=XLOOKUP(検索値,検索範囲,戻り範囲,第4引数を省略,一致モード)
=XLOOKUP(U3,R3:R8,S3:S8,,3)
第6引数「検索モード」
最後のオプション引数は「検索モード」になります。
XLOOKUP関数_応用編_練習用ファイルの「応用3」シートを使用して、第6引数の「検索」を見ていきましょう。
A列～E列に会員名簿があって、G3セルの検索名に名前を入力すれば直近の契約日が返ってくるように
既にH3セルにはXLOOKUP関数が入っています。
G3セルの検索名にB3セルにある「東野 晴子」と入力すると、H3セルの直近契約日には「2022/12/1」と返ってきました。
通常、何も指定しなければXLOOKUP関数もVLOOKUP関数同様に、上から検索していき、最初に出てきたものを結果として返してきます。
よく見るとE列の解約日に日付が入っている人がいます。
一度は解約したけれども再契約している可能性があるので、分かりやすいように重複を赤字表示にしてみます。
重複文字を赤字にしたりする「条件付き書式の設定」の記事は、現在準備中です。
動画でご紹介していますので、分からないよという方は下の動画をご確認くださいね。
やはり山本優子さんが5/25に再契約しています。
山本優子さんを検索してみると、直近契約日である「2023/5/25」が返ってこなければいけないのに、最初の契約日の「2023/1/30」が返ってきています。
これは上から検索した結果、最初に見つかった2023/1/30が返ってきていることが原因です。
こういう場合に、検索モードで指定してあげると正しい結果が返ってきます。
実際にやってみましょう。
まず数式が入っているH2セルを F2 キーでアクティブにします。
現在は、第4引数の見つからない場合の引数までしか入っていないので、もう一つカンマ , を打って、第5引数に完全一致の0を入力して再度カンマ , を打ちます。
第5引数まで入力が済んだら、最後に第6引数の検索モード引で「末尾から先頭へ検索」の-1を指定したら Enter を押してXLOOKUP関数を完成させましょう。
第6引数で、この「末尾から先頭へ検索」を指定すると、デフォルトでは上から下へ検索するところを下から上に検索してくれるので、最新のデータを返してくれるようになります。
H2セルを確認すると、直近の契約日として正しい「2025/5/25」が返ってきました。
VLOOKUP関数ではできなかったので、便利な引数として覚えておきましょう！
=XLOOKUP(検索値,検索範囲,戻り範囲,"見つからない場合",一致モード,検索モード)
=XLOOKUP(G3,B3:B18,A3:A18,"-",0,-1)
まとめ
このXLOOKUP関数ひとつで、これまでVLOOKUP関数ではできなかった色々なことができるようになったので
XLOOKUP関数はぜひ基本から応用まで、しっかり覚えてマスターしておきましょう。
XLOOKUP関数の基本は別記事でご紹介しています
動画でもご紹介していますので、動画を見ながら操作を確認したい方は下記からどうぞ！
励みになります！高評価とチャンネル登録よろしくおねがいいたします。