【Excel】別シートの特定セルの1つ下のセルの値を取り出す

excel Excel

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

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

広告

今回やりたいこと

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

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

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

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

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

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

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

fffを入力するとA2にはgggが表示
広告

別シートの特定セルの1つ下のセルの値を取り出す

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

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

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

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

MATCH関数

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

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

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

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

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

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

INDEX関数

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 が取り出される、ということになります。

広告

1つ上の値を取得することも可能

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

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

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

コメント

タイトルとURLをコピーしました