今回はXLOOKUP関数を使いこなす方法を紹介します。
- 手入力作業に時間を取られ、気が付いたら定時を過ぎている
- VLOOKUP関数で列を数えることが面倒すぎる
- データに列を挿入したらVLOOKUP関数がエラーになった
VLOOKUP関数は横長のデータに使うと、何列目か分からなくなって目が回るよ～
それに検索列も一番左に作らないといけないし・・・。
XLOOKUP関数をマスターすれば、そんな苦労とはおさらばです！
業務効率化を劇的に上げる、XLOOKUP関数の”すごさ”を一緒に見ていきましょう
- 入力作業の手間を”劇的に”減らすことができる
- 数式の構造がシンプルで後々の引継ぎやメンテナンスが楽
- 突き合わせ作業を自動化できるのでミスを減らすことができる
- VLOOKUP関数の不満を全て解消している
XLOOKUP関数は「あなた専用の超有能な司書」
XLOOKUP関数を例えるなら、膨大なデータの中から欲しい情報を一瞬で見つけ出す「専属の司書」のような存在。
例えばキーワードに『吾輩は猫である』という本のタイトルを伝えるだけで、作者や棚番号など紐づく情報を正確にメモして渡してくれます。
それってVLOOKUP関数と同じじゃないかなぁ・・・？
その通りです。ですが役割は同じでもVLOOKUPが苦手だった「列の挿入」や「左側の列から抽出」も軽々とこなす、上位互換のスーパー司書なのです！
もう二度と「手入力」を繰り返さない！XLOOKUP関数が輝く瞬間
XLOOKUP関数の真骨頂は、一度ルールを決めてしまえば、あとは自動でやってくれるという点にあります。
例えば、経理の仕事でこんな「手作業」を繰り返していませんか？
- 仕訳を作成するたびに科目コードを調べて手入力する
- 会計ソフトの内容を報告資料用のExcelに手入力する
- 取引先の名前に合わせて振込先情報を一件ずつコピペする
これらはすべて、XLOOKUP関数に任せることができます！
「科目コード」をキーワードにして「科目名」を呼び出すのはもちろん、その逆だって自由自在。
あなたがこれまで「手入力」に捧げていた時間はXLOOKUPを使うことで「チェックや分析」という、より重要な業務のための時間へと変わるのです！
XLOOKUP関数の引数(中身)を解説
関数を「Excelを動かすための”指示書”と捉えるならば、引数は指示書に書いてある「情報」です。
XLOOKUP関数の引数は６つあります。前半の３つは必須、後半の３つは省略可能です。
=XLOOKUP(①検索値,②検索範囲,③戻り範囲,④[見つからない場合],⑤[一致モード],⑥[検索モード])①検索値・・・検索キーワード
②検索範囲・・・検索キーワードを探す範囲
③戻り範囲・・・欲しい情報の範囲
④[見つからない場合]・・・①検索キーワードが②検索範囲に存在しない場合に実行する内容
⑤[一致モード]・・・①検索キーワードを”完全一致”で探すか、”近似値”で探すか
⑥[検索モード]・・・①キーワードを”上から”順番に探すか、”下から”順番に探すか
数式を見ると、文字だらけで拒絶反応ががが・・・
落ち着いて、セル坊や！カンマで区切られている引数(情報)を前から一つずつ見ていけば大丈夫。まずは基本の理解から。
前半３つの必須の引数が使えるように、実践してみましょう。
基本の型を実例を使って解説！
日常業務では「別の表やシートから情報を持ってきたい」という場面がよくあると思います。
今回は日々の仕訳作成を例に、XLOOKUP関数で「科目マスタ」から「科目コード」を自動で出力してみます。
科目マスタ：科目コードと科目名の一覧表
仕訳入力シート：科目名を入力するシート
仕訳受け入れシート：XLOOKUP関数を使い、科目コードを表示するシート
- 仕訳入力シートに科目名を入力する（Ｄ3セル）
- XLOOKUP関数がマスタから「科目コード」を探してくる（Ｂ3：Ｂ6，Ａ3：Ａ6）
- 仕訳受け入れシートに「科目コード」が自動で入力される！（Ｄ12セル）
画像右下の「◆仕訳受け入れシート」にXLOOKUP関数を入力すると、画像右上「◆仕訳入力シート」に入力した値を参照して瞬時に「科目コード」が自動入力されます。
図解の数式を３つの要素(引数)に分けてみましょう。
=XLOOKUP(①検索値,②検索範囲,③戻り範囲)
↓
=XLOOKUP(D3,B2:B6,A2:A6)
① 検索値：D3 「支払手数料」という言葉をキーワードにして探します。
② 検索範囲：B2:B6 科目マスタの「科目名」の列から、キーワードを探し出します。
③ 戻り範囲：A2:A6 見つかったら、同じ行にある「科目コード」を拾ってきます！②と③のの範囲(行数)がズレているとエラーの原因になるので、必ず揃えましょう！
必須の引数３つは理解できたよ！実務で使う表の場合、データが今後も増えるから②検索範囲と③戻り範囲は”列丸ごと”（A:AやB:B）にした方が便利そうだね！
ストォォォォップ！！
”列丸ごと”指定はおススメしません！処理が重くなる可能性【大】ですよ！
【要注意】”列丸ごと”指定はExcelが重くなる！？正しい対策を知ろう
XLOOKUPで範囲を指定するとき、A:Aのように「列全体」を選択していませんか？
=XLOOKUP(検索値,A:A,B:B)確かにこれなら、今後データが増えても範囲を直す手間がないので便利に見えます。
ですが、実はこれ…Excelの動作を重くしてしまう原因の一つなのです！
重くなる原因は「関数が検索する範囲が広すぎる」から
Excelは１シートに100万行以上あります。”列丸ごと”指定すると…
- 関数は最後の行まで律儀に検索しに行く
- どこに何があったかを覚えておこうとする
- どこか一箇所を書き換えると、100万行分の計算をやり直そうとする
そのため、動作がカクカクしたり固まったりするんです。
じゃあ表の範囲をA1:A500みたいに余裕を持った範囲にすれば良いのかな？表の行が増えたら、関数の範囲も自動で広がればいいのに…。
表をテーブルにすれば良いんです！テーブル機能を使えば、数式の管理が魔法のように楽になりますよ！一緒にやってみましょう。
対策は「表をテーブルにする」こと！
テーブルとは、表の見出しごとに名前を付けて一つの塊として扱えるようにしたものです。その結果、数式を名前で扱うことができるようになります。
◆ただの[表]の場合◆
検索範囲と戻り範囲はセル番地で指定します。
表の範囲が増えた場合は手で修正が必要です。
=XLOOKUP(D3,B2:B5,A2:A5))
B2:B5の範囲は…えーっと、科目マスタの科目名のことで、A2:A5の範囲は…あれ？！消耗品費が範囲に入ってないよ！
◆テーブル(名前付きの表)の場合◆
検索範囲と戻り範囲は「表の見出し」を使って指定します。
表にデータが追加されると「自動で」範囲が拡張します。
=XLOOKUP(D3,科目マスタ[科目名],科目マスタ[科目コード])![XLOOKUP関数の引数に、テーブルの構造化参照（「T_科目マスタ[科目名]」や「T_科目マスタ[科目コード]」）を使用して検索する方法の解説図](https://www.shigoto-boost.com/wp-content/uploads/2026/02/4.XLOOKUP-Function-Structured-Table-Reference.jpg)
テーブル(名前付きの表)は数式の参照範囲が表の見出し名だから、どこを見れば良いのか分かりやすいね。自動で参照範囲が拡張されるなら、データが追加されても安心だね。
でも、今ある表を作り直さなくちゃいけないんじゃ…。
今の表を活かして、簡単４ステップでテーブル(名前付きの表)に変換することができますよ！
【４ステップ】既存の表をテーブル(名前付きの表)に変換しよう
- 表の範囲内のセルを１箇所選択する(表の範囲内ならどこでもOK）
- キーボードの【Ｃtrl】を押しながら【Ｔ】を押す
またはリボンメニューの【挿入】タブから【テーブル】を選択する - 範囲があっていることを確認し、「先頭行をテーブルの見出しとして使用する」にチェックを入れる
- テーブルに名前を付ける
表をテーブルにするメリットは他にもあります。
詳しくは↓コチラ↓の記事へどうぞ。
まとめ：XLOOKUPとテーブルは最強のコンビ！
今回はXLOOKUP関数の基本から、Excelを重くさせないための「テーブル化」の重要性について解説しました。
今回のポイントはコチラ！
- XLOOKUPはVLOOKUPの弱点をすべて克服している
⇒列を数える手間がなく、列の挿入にも強い！ - 「列丸ごと指定」は動作が重くなる原因
⇒100万行以上を常にスキャンさせるのは、Excelに大きな負担をかけます。 - 「テーブル」を使えば、シンプルでスマートに解決！
⇒データの追加に合わせて範囲が自動で広がるので、メンテナンスも楽々です。
NEXTステップ！☞
XLOOKUP関数の「第４引数」～「第６引数」を使いこなせば、もっと実務で便利に使いこなせます。
実務で使う時の具体例と一緒に紹介していますので、応用編もご覧ください！
コメント