もう解凍はいらない：VBAでZIPのファイル一覧を取得する
k.w
SEへの道
XLOOKUP関数の使い方を完全解説｜0を表示しない・複数条件・別シート・合計まで対応
k.w
Contents
スポンサーリンク
この記事でわかること
XLOOKUPは「検索して値を返す」だけでなく、表示調整や集計にもつながる関数です。
参照作業の手戻りを減らし、表の作り替えにも強い式にできます。
この記事では、基本の書き方から「0を表示しない」「複数条件」「別シート」「合計」まで、実務で迷う順に整理します。
読み進めるほど、式のパーツを組み替えて応用できるようになります。
途中で出てくる式は、考え方を先に示してから例を出すので、丸暗記しなくても再現できます。
自分の表に当てはめるときの注意点も、要所で一緒に押さえます。
結論：迷ったときの選び方
完全一致で探すなら、まずは第5引数を省略して「見つからないときの表示」だけ整えるのが安全です。
最初から近似一致やワイルドカードに頼ると、気づかない誤一致が混ざりやすいです。
重複があるデータは「どの行を返したいか」を決めないと、正しい式でも結果がブレます。
先頭を返すのか、最新を返すのかで、必要な設定が変わります。
一覧で複数行を返したいなら、XLOOKUPではなくFILTERを使う場面が多いです。
XLOOKUPは1件取得の道具として割り切ると、設計がシンプルになります。
サンプル表とゴール
この記事の例では「商品コードから単価を返す」「0やエラーを空白にする」「条件に合う売上を集計する」をゴールにします。
ゴールを分解すると「参照」「表示」「集計」の3つに分かれます。
表の形が違っても、考え方は同じなので、自分の表に読み替えてください。
読み替えるときは「キー列は何か」と「返したい列は何か」を先に決めます。
| 列 | 内容の例 |
|---|---|
| A列 | 商品コード |
| B列 | 商品名 |
| C列 | 単価 |
| D列 | 数量 |
| E列 | 売上（単価×数量） |
XLOOKUP関数とは？できることと基本構文
XLOOKUPは、ある値をキーにして、別の範囲から対応する値を取り出す関数です。
「キーで探して、同じ行の別列を返す」と捉えると理解が早いです。
VLOOKUPと違い、左方向に返したり、列番号を数えたりする必要がありません。
列番号のズレによる事故を避けやすいのが大きな利点です。
検索範囲と返す範囲を別々に指定できるので、表の列が増減しても壊れにくいです。
列の並び替えが起きる運用ほど、XLOOKUPの恩恵が出ます。
できることをざっくり
表から単価や担当者名を引いてくるような「参照作業」を短い式で書けます。
マスタ参照のように「決まった表から引く」作業と相性が良いです。
見つからないときの表示を第4引数で指定できるので、#N/Aを消すのが簡単です。
表示を揃えるだけで、資料の読みやすさが一気に上がります。
下から検索する設定を使うと「最後に一致した値」つまり最新の情報を取り出せます。
履歴が積み上がる表で、最新の単価や担当を取りたいときに便利です。
基本構文（引数の役割）
XLOOKUPの基本形は、検索する値、探す場所、返す場所の3点セットで考えます。
この3点が決まれば、まずは式が書けます。
第4引数以降は「見つからないとき」「どう一致させるか」「どこから探すか」を調整する枠です。
必要になるまで触らない方が、原因の切り分けが簡単です。
| 引数 | 意味 | 例 |
|---|---|---|
| 第1 | 探したい値 | A2 |
| 第2 | 探す範囲 | 商品表[商品コード] |
| 第3 | 返す範囲 | 商品表[単価] |
| 第4 | 見つからないとき | “” |
| 第5 | 一致モード | 0 など |
| 第6 | 検索モード | -1 など |
最小の使用例
商品コードがA2にあるとして、単価を返すなら次の形になります。
= XLOOKUP(A2, 商品表[商品コード], 商品表[単価])この形が「キー」「検索範囲」「返す範囲」の最小セットです。
見つからないときに空白にしたいなら、第4引数に空文字を入れます。
= XLOOKUP(A2, 商品表[商品コード], 商品表[単価], "")見つからないときにメッセージを出したいなら、第4引数を文字列に変えます。
= XLOOKUP(A2, 商品表[商品コード], 商品表[単価], "未登録")XLOOKUPはいつから使える？対応バージョンと確認方法
XLOOKUPは古いExcelでは使えないことがあるので、まず環境確認が近道です。
式が通らない原因が「書き方」ではなく「バージョン」なことは意外と多いです。
特に、同じ会社のPCでも更新タイミングが違うと、同じファイルを開いても動く人と動かない人が出ます。
まずは「自分のExcelでXLOOKUPが使えるか」を早めに確定しておくと、遠回りを避けられます。
対応バージョンの目安
Microsoft 365のExcelは、基本的にXLOOKUPに対応しています。
常に最新版へ更新される前提なので、新しい関数が入るのも比較的早いです。
買い切り版でも、Excel 2021以降は対応することが多いです。
一方で、Excel 2016や2019では、環境によって使えないケースがあります。
同じ2019でも、更新チャネルや組織の配布形態によって差が出ることがあるので、年式だけで断定しない方が安全です。
いまのExcelで確認する方法
セルに「=XLOOKUP(1,{1},{1})」を入力してエラーの種類を見れば、ざっくり判定できます。
関数名自体が不明扱いなら、XLOOKUP未対応の可能性が高いです。
逆に、引数のエラーや別のエラーになる場合は、関数自体は認識されている可能性があります。
社内PCでは更新ポリシーで差が出るので、同じ年式でも結果が違うことがあります。
社外へ共有する資料やテンプレートで使うなら、共同編集者の環境も想定しておくと安心です。
使えない場合の代わり
XLOOKUPの代わりは、INDEXとMATCHの組み合わせが基本になります。
考え方は「MATCHで位置を探し、その位置をINDEXで取りに行く」という分業です。
XLOOKUPと同じように左方向でも返せるので、VLOOKUPの制約を回避できます。
エラー表示を整えるなら、IFNAやIFERRORを一緒に使うと近い動きになります。
例えば「見つからないときだけ空白にする」など、表示を揃える設計にしやすいです。
互換性が最優先の場面では「INDEX/MATCHで1件取得」に寄せる方が安全なこともあります。
一覧取得はFILTERが便利ですが、こちらも未対応環境があるので注意が必要です。
早見表：用途別おすすめ式
やりたいことから逆引きすると、引数の迷子になりにくいです。
下の表は「まずこの形から始める」を集めた早見表です。
| やりたいこと | おすすめの式の形 | ひとこと |
|---|---|---|
| 基本の参照 |
| まずはこれ |
| 見つからないとき空白 |
| #N/Aを消す |
| 0だけ空白 |
| 0と空白を分ける |
| 複数条件 |
| 結合キー |
| 最後の一致を返す |
| 最新優先 |
| 別シート参照 |
| 参照先を明示 |
| 単価×数量を合計 |
| 集計はSUM |
| 条件付き合計 |
| SUMIF向き |
上級者向け：一致モード（第5）と検索モード（第6）の使い分け
第5引数と第6引数は、設定を間違えると「動くけど違う結果」になりやすい領域です。
特に近似一致や下から検索は、表の作りや運用ルールが合っていないと誤回答になりやすいです。
だからこそ、用途を先に決めてから、必要なときだけ指定するのがコツです。
まずは第4引数までで「エラーをどう見せるか」を整え、その上で第5と第6を追加します。
一致モード（第5引数）：何を選ぶ？
通常のマスタ参照なら、完全一致が基本なので、第5引数は0の考え方で整理します。
完全一致のつもりなら、近似一致を選ばないことが事故防止になります。
近似一致を使うのは、点数に応じた評価、送料の区分、税率の境目など「階段表」のときです。
近似一致を使う前に、検索範囲が昇順または降順で並んでいるかを確認します。
| 指定 | 意味 | 向いている場面 |
|---|---|---|
| 0 | 完全一致 | コードやID |
| -1 | 次に小さい値 | 段階表の下側 |
| 1 | 次に大きい値 | 段階表の上側 |
| 2 | ワイルドカード | 部分一致したい |
ワイルドカードは便利ですが、誤一致も増えるので、マスタ参照では慎重に使います。
部分一致を使うときは、候補が複数にならないルールがあるかも確認します。
ワイルドカードは「似た名前の商品」などで意図せず別データに当たることがあります。
検索モード（第6引数）：どこから探す？
検索モードは「上から探すか」「下から探すか」を決める設定です。
重複があるデータで「最後に更新された行を返したい」ときに効きます。
履歴テーブルのように同じキーが増え続ける場合は、下から検索が実務に合います。
| 指定 | 意味 | 向いている場面 |
|---|---|---|
| 1 | 先頭から検索 | 通常はこちら |
| -1 | 末尾から検索 | 最新優先 |
| 2 | 二分探索（昇順） | 並びが保証されるとき |
二分探索は速いですが、並びが崩れると結果が壊れるので、実務では慎重に使います。
二分探索を使うなら、並び順を崩さない運用と、崩れたときの検知手段が必要です。
よくあるミスと回避
一致モードと検索モードを同時にいじると、原因の切り分けが難しくなります。
まずは第4引数までで期待の挙動を作り、必要になってから第5と第6を追加します。
重複がある表で「上から」か「下から」かを決めずに式を書くと、仕様が固まりません。
近似一致を使うのに、検索範囲の並びがランダムなままだと結果が不安定になります。
ワイルドカードを使う前に、候補が複数ヒットしたときの扱いを決めておくと安全です。
VLOOKUPとの違いと置き換えの注意点
XLOOKUPはVLOOKUPの上位互換に見えますが、置き換えには注意点もあります。
VLOOKUPの癖に慣れているほど、同じ感覚で置き換えると想定外の結果になることがあります。
違いを理解してから移行すると、式が短くなるだけでなく、保守も楽になります。
結果が正しく見えるだけでなく、後から列が増えても壊れにくい形にできるのが大きなメリットです。
違い（列方向・柔軟性・複数一致）
VLOOKUPは検索列の右側しか返せませんが、XLOOKUPは左側も返せます。
そのため、表の並びを変えずに参照でき、列の入れ替えや追加にも強くなります。
VLOOKUPは列番号で返す列を指定しますが、XLOOKUPは返す範囲を直接指定します。
列番号を数えないで済むので、列を挿入したときに「列番号がズレて別の列を返す」事故を減らせます。
XLOOKUPは「見つからないとき」を引数で扱えるので、IFERRORだらけになりにくいです。
さらに、下から検索する設定を使えば、重複データで「最後の一致」を取りやすいのも違いです。
置き換え時の注意
VLOOKUPの検索範囲は「左端列から」の癖があるので、そのままコピペ置換は危険です。
特に、VLOOKUPで「表全体」を指定していた場合、XLOOKUPでは検索範囲と返す範囲を分けて指定し直します。
XLOOKUPでは検索範囲と返す範囲の行数が一致していないとエラーになります。
列全体参照と途中からの参照が混ざるとズレやすいので、どちらかに統一すると安全です。
また、VLOOKUPで近似一致を使っていた表は、XLOOKUPでも一致モードの指定を明示しないと意図が変わることがあります。
テーブル化しておくと列追加に強くなるので、移行のタイミングで整備すると効果が大きいです。
移行後は、代表データで数件だけでなく、外れ値や未登録データでも結果を確認すると安心です。
エラー対策と「0を表示しない」表示調整
XLOOKUPは便利ですが、エラーや表示のクセを放置すると資料が読みにくくなります。
さらに、エラーが混じった状態で集計やグラフを作ると、後工程で手戻りが増えます。
ここでは「症状→原因→対処」の順で、再現しやすい形にまとめます。
まずはエラーの意味を言葉で押さえ、次に式で対処する流れにします。
症状→原因→対処：#N/A / #VALUE! / スピル
#N/Aは「見つからない」が原因なので、第4引数で表示を決めるのが最短です。
= XLOOKUP(A2, 商品表[商品コード], 商品表[単価], "")「見つからない」と「空白」は意味が違うので、用途に合わせて空白かメッセージを選びます。
例えば入力漏れを目立たせたいなら、空白ではなく「未登録」などの文字を返す方法もあります。
#VALUE!は、検索範囲と返す範囲のサイズがズレているときに起きやすいです。
検索範囲と返す範囲は、同じ行数の1列同士にそろえるのが基本です。
列全体参照と部分参照が混ざるとズレやすいので、どちらかに統一するとミスが減ります。
スピルエラーは、複数セルに結果を出そうとしたのに、出力先が埋まっているときに起きます。
配列で返す関数と組み合わせたときは、出力先の空きを確保するのが先決です。
隣のセルに値や見えない空白が入っているだけでも止まるので、まず出力範囲をクリアします。
0だけ空白にする／エラー時に空白
0は「計算結果としての0」なので、未入力の空白とは意味が違います。
0を空白にするときは、0が本当に不要かを確認してからにします。
例えば単価0は「無料」や「未設定」を表すこともあるので、意味を決めずに消すのは危険です。
基本形は、XLOOKUPの結果をIFで包んで0だけ消します。
=IF(XLOOKUP(A2,商品表[商品コード],商品表[単価],0)=0,"",XLOOKUP(A2,商品表[商品コード],商品表[単価],0))同じXLOOKUPを2回書くのが気になる場合は、後のLETを使って1回だけ計算する形に寄せます。
エラー時だけ空白にしたいなら、まず第4引数を空白にして、0は残す方法もあります。
= XLOOKUP(A2, 商品表[商品コード], 商品表[単価], "")この形なら「見つからないときだけ空白」で、0という値はそのまま残せます。
LETで軽量化
同じXLOOKUPを2回書くと、表が大きいときに体感で重くなります。
LETで結果を一度だけ計算して使い回すと、式も読みやすくなります。
=LET(v,XLOOKUP(A2,商品表[商品コード],商品表[単価],0),IF(v=0,"",v))LETを使うと「まずvを計算し、そのvを条件判定に使う」という形になるので、見直しもしやすいです。
0以外にも、表示用の文字列を返したい場合は、IFの部分だけ差し替えれば対応できます。
重複データ・複数条件・2番目以降の取得
重複があるデータは、式の問題ではなく「要件の問題」で詰まりやすいです。
同じ商品コードが複数行にある、履歴が積み上がる、入力が重複する、といった状況では特に起きます。
最初に返したい行を定義し、その定義に合う関数を選ぶと早いです。
「正しい式」を探すよりも「欲しい結果の定義」を先に固める方が、作り直しが減ります。
まず決める：どの行を返す？
重複があるときは「先頭の一致」「最後の一致」「条件で絞った一致」のどれかを決めます。
更新日があるなら最新を優先するのか、マスタなら先頭を優先するのかが分かれます。
同じキーでも、用途によって正解が変わるので、ここが曖昧だと結果に納得できません。
決めずに式だけ工夫すると、後から仕様変更になって作り直しになります。
複数条件の検索（結合キー/補助列の考え方）
複数条件は、条件を1つのキーにまとめて検索するのが基本です。
例えば「支店」と「商品コード」で探すなら、支店列とコード列を結合してキーにします。
= XLOOKUP(F2&G2, 支店列&コード列, 返す列, "")このとき、結合に使う列の表記ゆれがあると一致しなくなるので、余計なスペースや表記の揺れに注意します。
条件が増えて式が読みにくくなるなら、補助列で結合キーを作る方法も有効です。
補助列を作ると、式が短くなり、検索キーの中身も目で確認しやすくなります。
テーブルに補助列を追加しておくと、参照のズレが減って保守が楽になります。
最後に一致した値が欲しい（最新優先）
最新を取りたいときは、検索モードを-1にして下から探します。
= XLOOKUP(A2, 履歴[商品コード], 履歴[単価], "", 0, -1)このとき、履歴が時系列で並んでいる前提が崩れると、最新が取れません。
更新日列で並び替える運用をセットにすると安定します。
また、削除や差し込みで並び順が変わる運用なら、更新日でソートする手順を固定しておくと安全です。
重複を一覧で取りたい（XLOOKUPの限界と代替）
XLOOKUPは基本的に1つの値しか返さないので、複数行を一覧にする用途は苦手です。
「2番目以降」を取りたい場合も、XLOOKUP単体で無理に作るより、先に一覧を作る方が楽なことがあります。
一致する行を全部出したいなら、FILTERで抽出してから表示する方が自然です。
抽出結果を次の計算に使うなら、スピルの出力先を確保して設計します。
抽出一覧をさらに合計や集計に使うなら、FILTERの結果を別の列に展開してからSUM系で処理すると見通しがよくなります。
別シート・別ブック・テーブル参照（構造化参照）
参照先が同じブック内でも、シートが分かれると式の書き方が少し変わります。
参照先をまたぐと、見た目が少し長くなるので、どこを検索してどこを返しているかを意識して書くのがコツです。
壊れにくさを優先するなら、テーブル化と構造化参照が強いです。
列名で読める形にしておくと、後から列が増えたり並びが変わったりしても、式の意図が保ちやすいです。
別シート参照の最短例
別シートSheet2のA列で探してC列を返すなら、次の形になります。
= XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "")この形は「Sheet2のA列でA2を探し、同じ行のC列を返す」と覚えると読み替えが簡単です。
列全体参照は分かりやすいですが、データが巨大だと重くなるので必要なら範囲を絞ります。
例えばSheet2!A2:A5000のように実データの範囲に合わせるだけでも、体感が軽くなることがあります。
シート名にスペースがある場合
シート名にスペースがあるときは、シート名をシングルクォートで囲みます。
= XLOOKUP(A2, '売上 集計'!A:A, '売上 集計'!C:C, "")この書き方を知らないと参照エラーに見えるので、シート名を変えたときはまずここを疑います。
別ブック参照の注意
別ブック参照は、ファイル名や保存場所が変わると参照が切れやすいです。
運用でパスが変わるなら、ブックを同じフォルダに置くなどルールを決めます。
共有ドライブや同期フォルダでは、個人の環境でパス表記が微妙に変わることもあるので注意します。
外部参照は開閉状態で更新挙動が変わることがあるので、共有前に確認します。
他の人に渡す前に「参照先ブックを開いた状態でも閉じた状態でも想定どおりか」を一度チェックすると安心です。
テーブル参照の例とメリット
テーブル「商品表」を使うと、列追加しても式が読み替えられやすいです。
= XLOOKUP([@商品コード], 商品表[商品コード], 商品表[単価], "")構造化参照は列名で読めるので、第三者が見ても意図が伝わりやすいです。
さらに、同じ列名を使って式を横にコピーしても意味が崩れにくいので、入力ミスの予防にもなります。
XLOOKUPで合計する方法（SUM/SUMIFの使い分け）
XLOOKUPは「値を1つ返す」役で、合計はSUM系に任せると整理がきれいです。
まず役割を分けて考えると、式の構造がシンプルになります。
検索と集計を混ぜないと、式が壊れにくくなります。
特に実務では「検索で単価を出す」「数量と掛け算する」「最後に合計する」という流れに分解すると理解しやすいです。
XLOOKUPはあくまで“部品”として使い、合計はSUMやSUMIFに任せるのが基本方針です。
取得結果を合計する（単純合計）
単価を引いて売上を作り、それを合計する流れが分かりやすいです。
たとえば、商品コードから単価をXLOOKUPで取得し、数量と掛け算して売上列を作ります。
その上で、完成した売上列をまとめて合計するのが自然な設計です。
売上列があるなら、合計は単純にSUMで足します。
= SUM(E2:E100)このように列単位で合計する形にしておくと、後から行が増えても範囲を広げるだけで対応できます。
XLOOKUPの中で無理に合計まで完結させようとすると、式が長くなり読みにくくなります。
条件に合う合計はSUMIF/SUMIFSが向く
「支店Aの売上合計」など条件付き合計は、SUMIFやSUMIFSが得意です。
XLOOKUPは1つの値を探す関数なので、「複数行をまとめて足す」という用途には最適ではありません。
条件が1つならSUMIF、複数ならSUMIFSを使うと、式の意図が明確になります。
XLOOKUPで条件付き合計を無理に作るより、専用関数の方が読みやすいです。
= SUMIF(支店列, "A", 売上列)複数条件の場合は、支店と月などを組み合わせてSUMIFSを使います。
検索と集計を役割分担すると、トラブル時の切り分けも簡単になります。
「重い」と感じるときの対策（優先順位つき）
XLOOKUPが重いと感じる原因は、範囲の広さと計算の重複がほとんどです。
特に列全体参照や、何千行にも広がる検索範囲は、毎回の再計算コストが上がります。
また、同じ条件で同じ表を何度も検索していると、見た目以上に処理が積み上がります。
効く順に対策すると、遠回りになりません。
優先順位：範囲を絞る→LET→見直し
列全体参照は便利ですが、データが多いときは範囲指定に変えるだけで軽くなります。
例えば、実データがA2:A5000に収まっているなら、A:AよりもA2:A5000の方が計算量を抑えられます。
「とりあえず列全体」は、データ量が増えたときにボトルネックになりやすいです。
テーブルを使っている場合は、列全体参照よりも構造化参照の方が意図が明確になり、保守もしやすいです。
同じXLOOKUPを複数回書いているなら、LETで一度だけ計算して使い回します。
IFで0だけ空白にする、表示を整える、別の列でも同じ検索結果を使う、という場面ほどLETが効きます。
それでも重いなら、テーブル化やキー列の整理で、検索対象そのものを小さくします。
検索キーがバラついている場合は、TRIMや表記統一をしてヒット率を上げると、無駄な探索が減ることがあります。
最後に、二分探索などの高速設定は前提条件が厳しいので、並び順が保証できるときだけ検討します。
見直しポイントのチェックリスト
・検索範囲と返す範囲は、本当に必要な行数に絞られているか。
・同じXLOOKUPを複数セルで繰り返していないか。
・補助列を使えば式を短くできないか。
・重複データの扱いは仕様として明確か。
計算式だけでなく、表の設計そのものを見直すと一気に軽くなることがあります。
他関数との比較（INDEX/MATCH、FILTER）
XLOOKUPは万能ではないので、目的で使い分けると効率が上がります。
比較しておくと、詰まったときの逃げ道が増えます。
特に社内のExcel環境がそろっていない場合は、最初から代替ルートを知っておくと安心です。
INDEX/MATCHと比べたとき
INDEX/MATCHは古い環境でも動くことが多いのが強みです。
一方で、XLOOKUPは式が短く、見つからないときの扱いも組み込みで書けます。
INDEX/MATCHは「MATCHで位置を探してINDEXで返す」という分業なので、考え方を理解すると応用が広いです。
検索列が左端でなくても動くので、VLOOKUPの制約に困っているときの置き換えにも使えます。
社内の混在環境なら、互換性を優先してINDEX/MATCHを採用する判断もあります。
ただし式が長くなりやすいので、範囲名やテーブルを使って読みやすさを補うと事故が減ります。
FILTERが向くケース
FILTERは一致した行を複数返せるので、抽出一覧を作るときに強いです。
XLOOKUPが「1件だけ返す」のに対して、FILTERは「条件に合う行をまとめて返す」のが得意です。
抽出結果がスピルするため、出力先のレイアウト設計が必要になります。
出力先に既に値があるとスピルエラーになるので、結果が展開できる空白エリアを先に用意します。
重複を一覧で見せたいときは、XLOOKUPよりFILTERが自然です。
抽出した一覧をさらに集計したい場合は、FILTERの結果をSUMやピボットの入力に使うと作業がまとまります。
まとめ
XLOOKUPは基本形を固めた上で、必要なときだけ第5・第6引数を足すと失敗しにくいです。
まずは第1〜第4引数までで「検索して返す」「見つからないときの表示」を安定させるのが近道です。
一致モードや検索モードは便利ですが、要件が決まっていない状態で触ると結果がぶれやすいです。
表示調整は第4引数とIF系、重複や一覧は検索モードやFILTERで役割分担すると整理できます。
特に「0を空白にする」のか「エラーを空白にする」のかは意味が違うので、目的を分けて考えます。
迷ったらここだけ（チェックリスト）
まず第4引数で「見つからないときの表示」を決めると、資料が読みやすくなります。
0を消したいなら、0が本当に不要な値かを確認してからIFで空白にします。
重複があるなら「先頭か最後か一覧か」を決めてから関数を選びます。
最新を取りたい場合は検索モード-1を使い、並び順も運用で固定すると安定します。
重いときは、範囲を絞ることとLETで重複計算を減らすことを先に試します。
それでも改善しない場合は、参照方法をテーブル化するか、目的に合う別関数へ切り替えます。
