行と列の交差するセルの値を求める

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

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

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

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

入力規則の設定

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

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

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

そして、元の値の拡大/縮小ボタンをクリックします。

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

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

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

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

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

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

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

INDEX関数

=INDEX(参照,行番号,列番号)

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

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

=INDEX(B2:H11,7,4)

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

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

MATCH関数

=MATCH(検査値,検査範囲,照合の型)

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

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

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

=MATCH(C13,A2:A11,0) 同じようにセル［C14］に入力した店名が範囲内で何番目になるかを求めます。

=MATCH(C14,B1:H1,0) INDEX関数にMATCH関数をネストします。

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

=INDEX(B2:H11,MATCH(C13,A2:A11,0),MATCH(C14,B1:H1,0))

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

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

INDEX関数とMATCH関数

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