スポンサーリンク

Excel(エクセル) VLOOKUP 関数ではできない左側(マイナス方向)の列の検索を、MATCH関数とINDEX関数を組み合わせてやる方法


以前VLOOKUP関数についての記事を書いたときに少し触れたのですが、この関数はとても便利なのですが左側(マイナス方向)の検索をすることができません。

VLOOKUP関数は「=VLOOKUP(検索値,範囲,列番号,検索方法)」 のように4つの引数を用いて記述します。

「範囲」とは「検索値」の候補となるデータのことです。
この「範囲」の一番左側の列から右側(プラス方向)への検索しかできません。

列番号 は必ず正の値でなければならないということです。
負の値にするとエラーが出てしまいます。

例えば、下の表のように社員の情報(社員No、名前、性別、年齢)の一覧があったとします。

名前から性別、年齢、社員Noのデータを検索したい場合、性別、年齢についてはVLOOKUP関数を使って引っ張り出す(検索する)ことができます。

検索値である名前をセル「I4」に入力した場合、
性別はセル「I5」に「=VLOOKUP(I4,C6:E15,2,FALSE)」と入力し、年齢はセル「I6」に「=VLOOKUP(I4,C6:E15,3,FALSE)」と入力することにより検索できます。

しかし、社員Noだけは「範囲」の一番左側の列よりも左側(マイナス方向)に並んでいるので、VLOOKUP関数では検索できないのです。

社員Noの列を名前の列よりも右側(プラス方向)に並べることによって、解決することもできますが、あえて左側(マイナス方向)のデータを検索したい場合もあるかと思います。

今日は、MATCH関数とINDEX関数を使って左側(マイナス方向)のデータを検索する方法を紹介したいと思います。

スポンサーリンク

1、MATCH関数とINDEX関数を使って左側(マイナス方向)のデータを検索する方法

MATCH関数を使って検索したいデータが入った行番号を取得する

MATCH関数を使えば、指定した「範囲」の中で「検索値」と一致するデータが入っている行番号を抽出できます。

「範囲」は1つの列の中で指定します。
複数列にまたがってはダメです。

また、ここでいう行番号というのは、指定した「範囲」の中で上から何番目かということを意味します。
※セル自体の行番号ではないので注意して下さい。

MATCH関数は、「=MATCH(検索値、範囲、照合の型)」のように記述し、計算結果は正の整数(1、2、3、4、、、)となります。

・「検索値」には探したい値が入ったセル
・「範囲」には、「検索値」の候補となるデータがあるセルの範囲(1つの列の中)
・「照合の型」には1、0、-1のいずれかの数値

が入ります。

「照合の型」は
・-1のとき 検索値よりも大きくて最小の値
・0のとき 完全に一致する値
・1のとき 検索値よりも小さくて最大の値(省略した場合は1となる)

となります。

今回は、「照合の型」は0にします。

さて、先ほどの表の余白のセル「I9」に「=MATCH(I4,C6:C15,0)」と入力します。

以下については、リンク先よりお願い致します。----------------------------------------------------------------
◆メルマガ登録
◆Youtube:税理士 松井元のチャンネル

これにより、セル「I4」に入っている「検索値」と一致するデータが入った行番号をセル「C6〜C15」の「範囲」から導き出します。

MATCH関数の計算結果(セル「I9」)は6となります。

セル「C6~C15」の中で、名前が「かかか」のデータは上から6番目にあるからです。

INDEX関数を使って指定した行番号と列番号が交差するセルのデータを取得する

MATCH関数で、名前が「かかか」のデータの行番号が取得できました。

次は、名前が「かかか」の社員Noを抽出したいと思います。

INDEX関数を用います。

INDEX関数は、指定した範囲内で行番号と列番号を指定してデータを抽出する関数です。

INDEX関数は「=INDEX(範囲、行番号、列番号)」のように入力します。

・「範囲」には探したいデータがあるセルの範囲
・「行番号」には「範囲」の中で探したいデータがある行の番号
・「列番号」には「範囲」の中で探したいデータがある列の番号

を入力します。

行番号というのは、指定した「範囲」の中で 上から何番目かということを意味します。
同様に列番号というのは、指定した「範囲」の中で 左から何番目かということを意味します。
※セル自体の行番号、列番号ではないので注意して下さい。

さて、先ほどMATCH関数を使って抽出した行番号を用いて、社員Noを出したいと思います。

セル「I7」に「=INDEX(B6:B15,I9,1)」と入力します。

・「範囲」を セル「B6~B15」
・「行番号」を セル「I9」(MATCH関数の計算結果が出力されるセル)
・「列番号」を 1

としました。

こうすることで、名前が「かかか」の場合はセル「I7」に社員Noとして3が出力されます。

MATCH関数とINDEX関数を組み合わせて使う

以上でMATCH関数とINDEX関数、それぞれの使い方について説明しいました。

これらの関数は1つのセルの中で組み合わせて使うこともできます。

先ほどセル「I7」に「=INDEX(B6:B15,I9,1)」と入力しましたが、
この式の中の列番号 I9の部分を置き換え
「=INDEX(B6:B15,MATCH(I4,C6:C15,0),1)」とします。

これで、先ほどと同じように名前が「かかか」の場合は、社員 No として3が抽出されます。

2、まとめ

以上、MATCH関数とINDEX関数を用いて、左側(マイナス方向)の検索をする方法について説明しました。

VLOOKUP 関数と合わせて使えば、様々な表の検索に活用することができますので、用途に合わせて活用して頂ければと思います。

以下については、リンク先よりお願い致します。----------------------------------------------------------------
◆メルマガ登録
◆Youtube:税理士 松井元のチャンネル