【Excel】別シートの特定セルの1つ下のセルを相対参照する

エクセルで別シートの特定セルを取り出すことは簡単にできますし、別シートの特定セルと同じ行の値もVLOOKUP関数を利用することで取り出すことができます。

しかし、別シートの特定セルの一つ下のセルの値を取り出すにはVLOOKUP関数は利用できないので、関数の組み合わせで取得してみました。

今回やりたいこと

今回やりたいことは以下です。

ある列に文字などの値が複数入っている [Sheet1] が存在している状態で、

[Sheet2] のA1に [Sheet1] に存在する列の値を入力すると、
[Sheet2] のA2に [Sheet1] に存在する列の値の中からA1で指定した値の一つ下を表示する。

・bbb を入力すると A2 には ccc が表示される

・fff を入力すると A2 には ggg が表示される

INDEX関数とMATCH関数を組み合わせることで値を取得することが可能です。

上記の [Sheet2] の A2 には以下のように記載します。

=INDEX(Sheet1!A2:A8, MATCH(A1, Sheet1!A2:A8, 0)+1, 1, 1)

以下で式の中身を解説していきます。

MATCH関数は以下の引数をとり、

MATCH(検査値,検査範囲,照合の種類)

引数で指定された[照合の種類]に従って、
引数で指定された[検査範囲]を検索し、
引数で指定された[検査値]と一致する要素の相対的な位置を返します。

今回でいうと、[Sheet2] のA1に bbb と入力した場合、 [Sheet2] の A2 に入力した式の一部である以下のMATCH関数の部分は、2 を返します。

MATCH(A1, Sheet1!A2:A8, 0)　→　2

その上で、セルの中の式として +1 しているのでINDEX関数の2番目の引数は 3 となります。

INDEX関数は以下の引数を取り、

INDEX(配列,行番号,列番号,領域番号)

引数で指定された[行番号]と、
引数で指定された[列番号]が交差する位置にあるセルの値を返します。
[領域番号]は省力可能です。

[Sheet2] のA1に bbb と入力した場合、MATCH関数の結果も含めると以下のようになり、

 #[Sheet2] の A2 入力した式（再掲）
=INDEX(Sheet1!A2:A8, MATCH(A1, Sheet1!A2:A8, 0)+1, 1, 1)

 #[Sheet2] の A1 に bbb と入力した場合の式の結果
=INDEX(Sheet1!A2:A8, 3, 1, 1)

[Sheet1] の[A2:A8]において、3行目の値（A2を1行目としたときの値）、すなわち A4 に格納されている ccc が取り出される、ということになります。

上記では bbb と入力したら ccc が出力されるというような1つ下の値を取り出していますが、
bbb と入力したら aaa が出力されるというような1つ上の値を取り出すこともできます。

=INDEX(Sheet1!A2:A8, MATCH(A1, Sheet1!A2:A8, 0)‐1, 1, 1)

INDEX関数の2番目の引数のMATCH関数に対して [-1] とするだけで可能です。