Excel関数の組み合わせについてお探しですね。

広告

ExcelでINDEX＋MATCHを使った複数条件検索をマスターしよう

「部署と商品名が両方一致する行の売上だけを取り出したい」「同じ名前が何度も出てくる表から、条件に合うデータだけを見つけたい」──Excelで仕事をしていると、こんな場面によく出くわします。

VLOOKUPは便利ですが、複数の条件を同時に扱うのは苦手です。

そこで活躍するのが、INDEX関数とMATCH関数を組み合わせた複数条件検索です。

さらに、古いExcelで使う配列数式（CSE）と、Microsoft 365やExcel 2021以降で使えるスピルという機能を理解すると、検索だけでなく、条件に合うデータをまとめて抽出する処理まで、関数だけで実現できるようになります。

INDEX＋MATCHの複数条件検索って何？

INDEX＋MATCHは、指定した位置にあるデータを取り出すINDEX関数と、探している値が何行目にあるかを調べるMATCH関数を組み合わせる検索方法です。

VLOOKUPは基本的に、検索する列が表の一番左にないと使えませんが、INDEX＋MATCHならそんな制限はありません。

検索列と取得列がどこにあっても大丈夫なので、実務では「社員番号から氏名を探す」だけでなく、「店舗・商品・月がすべて一致する売上を見つける」といった複数条件の検索にも応用できます。

特に、古いバージョンのExcelでも使えるのが大きな強みです。

VBAやマクロが禁止されている職場でも、気軽に導入できます。

複数条件検索の基本的な考え方は、いくつかの条件判定を掛け算して、すべてがTRUE（正しい）になる行だけを「1」として扱うことです。

たとえば、A列に店舗名、B列に商品名、C列に月、D列に売上が入っている表があるとします。

店舗・商品名・月がすべて一致する売上を取り出すには、それぞれの条件が合っているかを配列（複数の値をまとめたもの）として判定します。

数式の例はこんな感じです。

“`excel
=INDEX($D$2:$D$100,MATCH(1,($A$2:$A$100=H2)*($B$2:$B$100=H3)*($C$2:$C$100=H4),0))
“`

この式では、H2に店舗名、H3に商品名、H4に月を入力しておいて、それらと一致する行のD列の値を返します。

`($A$2:$A$100=H2)`のような比較は、各行ごとにTRUEかFALSEを返します。

ExcelではTRUEを1、FALSEを0として扱えるので、3つの条件を掛け算すると、すべての条件に一致した行だけが1になります。

MATCH関数がその1の位置を探して、INDEX関数が同じ位置にある売上を返す、という流れです。

古いExcelで使う配列数式（CSE）の考え方と注意点

Excel 2019より前のバージョンや一部の古い環境では、さっきのように範囲同士を比較する数式は、普通にEnterキーを押すだけでは正しく計算されないことがあります。

そんなときに必要になるのが、Ctrl＋Shift＋Enterで確定する配列数式です。

CSEというのは、Ctrl、Shift、Enterの頭文字を取った呼び方で、複数セル分の計算を1つの数式の中でまとめて処理するための入力方法です。

数式を入力したあとにCtrl＋Shift＋Enterで確定すると、数式バーには波かっこ付きの `{= … }` のように表示されますが、この波かっこを自分で手入力しても同じ動作にはなりません。

CSEを使う場合でも、数式の中身自体は基本的に同じです。

ただし、古いExcelでは処理が重くなりやすいので、参照範囲を列全体にするのは避けたほうが無難です。

たとえば`A:A`や`D:D`のような列全体の参照を配列数式で使うと、100万行以上を毎回チェックすることになって、ファイルの動作が遅くなる原因になります。

実務では、`A2:A1000`のように必要な範囲だけを指定するか、テーブル機能を使ってデータ範囲を管理すると、読みやすさと処理速度の両方で安定します。

また、複数条件検索では「該当なし」の処理も大事です。

条件に一致する行が存在しない場合、MATCH関数は`#N/A`というエラーを返します。

そのままでも原因は分かりますが、帳票や共有ファイルではエラー表示が混乱を招くことがあります。

実務用の数式にするなら、次のようにIFERRORで包むと見やすくなります。

“`excel
=IFERROR(INDEX($D$2:$D$100,MATCH(1,($A$2:$A$100=H2)*($B$2:$B$100=H3)*($C$2:$C$100=H4),0)),”該当なし”)
“`

ただし、IFERRORはすべてのエラーをまとめて処理してしまいます。

範囲の指定ミスや列の削除によるエラーまで隠れてしまう可能性があるので、作成直後の検証段階ではIFERRORを外して原因を確認して、完成後に表示調整として追加するのが安全です。

古いExcelのCSEは強力ですが、入力方法を忘れると数式が壊れたように見えるので、共有する場合はセルの近くに「配列数式のためCtrl＋Shift＋Enterで確定」とメモを残しておくと、あとで困りません。

