Excelをデータベースとして使用したりする場合の検索/行列関数として真っ先に思いつき、利用頻度も高いのが VLOOKUP関数ではないかと思います。
この VLOOKUP関数は便利なのですが、検索する列を1列目として行方向に検索し、1列目から値を返す列を含めた範囲を対象として、値を返す列を列番号として指定するので、範囲内の列を挿入削除した場合などに列番号が変わり、#REF!エラーを起こしたり、数式を変更しなけらばならなくなる場合があります。また、1列目から列番号分数える必要があるので変更に手間がかかったり、再計算の速度にも影響があります。
VLOOKUP関数に対して列方向に検索する HLOOKUP関数があります。こちらは先頭行を検索して、値を返す行を行番号として指定します。こちらも同様に行の挿入削除があった場合に数式の変更が必要になる場合があります。
これらの問題を解消し、しかも速度アップにも繋がるのが INDEX関数 + MATCH関数 です。デメリットは少々数式が長くなることくらいでしょうか。列番号や行番号を指定する必要がないので、範囲内の行列を挿入削除しても数式の変更が不要なので便利です。
=INDEX(範囲,MATCH(検査値,検査範囲,0),MATCH(検査値,検査範囲,0))
例えばサンプルとして作成した下記の表では、R14セルに以下の上の数式を入れています（[照合の型]のFALSEは0と等価です。画像ではFALSEにしていますが通常は0ですね。）
=INDEX($A$1:$N$10,MATCH($A$14,$A$1:$A$10,0),MATCH($R$1,$A$1:$N$1,0)) =IF(ISERROR(INDEX($A$1:$N$10,MATCH($A$14,$A$1:$A$10,0),MATCH($R$1,$A$1:$N$1,0))),"",INDEX($A$1:$N$10,MATCH($A$14,$A$1:$A$10,0),MATCH($R$1,$A$1:$N$1,0)))
この上の式では、$A$1:$N$10の表を範囲として、A14セルに入力された年をA列の$A$1:$A$10から検索して行番号を求め、R1セルに入力された月を1行目の$A$1:$N$1から検索して列番号を求めて、その行と列が交差するセルの値を返しています。
仮に$A$1:$N$10の表が移動されたり行列が挿入削除されても、数式セルを移動しても、数式セルが参照するセルを見失って#REF!エラーを起こすようなことはありません。
ただし、MATCH関数の検索値が見つからない場合には#N/Aエラーを返すので、それを回避したい場合は下の式のようにIF関数とISERROR関数を使用します（この場合は空白を返しています）。
VLOOKUP関数やHLOOKUP関数では、求める列番号や行番号が変わってしまいます。
分解して解説
行番号を求めるMATCH関数
=MATCH($A$14,$A$1:$A$10,0)
MATCH(検査値,検査範囲,[照合の型])関数では、検査範囲から検査値を検索し、その範囲内での相対的な位置を数値で返します。列を範囲とした場合は範囲の一番上からの相対的な位置を返します。[照合の型］により、1:検査値以下の最大値、0:検査値と等しい最初の値、-1:検査値以上の最小値となりますが、この場合は完全一致の0とし、A14セルの値をA列のA1:A10から検索し、A1からの相対的な位置を返します。この値がINDEX関数での行番号となります。
列番号を求めるMATCH関数
=MATCH($R$1,$A$1:$N$1,0)
行を範囲とした場合は一番左からの相対的な位置を返します。この場合は、R1セルの値を1行目のA1:N1から検索し、A1からの相対的な位置を返します。この値がINDEX関数での列番号となります。
INDEX関数に2つのMATCH関数を代入
=INDEX($A$1:$N$10,行番号,列番号) =INDEX($A$1:$N$10,MATCH($A$14,$A$1:$A$10,0),MATCH($R$1,$A$1:$N$1,0))
INDEX関数は、指定された行と列が交差する位置にあるセルの値を返します。
この場合、MATCH関数で求めた行番号と列番号を使い、範囲A1:N10において行番号と列番号が交差する位置のセルの値を返します。
以下、同じ表でVLOOKUP関数やHLOOKUP関数の場合と INDEX関数 + MATCH関数 の場合を比較します。
VLOOKUP関数のいろいろなパターン
VLOOKUP関数のみ
=VLOOKUP(検索値,範囲,列番号,[検索方法])
VLOOKUP関数は、指定した範囲の1列目（一番左の列）で検索値を検索し、範囲内の同じ行で、列番号で指定した列の値を返します。
=VLOOKUP(2017,$A1:$N10,8,FALSE)
上記式は下記表のP12セルに入力されており、VLOOKUP関数で2017年7月の値を参照する計算式です。
月は列番号で指定（固定）していますので、例えば範囲ごと移動された場合はP12セルの計算式も追従して対応しますが、列の挿入削除で列番号が変わってしまった場合などは意図しない値を返す場合があります。
VLOOKUP + COLUMN関数
=COLUMN([参照])
COLUMN関数は、参照するセルの列番号を返します。
=VLOOKUP($A12,$A1:$N10,COLUMN(B1),FALSE)
上記式は下記表のB12セルに入力されており、VLOOKUP関数で2017年1月の値を参照する計算式です。
A12セルの値（2017）を検索値としてA列を検索し、列番号をCOLUMN(B1)で取得し、2列目の値を返しています。COLUMN関数を使用しているので、列方向にN12セルまでオートフィルで連続コピーすることにより、2017年の1月～12月と合計の値を求められますが、検索範囲の列の挿入削除で列番号が変わってしまった場合などは意図しない値やエラーを返す場合があります。
VLOOKUP + MATCH関数
=VLOOKUP(A2,A$1:N$10,MATCH(P$1,A$1:N$1,0),FALSE)
上記式は下記表のP2セルに入力されており、VLOOKUP関数で2019年7月の値を参照する計算式です。
下記のMATCH関数部分で列番号を指定しています。この場合は、P1セルの値を検索値として範囲A1:N1を検索し列番号（8）を求めています。
=MATCH(P$1,A$1:N$1,0)
その上で、VLOOKUP関数でA2セルの値（2019）を検索値としてA列を検索し、MATCH関数で得た列番号から8列目の値を返しています。行方向にP10セルまでオートフィルで連続コピーすることにより、2019～2011年の7月の値を求められます。
HLOOKUP関数のいろいろなパターン
HLOOKUP関数のみ
=HLOOKUP(検索値,範囲,行番号,[検索方法])
HLOOKUP関数は、指定した範囲の1行目（一番上の行）で検索値を検索し、範囲内の同じ列で、行番号で指定した行の値を返します。
=HLOOKUP(7,$A1:$N10,4,FALSE)
上記式は下記表のQ13セルに入力されており、HLOOKUP関数で2017年7月の値を参照する計算式です。
検索値を月の7として1行目を検索し、年は行番号で指定（固定）していますので、例えば範囲ごと移動された場合はQ13セルの計算式も追従して対応しますが、行の挿入削除で行番号が変わってしまった場合などは意図しない値を返す場合があります。
HLOOKUP + ROW関数
=ROW([参照])
ROW関数は、参照するセルの行番号を返します。
=HLOOKUP(Q$1,A$1:N$10,ROW(B2),FALSE)
上記式は下記表のQ2セルに入力されており、HLOOKUP関数で2019年7月の値を参照する計算式です。
Q1セルの値（7）を検索値として1行目を検索し、行番号をROW(B2)で取得し、2行目の値を返しています。ROW関数を使用しているので、行方向にQ10セルまでオートフィルで連続コピーすることにより、2019～2011年の7月の値を求められますが、検索範囲の行の挿入削除で行番号が変わってしまった場合などは意図しない値やエラーを返す場合があります。
HLOOKUP + MATCH関数
=HLOOKUP(B1,$A1:$N10,MATCH($A13,$A1:$A10,0),FALSE)
上記式は下記表のB13セルに入力されており、HLOOKUP関数で2017年1月の値を参照する計算式です。
下記のMATCH関数部分で行番号を指定しています。この場合は、A13セルの値を検索値として範囲A1:A10を検索し行番号（4）を求めています。
=MATCH($A13,$A1:$A10,0)
その上で、HLOOKUP関数でB1セルの値（1）を検索値として1行目を検索し、MATCH関数で得た行番号から4行目の値を返しています。列方向にN13セルまでオートフィルで連続コピーすることにより、2017年の1～12月と合計の値を求められます。
INDEX関数の行列番号にMATCH関数を使用した場合
INDEX関数の範囲を列にとりMATCH関数で行番号指定
=INDEX(B2:B10,MATCH($A14,$A2:$A10,FALSE))
上記式は下記表のB14セルに入力されており、INDEX関数で2017年1月の値を参照する計算式です。
下記のMATCH関数部分で行番号を指定しています。この場合は、A14セルの値を検索値として範囲A2:A10を検索し行番号（3）を求めています。
=MATCH($A14,$A2:$A10,FALSE)
その上で、INDEX関数で範囲B2:B10を指定し、MATCH関数で得た行番号から3行目の値を返しています。列方向にN14セルまでオートフィルで連続コピーすることにより、2017年の1～12月と合計の値を求められます。
INDEX関数の範囲を行にとりMATCH関数で列番号指定
=INDEX(B2:N2,MATCH(R$1,B$1:N$1,FALSE))
上記式は下記表のR2セルに入力されており、INDEX関数で2019年7月の値を参照する計算式です。
下記のMATCH関数部分で列番号を指定しています。この場合は、R1セルの値を検索値として範囲B1:N1を検索し列番号（7）を求めています。
=MATCH(R$1,B$1:N$1,FALSE)
その上で、INDEX関数で範囲B2:N2を指定し、MATCH関数で得た列番号から7列目の値を返しています。行方向にR10セルまでオートフィルで連続コピーすることにより、2019～2011年の7月の値を求められます。
INDEX関数の範囲を行列にとりMATCH関数で行列番号指定
冒頭の通りですが、例えば、B15セルに入力してオートフィルで列方向に連続コピーする場合は下記のようになります（A15セルに年を入力する場合）。
=INDEX($A$1:$N$10,MATCH($A$15,$A$1:$A$10,FALSE),MATCH(B$1,$A$1:$N$1,FALSE))
S2セルに入力してオートフィルで行方向に連続コピーする場合は下記のようになります（S1セルに月を入力する場合）。
=INDEX($A$1:$N$10,MATCH($A2,$A$1:$A$10,FALSE),MATCH($S$1,$A$1:$N$1,FALSE))
というわけで、INDEX + MATCH関数を使いましょうという内容です。
以上、ご参考になれば幸いです。
コメント