INDEXとMATCHの組み合わせ方|VLOOKUPでできないことができる使い方を図解で解説

教員向け 最高点の子の名前をINDEXとMATCH 検索・参照関数

テストの表から、最高点を取った子の名前を探したい。ところが、名前の列が、点数の列より左にある。教員の仕事では、数字から名前を探す場面があります。

INDEX(インデックス)関数とMATCH(マッチ)関数を組み合わせると、左の列の値も取り出せます。この記事では、教員の仕事でよく出る例を使って、組み合わせ方を説明します。

教員の仕事でINDEXとMATCHを使う場面|最高点の子の名前を出す

表の上のアルファベット(A、B、C…)が列、左の数字(1、2、3…)が行です。「B2」は、B列の2行目のセルのことです。

AB
1氏名算数
2青木72
3伊藤88
4上田60
=INDEX(A2:A4,MATCH(MAX(B2:B4),B2:B4,0))

MAXで最高点を出し、MATCHで、その点数がある行の位置を探し、INDEXで、その行の氏名を取り出します。上の表では「伊藤」と出ます。

ここからは、INDEXとMATCHの組み合わせ方を順に見ていきます。

INDEXとMATCHの役割

まず、2つの関数をそれぞれ見てみましょう。

  • MATCH関数:検索値が範囲の「何番目」にあるかを返します。=MATCH(検索値,検索範囲,0)
  • INDEX関数:範囲の「何番目」の値を返します。=INDEX(範囲,番号)

MATCHで調べた番号を、そのままINDEXに渡すことで、検索ができます。末尾の「0」は完全一致で探す指定です。

基本の使い方|商品コードから商品名を探す

MATCH関数で商品コードの位置を調べ、INDEX関数でその位置の商品名を取り出す式を示した図
  1. 結果を表示したいセル(E2)を選び「=INDEX(」と入力する
  2. 取り出したい商品名の範囲(B2:B5)をドラッグして選び、カンマを入れる
  3. 「MATCH(」と入力し、検索値(D2)、検索範囲(A2:A5)、「0」をカンマで区切って入力する
  4. 「))」で2つの括弧を閉じて Enter を押す

VLOOKUPでできない|検索値より左の列を取り出す

VLOOKUPは、検索値の列より右の列しか取り出せません。INDEXとMATCHなら、検索の列と取り出す列を別々に指定するので、左側の列も取り出せます。

商品名から、左の列にある商品コードをINDEXとMATCHで取り出す式を示した図

行と列の2方向で検索する

INDEX関数は、行番号と列番号の2つを指定すると、表の交点の値を取り出せます。行と列をそれぞれMATCHで調べれば、表の見出しから値を探せます。

=INDEX(B2:D5,MATCH(F1,A2:A5,0),MATCH(F2,B1:D1,0))

F1に行の見出し(商品名など)、F2に列の見出し(月など)を入力すると、2つの見出しが交わるセルの値が表示されます。

VLOOKUPとの使い分け

比較項目VLOOKUPINDEX+MATCH
左の列の取得できないできる
列を挿入したとき列番号がずれることがある式が崩れにくい
式の短さ短いやや長い
初心者への分かりやすさ分かりやすい慣れが必要
2方向の検索難しいできる

もう一歩進める|MATCH関数の照合の型と、あいまい検索

INDEXとMATCHの組み合わせでは、MATCH関数の3つ目の引数「照合の型」が結果を左右します。ここを正しく指定できると、使える場面がぐっと広がります。

照合の型の3種類

  • 0:完全一致。完全に同じ値を探す(ほとんどの場面でこれを使う)
  • 1:検索値以下で最大の値を探す(範囲が昇順に並んでいる必要がある)
  • -1:検索値以上で最小の値を探す(範囲が降順に並んでいる必要がある)

省略すると「1」になるため、完全一致で探したいのに「0」を書き忘れて、違う行を返してしまうミスが多く見られます。迷ったら「0」と書いておきましょう。

ワイルドカードで部分一致検索

完全一致(0)のときは、ワイルドカードが使えます。商品名の一部だけが分かっているときは、次のように書きます。

=INDEX(B2:B100,MATCH("*"&E1&"*",A2:A100,0))

E1に入力したキーワードを含む、最初の行が見つかります。

複数条件で探すとき

2つの条件がそろう行を探すときは、検索値と検索範囲をそれぞれ「&」でつなぎます。Microsoft 365やExcel 2021では、そのまま入力して確定できます。古いバージョンでは、「Ctrl」+「Shift」+「Enter」で確定する必要があります。

INDEXとMATCHを使うときの注意点

INDEXとMATCHの組み合わせは柔軟ですが、2つの関数の範囲がずれていると、違う行の値を返してしまいます。

  • INDEXの範囲とMATCHの範囲は、同じ開始行・同じ行数にそろえる(A2:A100とB2:B100のように)
  • 範囲は絶対参照($A$2:$A$100)にして、コピーしてもずれないようにする
  • 見つからないときの#N/Aは、IFERROR関数で「該当なし」などの表示に置き換える

VLOOKUPは列番号を数字で指定するため、表の途中に列を挿入すると結果がずれることがあります。INDEXとMATCHは列そのものを範囲で指定するので、列を挿入しても壊れにくいのが大きな利点です。また、検索する列を並べ替えておく必要もありません。

迷ったときの選び方

どの関数を使うか迷ったときは、次の目安で選ぶと分かりやすくなります。

  • 検索する値が表の左端にあり、列の追加も少ない:VLOOKUPで十分
  • 検索する値が表の右側にある、列をよく挿入する:INDEXとMATCH
  • Microsoft 365やExcel 2021以降を使っている:XLOOKUPがもっとも書きやすい

自分の使っているExcelのバージョンと、表の作りを見て、書きやすい方法を選びましょう。

INDEXとMATCHを使うときの注意

  • 同じ最高点の子が複数いると、一番上の子の名前だけが出ます。
  • 範囲の行数が、そろっているかを確認します。

あわせて読みたい

コメント

タイトルとURLをコピーしました