今回の記事では、XLOOKUP関数の応用を紹介します。
- 省略可能な第4引数～第6引数を使いこなす
⇒実例も紹介！ - VLOOKUP関数に出来ないことをXLOOKUP関数で実現する
XLOOKUPの基本的な使い方はバッチリですか？もし不安な方はコチラの基礎編をチェックしてみてください。
基礎をマスターしてから本記事を読むと、応用テクニックの理解度がグンと高まりますよ！
【もっと業務効率アップ！】省略可能な引数を使いこなそう
基礎編で必須の引数３つも理解した！テーブルも理解した！
これでXLOOKUP関数はマスターだね！
いえいえ、実は『省略可能な引数』を使いこなせると、エラーを隠したり探し方を工夫したり・・・自由にデータを操ることができて、業務効率がアップしますよ。経理実務での活用例と一緒に見てみましょう！
第4引数：「見つからない場合」はこう使う
見つからない場合とは、検索値が検索範囲になかった場合のこと。
その場合に何をするか、指示を書いてあげましょう。
例えば画像の赤枠のようなエラーが出たことはありませんか？
XLOOKUP関数を知っていたら検索値を表に追加すれば良いって分かるけど、何も知らずに使っている人は不安になっちゃうよ。
そんなときは第4引数「見つからない場合」の出番です。
まずは簡単に、文字を表示してみましょう。
指定の文字を表示して次の作業へ誘導する
=XLOOKUP(検索値,検索範囲,戻り範囲,"社員マスタに追加")
これならXLOOKUP関数の中身を知らなくても、次にするべき作業に誘導することができるね。
空白にしたい場合は第四引数を「””」にすれば良さそう！
第4引数の[見つからない場合]は文字ではなく「別の指示＝ほかの関数」を組み込むことも可能です。例を見てみましょう。
IF関数で条件分岐する例：与信限度額を設定する
【想定シーン：与信管理表で顧客の与信限度額を抽出する】
与信管理表を検索してもHITしないということは「新規顧客」ということです。
そんな時は第4引数にIF関数を入れて別の計算結果を表示してみましょう。具体的には、取引種別が「個人」だったら”3万”、「法人」だったら”要検討”と表示してみます。
=XLOOKUP(検索値,検索範囲,戻り範囲,IF(取引種別="個人",30000,"要検討")
第5引数：「一致モード」はこう使う
一致モードとは、検索値を探すときに「完全一致」で探すか「近似値」で探すかということです。
※一致モードを省略すると「完全一致」が適用されます。
0・・・完全一致
-1・・・完全一致または次に小さい項目
1・・・完全一致または次に大きい項目
2・・・ワイルドカード文字との一致
3・・・正規表現に一致する※Microsoft 365のみ
ワイルドカード文字や正規表現の一致モードは文字の「あいまい検索」ができる機能だよね
そうです。経理実務ではあまり出番がないので、今回は数字の範囲検索で使える「次に小さい（大きい）項目」の一致モードを紹介します。
「次に小さい（大きい）項目」を使う例：振込手数料を抽出する
【想定シーン：振込手数料を算出する】
振込手数料は金額によって変わるので、完全一致での検索は難しいですよね。
そんな時は第5引数に「-1次に小さい項目」を使って近似値で検索する指示を出しましょう。
=XLOOKUP(検索値,検索範囲,戻り範囲,,-1)
第6引数：「検索モード」はこう使う
検索モードでは、キーワードを検索範囲から探すときに、表の「上から」探すか「下から」探すかを指定することができます。
※検索モードを省略すると「1：先頭から末尾へ」つまり上から検索が適用されます。
1・・・先頭から末尾へ検索＝上から下へ検索
-1・・・末尾から先頭へ検索＝下から上へ検索
2・・・バイナリ検索(昇順で並べ替え)
-2・・・バイナリ検索(降順で並べ替え)
基本は先頭から末尾へ、つまり上から下へ検索で問題ないよね。
バイナリ検索は「大量のデータを高速検索する」モードだね。
そうです。ただし、データの並びが正確じゃないと間違った答えをはじき出します。
通常の業務では使わなくても問題ないですよ。
今回は「末尾から先頭へ検索」を有効活用する例を紹介します。
末尾から先頭に検索を使う例：出納帳の残高を表示する
【想定シーン：現金出納帳で指定日の残高を表示する】
同じ日付で複数の取引があった場合、現金出納帳の残高は下にあるデータが最新ですよね。
XLOOKUPで上から検索すると最新のデータを取ることができないんです。
そんな時は第6引数に末尾から先頭に検索を設定して、下から検索するように指示を出しましょう。
=XLOOKUP(検索値,検索範囲,戻り範囲,,-1,-1)
最新の情報は常に表の一番下にある！
これを活かせば「末尾から先頭に検索」を有効に使えそうだね！
他にもこんな場面で活用できます。
・仕入れ実績から最新の単価を抽出する
・経費精算実績から申請者の最新の精算金額を抽出する
どんどん使っていきましょう。
VLOOKUPにはできない！実務で役立つ３つの活用術
ここからはVLOOKUPではスマートに実現できなかった、XLOOKUPならではの活用術を４つ紹介します
複数項目を一気に抽出する
【想定シーン：振込先情報を抽出する】
４つの項目「銀行名」「支店名」「預金種別」「口座番号」を一つのXLOOKUP関数で一気に抽出してみましょう。
=XLOOKUP(検索値,支払先マスタ[支払先名],支払先名[銀行]:支払先マスタ[口座番号])「戻り範囲を複数列にする」これだけでOKです。
![XLOOKUP関数の戻り範囲に複数列（[銀行]から[口座番号]まで）を指定し、一度の入力で複数の検索結果を隣接するセルに一括表示させる例。VLOOKUP関数では不可能な「スピル機能」による効率的なデータ抽出を解説している。](https://www.shigoto-boost.com/wp-content/uploads/2026/02/6.XLOOKUP-Function-Multiple-Column-Extraction-Spill_1.jpg)
複数条件で検索して抽出する
【想定シーン：振込先情報を抽出する】
例えば「取引先の会社名」と「支店名」の両方に合致する、つまり複数条件に合致する振込先情報を抽出したい場合もありますよね。
XLOOKUPでは複数条件に合致する情報の抽出も簡単にできます。
=XLOOKUP(検索値＆検索値,検索範囲＆検索範囲,戻り範囲)検索値と検索範囲をそれぞれ「＆」でつなぐだけでOKです。
空白セルを「0」表示させず空白で返す
XLOOKUP関数で「戻り範囲＝抽出するセル」が”空欄”だと、”0″という数値が表示されます。
抽出結果が空白だったから、Excelが気をきかせて0(からっぽという意味)を表示させているんだ
Excelは計算ソフトだから0という数字が便利だと思っているのかな。
そうね。便利な場面もあるけれど、正直よけいなお世話だと思う時も・・・
対策を手軽な順に紹介します。
対策１：計算式に「＆””」を追加する
抽出結果を後で計算に使わない場合のテクニックです。
計算式に「＆””」を追加すると、結果がすべて文字列になります。
すると空っぽという意味の0ではなく、空っぽの文字列つまり空白を表示します
=XLOOKUP(検索値,検索範囲,戻り範囲)&""
対策２：表示形式を変更して見た目だけ整える
抽出結果を計算式に使う場合で見た目を整えるだけなら、表示形式を変更する方法が有効です。
関数に手を加える必要はありません。
Excelの表示形式はセミコロン；で区切ることで、左から順に「正の数の時；負の数の時；ゼロの時；文字列の時」の見せ方を指定できます。
抽出結果の見た目は空欄ですが、コピーして別のセルに値貼り付けをすると「0」が表示されます。
つまり、SUMなどの計算に使うことも可能です。
対策３：LET関数とIF関数と組み合わせて条件分岐させる
LET関数を使うと任意の名前を付けた箱を作ることができます。
その箱に計算結果を入れると、同じ計算式を何度も書かなければいけないような場面で、数式をシンプルにできるということなんです。
今回実現したいことは
「もしもXLOOKUPの結果が”空欄”だったら、”空欄”のまま、”空欄”じゃなかったらXLOOKUPの結果をそのまま表示する」
ということです。
IF関数だけで表現してみましょう。
=IF(XLOOKUP(検索値,検索範囲,戻り範囲)="","",XLOOKUP(検索値,検索範囲,戻り範囲))長いしゴチャゴチャしてるね・・・。
XLOOKUPの数式を2回も書かなくちゃいけないんだ。
そこでLET関数の出番！XLOOKUPの結果にLET関数で名前を付けてみましょう！
=LET(結果,XLOOKUP(検索値,検索範囲,戻り範囲),IF(結果="","",結果))
[結果]という名前の箱にXLOOKUPの結果を入れる。
[結果]が空欄⇒空欄を表示する。
[結果]が空欄じゃない⇒[結果]を表示する
前から順番に数式を読めばいいから、分かりやすいね。
これならXLOOKUPの結果をSUMなどの計算に使うこともできますよ。
VLOOKUPやINDEX&MATCHとの違いは対応能力の高さ
ここまでXLOOKUPについてみてきました。
最後にVLOOKUPやINDEX&MATCHとの違いをまとめます。
| 比較項目 | VLOOKUP | INDEX & MATCH | XLOOKUP |
|---|---|---|---|
| 検索方向 | 右方向のみ | 左右自由 | 左右自由 |
| 列挿入への耐性 | 弱い（ズレる） | 強い（ズレない） | 強い（ズレない） |
| 数式のシンプルさ | シンプル | 複雑（二つの関数を組合せ） | 極めてシンプル |
| エラー処理 | IFERRORが必要 | IFERRORが必要 | 標準機能で完結 |
| デフォルトの一致 | 近似一致（誤操作の元） | 指定が必要 | 完全一致（安全・確実） |
| スピル対応 | 不可 | 不可 | 可能（複数列一括取得） |
XLOOKUP関数が便利なことは分かったけど、VLOOKUPやINDEX&MATCHはお役御免ということでいいのかな？
機能面ではXLOOKUP関数に軍配が上がります。
ですが、VLOOKUPやINDEX&MATCHを使う場面も残っていますよ。
◆Excelのバージョンが古い
XLOOKUP関数はOFFICE2021から使える関数です。それ以前のバージョンでは使えません。
◆周囲がVLOOKUPやINDEX&MATCHをメインで使っている
XLOOKUP関数だと他の人がメンテナンスできないかもしれません。
その場合はXLOOKUP関数についてマニュアルを準備してケアする必要がありますね。
まとめ
今回はXLOOKUP関数を紹介しました。
XLOOKUP関数は「シンプルな数式」で「表の配列に振り回されずに」使うことができる、LOOKUP系の中でも最強の関数です。
XLOOKUP関数を使いこなすことができれば手入力の頻度も減り、作業時間の短縮やミスの削減につながるので、業務効率化にも使える関数です。
ぜひ有効活用してくださいね。
コメント