VLOOKUPを超える：INDEX・MATCHで複数条件＋縦横検索を完全マスターする

VLOOKUPを超える：INDEX・MATCHで複数条件＋縦横検索を完全マスターするのサンプル ▲ Lookup サンプル

VLOOKUPの「もう1つ条件を追加したい」に応える

Excelで「商品名と店舗名の両方が一致する売上を取り出したい」という場面に出くわしたことはないでしょうか。

VLOOKUPで何とかしようとしてヘルパー列を追加したら、表がどんどん横に広がってしまった。IFERRORで誤魔化してみたが、そもそも複数条件を渡せない。そういった経験をした方は多いはずです。

この記事を読めば、複数条件での縦方向検索と、行・列を両方動的に指定する横断検索の2つが自在に使えるようになります。コピペできるサンプル数式と仕組みの解説を合わせて提供するので、手元のデータにすぐ応用できます。

INDEX・MATCHの基本をおさらい

まず構造を確認しておきましょう。

=INDEX(返したい範囲, MATCH(検索値, 検索範囲, 0))

MATCH → 検索値が何行目にあるかを返す
INDEX → その行番号に対応する値を返す

VLOOKUPと異なり、返す列が検索列の左側にあっても問題ありません。これだけでも使う価値があります。

XLOOKUPとの関係： Excel 365以降ではXLOOKUPが使えますが、複数条件の指定は単体では難しく、古いバージョンとの互換性も保てません。複数条件が絡む場面では、INDEX・MATCHの配列数式が最も安全で汎用性の高い選択肢です。

複数条件でのLookup（AND条件）

数式の形

=INDEX(C2:C100, MATCH(1, (A2:A100=F1)*(B2:B100=F2), 0))

入力方法： Excel 365 / 2021以降はそのままEnterでOK。それ以前のバージョンは Ctrl + Shift + Enter で配列数式として確定する必要があります。通常のEnterで入力した場合、#N/Aエラーや意図しない値が返ることがあるので注意してください。

サンプルデータ

列A（商品名）	列B（店舗名）	列C（売上）
りんご	新宿店	12,000
りんご	渋谷店	8,500
みかん	新宿店	6,200

F1に「りんご」、F2に「渋谷店」と入力すると 8500 が返ります。

なぜ「1を探す」のか

(A2:A100=F1) はTRUE/FALSEの配列を返し、TRUEは1・FALSEは0として扱われます。掛け算することで両方が一致した行だけが1になるAND条件になります。

(A2:A100=F1)  → {1,1,0,0,...}   ← りんごの行
(B2:B100=F2)  → {0,1,0,0,...}   ← 渋谷店の行
積             → {0,1,0,0,...}   ← 両方一致は2行目だけ

MATCHは最初の1の位置を探し、その行番号をINDEXに渡します。

OR条件でのLookup

AND条件が掛け算なら、OR条件は足し算で表現します。

=INDEX(C2:C100, MATCH(1, ((A2:A100=F1)+(A2:A100=F3))*(B2:B100=F2), 0))

「りんごまたはバナナ、かつ渋谷店」といった条件が書けます。

ただし、この数式は最初にヒットした1件のみを返します。同じ条件に複数行が該当する場合、2件目以降は取得できません。全件取得が必要なときはSMALL・IF関数の組み合わせやPower Queryが現実的な選択肢です。

縦横どちらも検索できるLookup

横方向（列方向）の検索

月別に列が並ぶデータなど、横展開された表では列方向を検索します。

=INDEX(B2:Z2, MATCH(F1, B1:Z1, 0))

F1に「4月」と入れると、4月の列に対応する値が返ります。

行・列の両方を動的に指定する

INDEXは行番号と列番号の両方をMATCHで指定できます。

=INDEX(B2:Z10, MATCH(F1, A2:A10, 0), MATCH(F2, B1:Z1, 0))

	1月	2月	3月
東京	100	120	110
大阪	80	90	95

F1に「大阪」、F2に「2月」と入れれば 90 が返ります。行と列の両方をセル参照で切り替えられるため、ドロップダウンと組み合わせたダッシュボード作成にも使えます。

複数条件 × 縦横検索の組み合わせ

複数条件で行を特定し、さらに列も動的に選ぶ最終形です。

=INDEX(C2:E100,
  MATCH(1, (A2:A100=G1)*(B2:B100=G2), 0),  ← 行を複数条件で特定
  MATCH(G3, C1:E1, 0)                        ← 列見出しで特定
)

G1：検索条件1（例：商品名「りんご」）
G2：検索条件2（例：店舗名「渋谷店」）
G3：取得したい列の見出し（例：「売上」「在庫」「単価」）

G3を変えるだけで同じ行の別データを取得できるため、属性が複数ある商品マスタや在庫管理に便利です。

実務での活用シーン

シーン	使うテクニック
社員名と日付で勤怠を検索	AND条件MATCH
月別・支店別の売上取得	行列両方をMATCH
複数SKUから指定属性を取得	複数条件＋列MATCH
月次レポートの自動参照	列方向MATCH

まとめと次のステップ

複数条件のLookupは MATCH(1, (条件1)*(条件2), 0) の掛け算で実現
縦横検索はINDEXの行・列番号を両方MATCHで動的に指定する
複数条件と縦横検索を組み合わせれば、ほぼすべての表引きに対応できる

まず試すなら： 手元の表でAND条件の数式を1つ作り、配列の展開図を頭に思い浮かべながら動作を確認してみてください。「なぜ1を探すのか」が腑に落ちれば、あとはMATCHの引数を増やすだけで応用が広がります。慣れてきたらIFERRORで見つからない場合の処理を加えたり、INDIRECTで範囲そのものを動的に変えたりする方向へ発展させてみましょう。

doodle-on-web

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