エクセルで条件に一致するデータを抽出するメジャーな方法としてはVLOOKUP関数やXLOOKUP関数を使う方法が有名だが、INDEX関数とXMATCH関数を使ってより高度なデータ抽出ができます。
INDEX関数とXMATCH関数を組み合わせて使えば、これらの問題を解決することができます。
Excel VLOOKUP関数の列番号をXMATCH関数で簡単に指定する
ExcelのXLOOKUP関数でデータ処理をする｜VLOOKUP関数, HLOOKUP関数の上位互換
INDEX関数の使い方
まずは、INDEX関数の使い方を押さえておきましょう。
=INDEX(配列, [行番号], [列番号])
※行番号か列番号のいずれかは必須
配列：セルの配列を指定する
行番号：配列の値を返す行を数字で指定する。
列番号： 配列の値を返す行を数字で指定する。
第１引数の配列で指定した範囲の先頭を１として、第二引数で行番号、第3引数で列番号を指定したセルの値を返します。
下の会社と製品の価格のマトリックス表のF社の製品Cの商品の価格を抽出してみます。
INDEX関数の第1引数に表全体（B2:F11）を指定します。
F社は第１引数で指定した配列の6番目の行なので第2引数に行番号「6」を指定します。
製品Cは第１引数で指定した配列の３番目の列なので第3引数に列番号「3」を指定します。
INDEX関数はF社の製品Cの価格である「3900」を抽出しました。
XMATCH関数でINDEX関数の列番号、行番号を指定する方法
INDEX関数の第二引数で行番号、第3引数で列番号は数値で指定する必要がありますが、XMATCH関数などで行番号や列番号を算出して使うのが便利です。
Excel XMATCH関数で条件に一致するセルの位置を特定する
XMATCH関数で行番号を指定する方法
まずは、第２引数の行番号をXMATCH関数で指定します。
第２引数をXMATCH関数を使ってF社の行番号を自動で算出した。結果は直接行番号を入力したのと同じとなっている。
参考までに第２引数のXMATCH関数の結果も示す。想定通りに「６」となっている。
これは、XMATCH関数を使うことで第２引数は数字でなく検索値で指定することができている。これはVLOOKUP関数と同じ作業ができていることになる。
XMATCH関数で行番号と列番号を指定する方法
行だけでなく列もXMATCH関数で指定した例を下図に示す。
INDEX関数で抽出したい値は、引数に行番号・列番号を数値で直接入力したときと同じ結果となっている。
参考までに第２引数・第3引数のXMATCH関数の結果も示す。想定通りに「６」と「３」となっている。
これは、XMATCH関数を使うことで第２引数と第３引数は数字でなく検索値で指定することができている。これはVLOOKUP関数とXMATCH関数を組み合わせて使用したときと同じ作業ができていることになる。
行と列の両方向をスピル機能を使う
下図のようにXMATCH関数の第一引数の検索値を範囲で指定することでスピル機能を行と列を同時に使用することができ、一気に値を抽出することができます。
VLOOKUP関数・XLOOKUP関数との違い
VLOOKUP関数だと、検索値が含まれる列が抽出したい値が含まれる列より前にあるという制約があります。
INDEX関数とXMATCH関数を組み合わせた場合は、検索値の列がどの位置にあっても値の抽出ができます。
XLOOKUP関数の場合は、抽出したい値が含まれる列の位置が入れ替わったら、XLOOKUP関数の戻り値の引数を変更する必要があります。
INDEX関数とXMATCH関数を組み合わせた場合は、列も行もどちらも入れ替えても、XMATCH関数が自動で行番号や列番号を算出し直すので目的の値を抽出できます。（下図参照）
