doodle-on-web

自分で調べたことや、仕事の中で質問されたことなどをまとめています。

【Excel上級】3列同時マッチングを1式で解決！MMULT活用のINDEX×MATCH完全解説

Excel 関数 データ分析 業務効率化 スプレッドシート

スポンサーリンク

この記事の対象読者： INDEX×MATCHの基本は知っているが、複数列への同時マッチングで行き詰まった経験のある中〜上級Excelユーザー。この記事を読めば、条件列が何列に増えても崩れない、保守性の高い検索式が書けるようになります。

Excelで「複数の列すべてに条件が一致する行を検索したい」という場面、ありませんか？

条件が増えるたびに式が膨れ上がり、1か月後に自分で見返しても何をしているか分からなくなった——そんな経験はないでしょうか。今回はその悩みをすっきり解消する方法として、MMULT・COLUMN・TRANSPOSE・INDEXを組み合わせた上級テクニックを、実例とともに丁寧に解説します。

なぜ複数列マッチングは難しいのか

通常のINDEX×MATCHは「1列に対して1条件」という構造です。

=INDEX(返す列, MATCH(検索値, 検索列, 0))

複数条件を扱うには、*演算子でつなぐ配列数式がよく使われます。

{=INDEX(D:D, MATCH(1, (A:A=G1)*(B:B=G2)*(C:C=G3), 0))}

条件が2列のときはまだ読めます。しかし3列、4列と増えると……

{=INDEX(D:D, MATCH(1, (A:A=G1)*(B:B=G2)*(C:C=G3)*(D:D=G4)*(E:E=G5), 0))}

このように式が横に際限なく伸び、条件の追加・削除のたびに式全体を触り直す必要が生じます。管理コストが高く、ミスも起きやすくなります。

そこで登場するのが MMULT を活用した方法です。条件列が増えても、変更箇所が最小限で済む構造になっています。

サンプルデータを用意しよう

以下のような売上テーブルを想定します。

A（地域） B（商品） C（担当者） D（売上）
東京 りんご 田中 120,000
大阪 みかん 鈴木 85,000
東京 みかん 田中 95,000
大阪 りんご 山田 110,000
東京 りんご 鈴木 75,000

目標：「地域＝東京」かつ「商品＝りんご」かつ「担当者＝田中」の売上を取得する

検索条件はG1〜G3セルに入力されているとします（G1：東京、G2：りんご、G3：田中）。

MMULT + COLUMN + TRANSPOSE による解法

数式の全体像

=IFERROR(
  INDEX(D2:D6,
    MATCH(
      3,
      MMULT(
        (A2:C6=TRANSPOSE(G1:G3))*1,
        COLUMN(A:C)^0
      ),
      0
    )
  ),
  "該当なし"
)

複雑に見えますが、内側から1ステップずつ分解すると論理は明快です。

ステップごとに分解して理解する

① 条件との比較：(A2:C6=TRANSPOSE(G1:G3))*1

(A2:C6 = TRANSPOSE(G1:G3)) * 1
  • A2:C6 は5行×3列の範囲
  • G1:G3 は縦方向（3行×1列）のリスト
  • TRANSPOSE(G1:G3) で横方向（1行×3列）に変換

Excelは各行の各列を、それぞれ対応する条件と比較し、一致すれば1・不一致なら0の5行×3列の行列を生成します。

1 1 1   ← 東京・りんご・田中 → すべて一致
0 0 0   ← 大阪・みかん・鈴木
1 0 1   ← 東京・みかん・田中 → B列が不一致
0 1 0   ← 大阪・りんご・山田
1 1 0   ← 東京・りんご・鈴木 → C列が不一致

② 行ごとの一致数を集計：MMULT(..., COLUMN(A:C)^0)

MMULT(行列, COLUMN(A:C)^0)
  • COLUMN(A:C) は {1, 2, 3} という横方向の配列
  • ^0（0乗）を付けることで {1, 1, 1} に変換

MMULTは行列の積を計算する関数で、ここでは「各行の値を{1,1,1}と掛けて合計する」、つまり行ごとの一致数の合計を求めています。

結果は縦方向の配列になります：

{3; 0; 2; 1; 2}

すべての列で一致した行だけが 3（＝条件の数）になります。

COLUMN(A:C)^0 vs {1;1;1} の使い分け {1;1;1} と直接書いても動作しますが、条件列の数が変わったときに手動で更新する必要があります。COLUMN(A:C)^0 は参照範囲を変えるだけで自動追従するため、保守性が高く実務向きです。

③ 行番号を特定：MATCH(3, ..., 0)

MATCH(3, {3;0;2;1;2}, 0)

値 3（＝条件の数）が最初に現れる位置、つまり 1行目 が返ります。

④ 値の取得：INDEX(D2:D6, 1)

INDEX(D2:D6, 1)

D列の1行目、すなわち 120,000 が取得されます。

⑤ エラー対策を忘れずに：IFERRORでラップ

一致する行が存在しない場合、MATCHは#N/Aエラーを返します。実務では必ずIFERRORでラップして、ユーザーフレンドリーな表示にしましょう。

=IFERROR(INDEX(...), "該当なし")

これだけで、条件に合うデータがなかった場合も安全に処理できます。

【応用編】複数件ヒットにすべて対応するには？

上記の式は「最初の一致行のみ」を返します。複数件ある場合にすべて取得したいなら、Excel 365の FILTER 関数が強力です。

=IFERROR(
  FILTER(
    D2:D6,
    MMULT((A2:C6=TRANSPOSE(G1:G3))*1, COLUMN(A:C)^0) = 3
  ),
  "該当なし"
)

一致するすべての売上を縦に並べて返すことができます。なお、このFILTER関数はExcel 365・2021以降でのみ使用可能です。

旧バージョンExcelでの注意点

Excel 2019以前では、MMULTを含む配列数式は CTRL+SHIFT+ENTER で入力する必要があります。入力後に数式が{}で囲まれていることを確認してください。

Excel 365・2021では通常の ENTER で動的配列として処理されます。

まとめ：各関数の役割を整理する

関数・構文 役割
TRANSPOSE 検索条件を横向きに変換し、行列比較を可能にする
比較演算 × 1 各セルと条件を0/1の行列で表現する
MMULT 行ごとの一致数を集計する
COLUMN(A:C)^0 条件数に自動追従する1ベクトルを生成する
MATCH 全条件一致行（値＝条件数）の位置を特定する
INDEX 対象の値を返す
IFERROR 一致なし時のエラーを安全に処理する

複数列の条件マッチングは最初こそ難解に見えますが、各関数の役割を分解して理解すると、非常に論理的な構造をしていることがわかります。MMULTを使いこなせるようになると、条件列が増えても式の構造を変えずに対応できるため、複雑な集計・検索が一気にシンプルになります。

ぜひ自分のデータに当てはめて、まずは2列の条件から試してみてください！

【Lookupシリーズ】の他の記事もあわせてどうぞ。