スピルを使った新しいExcelでの高度な抽出

Microsoft 365やExcel 2021以降では、動的配列に対応しているので、配列数式をCtrl＋Shift＋Enterで確定する必要がありません。

普通のEnterで入力しても、計算結果が複数セルに自動で広がる仕組みを「スピル」と呼びます。

INDEX＋MATCHの複数条件検索も、基本的にはそのままEnterで動くことが多く、古いExcelより扱いやすくなっています。

特に、条件に合う1件だけを返す検索なら、CSEを意識せずに同じ構造の数式を使えるので、引き継ぎや修正の負担が小さくなります。

スピルの本当のすごさは、1件検索ではなく「条件に合う複数行をまとめて抽出する」場面で発揮されます。

たとえば、店舗と商品名に一致する明細を、日付・数量・売上の3列まとめて取り出したい場合、FILTER関数が使える環境ならとてもシンプルに書けます。

“`excel
=FILTER($C$2:$E$100,($A$2:$A$100=H2)*($B$2:$B$100=H3),”該当なし”)
“`

この式はINDEX＋MATCHそのものではありませんが、複数条件の配列判定という考え方は同じです。

条件に合う行が複数あれば、結果が下方向・右方向へ自動的に広がります。

古いExcelで同じことをやろうとすると、SMALL関数やROW関数を組み合わせて複数行を1件ずつ取り出す必要があって、数式がかなり長くなりがちです。

なので、新しいExcelでは「単一結果の検索はINDEX＋MATCH、複数件の抽出はFILTER」と役割分担すると、実務上は分かりやすくなります。

INDEXを使ってスピル抽出に近い形を作ることもできます。

たとえば、条件に一致する行番号だけをFILTERで取り出して、その行番号をINDEXに渡すと、指定した列範囲から該当行をまとめて返せます。

“`excel
=INDEX($C$2:$E$100,FILTER(ROW($A$2:$A$100)-ROW($A$2)+1,($A$2:$A$100=H2)*($B$2:$B$100=H3)),SEQUENCE(,3))
“`

この式では、FILTERで条件に合う相対行番号を作って、INDEXがC列からE列までのデータを返します。

SEQUENCE関数は列番号の配列を作るために使っていて、ここでは1列目から3列目までを横方向に返す役割です。

ちょっと上級者向けですが、INDEXを軸にした抽出ロジックを維持したい場合や、返す列を柔軟にコントロールしたい場合に役立ちます。

実務で失敗しない設計と使い分けのコツ

INDEX＋MATCHの複数条件検索を実務で安定して使うには、数式そのものよりも、表の設計を整えることが大切です。

検索対象の列に余分な空白が混ざっていたり、日付が文字列として保存されていたりすると、見た目は同じでも一致判定では別物として扱われます。

特に、外部システムから出力したCSVや基幹システムの帳票データでは、末尾のスペース、全角半角の違い、日付形式の不一致がよく起こります。

数式が正しいのに結果が返ってこない場合は、まず条件列のデータ型と余分な文字を確認するのが近道です。

複数条件検索では、同じ条件に一致する行が複数ある場合の扱いも事前に決めておく必要があります。

MATCH関数は最初に見つかった1件だけを返すので、同じ店舗・同じ商品・同じ月のデータが複数行あると、2件目以降は無視されます。

これは仕様であって、エラーではありません。

最初の1件だけでいいのか、合計したいのか、明細をすべて抽出したいのかによって、使う関数を変える必要があります。

1件検索ならINDEX＋MATCH、合計ならSUMIFS、複数明細の抽出ならFILTERやスピル対応のINDEX式を選ぶと、目的に合った設計になります。

バージョンによる使い分けも大事です。

Excel 2016や2019を含む古い環境では、CSEによるINDEX＋MATCHが有力な選択肢です。

一方で、Microsoft 365やExcel 2021以降では、スピルやFILTER関数を活用したほうが、数式が短くて、更新にも強い構成にできます。

職場で複数バージョンのExcelが混在している場合は、全員が使える古い方式で作るのか、最新版の機能を前提に効率化するのかを先に決めておくと、ファイル共有時のトラブルを防げます。

最後に、数式を長くしすぎない工夫も欠かせません。

複数条件が4つ、5つと増えてくると、INDEX＋MATCHの式は読みづらくなります。

そんなときは、作業列に結合キーを作って検索する方法や、LET関数で条件範囲に名前を付けて整理する方法が有効です。

たとえば、店舗・商品・月を結合したキー列を作れば、MATCHは1つの条件だけを見る形になって、古いExcelでも保守しやすくなります。

関数だけで高度な抽出を実現するには、難しい式を一発で書こうとするよりも、検索条件、返す範囲、該当なし処理、バージョン対応を分けて考えることが成功のポイントです。

広告