本記事では、ExcelでINDEX関数とMATCH関数を組み合わせてデータの特定のセルを動的に参照する方法をご紹介します。基本的な使い方からVLOOKUP、HLOOKUP、XLOOKUPとの違いまで丁寧にご紹介・解説します。
結論
INDEX関数とMATCH関数を組み合わせたエクセルの数式は下記の使い方で実装することができます。
- 1行または1列のセル範囲との組み合わせの使用方法:
=INDEX([値を抽出したい列または行], MATCH([検索値], [検索範囲], 0))
- 複数行 * 複数列のセル範囲との組み合わせの使用方法:
=INDEX([セル範囲], MATCH([行検索値], [検索範囲], 0), MATCH([列検索値], [検索範囲], 0))
INDEXとMATCHの使い方
概要
こちらのセクションでは、はじめにINDEX関数とMATCH関数のそれぞれの使い方を解説します。その上で、1列または1行のときの動作と、複数行*複数列の組み合わせにおける動作をわかりやすく説明します。
クリックで該当セクションにジャンプします。
- ステップ1
- ステップ2
- ステップ3
- ステップ4
- ステップ5
ステップごとの使い方解説
INDEX関数の使い方
まず初めにINDEX関数の使い方をご紹介します。1つ1つの関数の動作を理解することで、INDEXとMACTHの組み合わせの仕組みが理解しやすくなります。INDEX関数はセル範囲における指定された位置のセルを返す関数です。
- 構文:
INDEX(配列, 行番号, [列番号]) - 各引数の説明:
引数 説明 配列必須 参照する範囲または配列 行番号必須 行の番号 列番号省略可 列の番号 - 参考: マイクロフト公式ドキュメント
具体的には、下記の例ではC3:C8で上から3番目のセルを取得しています。この指定方法は1列または1行の場合は、縦横の向きを問わず同じように使用できます。
INDEX関数は1つ目の引数にセル範囲、2つ目の引数にその中で何行目または何列目に位置しているかを指定する、と覚えてください。
MATCH関数の使い方
次に、MATCH関数の使い方を解説します。MATCH関数は、特定の値がセル範囲の中で何番目に位置するかを返す関数です。
- 構文:
MATCH(検索値, 検索範囲, [検索モード]) - 各引数の説明:
引数 説明 検索値必須 検索する値 検索範囲必須 検索を行う範囲 検索モード省略可 一致の種類 - 参考: マイクロフト公式ドキュメント
下記の具体例では、AppleがC3:C8のうち何番目に位置するかを求めています。
MATCH関数は1つ目の引数に検索したい値、2つ目の引数に検索値が含まれるセル範囲、3つ目の引数に0を指定すると覚えてください
INDEXとMATCH関数の組み合わせ方
次に、INDEXとMATCH関数を組み合わせる方法をご紹介します。順番としては下記のように使用します。
- MATCH関数で検索値が基準列の中の何行目に位置しているかを確認
- INDEX関数で取得したい列の中から、MATCH関数で取得した行番号を指定する
以下に、実際の計算ステップを説明します。
=INDEX(E$3:E$8, MATCH($D11, $C$3:$C$8, 0)) # 元の数式
=INDEX(E$3:E$8, MATCH("Google", $C$3:$C$8, 0)) # ステップ1
=INDEX(E$3:E$8, 2) # ステップ2
=141.22 # 結果の出力INDEXとMATCH関数の組み合わせは、MATCH関数が出力した範囲内での位置をもとに、INDEX関数が任意のセル範囲から同じ位置にあるセルを取得します。
- 通常横にコピーして使うことが多いため、絶対参照の使い方に気をつけてください
- VLOOKUP、HLOOKUP、XLOOKUPとの違いは、検索用の基準列と抽出したい列を分けて使用するかどうかです。下記はVLOOKUPを例に使い方の違いを図示しています
複数行*複数列での使い方
少し発展的な使い方として、INDEX関数は複数行*複数列のセル範囲にも使用する事ができます。これを踏まえた使い方は下記のとおりです。ここでは、INDEX関数の1つ目の引数で行位置、2つ目の引数で列位置を指定していますが、基本的な使い方は先程までと変わりありません。
計算ステップは下記のとおりです。
=INDEX(C3:G8, MATCH(I6, C3:C8, 0), MATCH(I5, C3:G3, 0))
=INDEX(C3:G8, 2, MATCH(I5, C3:G3, 0))
=INDEX(C3:G8, 2, 3)
=141.22(応用編) 複数行*複数列で行または列全体の抜き出し方
ここからは完全に応用編です。INDEX関数のさらなる使い方として、スピルをサポートするバージョンではINDEX関数の行または列番号に0を指定することができます。0を指定した場合、[すべての位置]を返すことになります。
具体的な使い方としては下記の通りで、ここでは[行番号]に0を指定することで、2023年10月の列全体を出力しています。
=INDEX(C3:G8, 0, MATCH(I5, C3:G3, 0))
=INDEX(C3:G8, 0, 3)
={"2023年10月";141.22;182.34;330.54;134.48;346.2}
おわりに
以上が、ExcelにおけるINDEXとMATCHを組み合わせた関数・数式の使い方の解説となります。
ご質問やご不明点がある場合はお気軽にコメントお待ちしております。
ご精読いただきありがとうございました。
コメント