行と列が交差するセルの値を求める（INDEX関数とMATCH・XMATCH関数）

Excelで下のようなマトリックス表があって、このマトリックス表の行と列が交差する値を、関数を使って自動的に求める方法です。

早見表などでも使用できると思います。

下の表は、1行目に店名、A列に食材名が表記されています。

セル［C13］に食材名を、セル［C14］には店名を入力すると、セル［C15］には、価格が自動的に表示できるようにしたいと思います。

入力規則の設定

まず、セル［C13］とセル［C14］には、入力規則を設定していてドロップダウンリストから選択できるようにします。

セル［C3］を選択して、［データ］タブの［データの入力規則］をクリックします。

［データの入力規則］ダイアログボックスで［設定］タブにある［入力値の種類］のプルダウンメニューから［リスト］を選択します。

そして、［元の値］の［ダイアログボックス拡大/縮小］ボタンをクリックします。

食材名が表記されているセル［A2］からセル［A11］までをドラッグします。

そして、再度　［ダイアログボックス拡大/縮小］ボタンをクリックします。

［元の値］に食材のリスト範囲が指定できたことを確認して、［OK］ボタンをクリックします。

セル［C13］をクリックすると、ドロップダウンリストが表示されリストから食材を選択できるようになります。

同じようにしてセル［C14］にも入力規則を設定して、リストから店名を選択できるようにします。

参考入力規則については、以下の記事で解説しています。

INDEX関数

セル［C15］に関数を入力します。

INDEX関数とMATCH関数を組み合わせますが、まずINDEX関数を解説します。

INDEX 関数（Microsoft）

上記の表では、参照範囲はセル［B2］からセル［H11］、行番号は7（食材Gは参照範囲内で7番目）、列番号は4（D店は参照範囲内で4番目）となります。

INDEX関数の関数の引数ダイアログボックスです。

セル［C15］の数式は以下のようになります。

行番号は参照範囲内で何番目、列番号は何番目といちいち数えるのは面倒ですね。

そこで、MATCH関数を使うと便利ですよということです。

MATCH関数

上記の表で、セル［C13］に入力した食材が範囲内で何番目になるかを求めるには

検査値はセル［C13］、検査範囲はセル［A2～A11］、照合の型は検査値と完全一致を求めるので0となります。

照合の種類
0	［検索値］と一致、［検索値］が文字列の場合は、ワイルドカードの使用ができます。
-1	［検索値］以上の最小値。［検索範囲］を降順に並べておく必要があります。
1または省略	［検索値］以下の最大値（既定値）。［検索範囲］を昇順に並べておく必要があります。

検索時に大文字と小文字は区別されませんが、全角と半角は区別されます。

MATCH 関数（Microsoft）

数式は以下のようになります。

同じようにセル［C14］に入力した店名が範囲内で何番目になるかを求めます。

INDEX関数にMATCH関数をネストします。

セル［C15］に以下の数式を入力します。

INDEX関数にMATCH関数をネストしたのがわかりますね。

XMATCH関数を使用する場合

Excel2021と、Microsoft 365のExcelでは、新関数［XMATCH］関数を使用できます。

XMATCH関数を使用するのであれば、以下のようになります。［検索値］と［検索範囲］を指定するだけでOKです。

以下の画像は、Excel2021です。

XMATCH関数の数式は、以下のようになっています。

［一致モード］の既定が完全一致なので、省略できます。また、［検索モード］も既定値が先頭から末尾への検索なので、これも省略できます。

一致モード
0または省略	完全一致（既定値）
-1	完全一致 または 次に小さい項目
1	完全一致 または 次に大きい項目
2	ワイルドカード文字との一致

検索モード
1または省略	先頭から末尾へ検索（既定値）
-1	末尾から先頭へ検索
2	バイナリ検索（［検索範囲］を昇順に並べておく必要があります。）
-2	バイナリ検索（［検索範囲］を降順に並べておく必要があります。）

XMATCH 関数（Microsoft）

関連以下の記事も参考になると思います。

INDEX関数とMATCH関数

この記事で使用した表は、マトリックス表、または、クロス集計表と呼ばれるものです。マトリックス表は、旧のピボットテーブルウィザードや、Power Query（パワークエリ）エディターを使用してテーブルに変換できます。

テーブルへ変換したほうが、便利な場合もあります。変換方法は、以下の記事で解説しています。

以下は入力規則に関する記事です。