【Excel関数】リストから情報を取得する方法

こんにちは、ふみです。みなさんは表計算ソフト「Microsoft Excel（エクセル）」でリストから情報を取得することはありますか？
私がリストから情報を取得するときはVLOOKUP関数を使うことが多いです。
しかし、中にはVLOOKUP関数で簡単に情報を取得できない場合もありました。

Excel でリストから情報を取得する方法について紹介します。

CONTENTS

1 VLOOKUP関数で情報を取得する
- 1.1 リストの名前を参照して情報を取得する
- 1.2 複数の名称を参照して情報を取得する
2 検索値を検索する列の左側にある列から情報を取得する
3 名前の入力をドロップダウンリストで選択できるようにする
4 まとめ

VLOOKUP関数で情報を取得する

リストの名前を参照して情報を取得する

リストから情報を取得する最もポピュラーな方法はVLOOKUP関数を使うことです。

H列に名前、J列に年齢が記載されているリストがあったとします。
セルB4に入力された名前をリストから参照してその人の年齢を表示させる場合、年齢を表示させるセルの数式は次のように設定します。

=IF($B4="","",VLOOKUP($B4,$H:$J,3,FALSE))

B4に入力された名前と一致する名前がリストにあったとき、その人の年齢が表示されます。

VLOOKUP関数は、検索値、範囲、列番号、検索方法を設定します。
範囲で指定した1列目から検索値と一致する行を探し、同じ行の列番号を設定した列から情報を取り出します。完全一致で検索する場合の検索方法は「FALSE」（フォルス）です。
ここで列番号を「3」に設定しているのは範囲の1列目のH列から数えて、年齢が記載されているJ列が3列目になるからです。

よって列番号を「3」から「2」に変更すると、H列から数えて2列目になるI列の性別が表示されます。

VLOOKUP関数は相対参照のアドレスのまま横にコピーすると機能しなくなる為、範囲のアドレスは絶対参照「$」で設定します。また、性別の2列目を参照する場合でも、リストの範囲の列全て「$H:$J」を指定しておくと、コピーしたときに列番号を変更するだけで3列目以降を参照できるようになります。将来リストを追加したときの為、範囲のアドレスは列番号のみにして、行番号で指定しないほうが良いです。

複数の名称を参照して情報を取得する

リストに同じ名前があった場合、2番目以降の名前は参照されなくなります。

例えば、A社の山田さんがいて、B社にも山田さんがいた場合、名前「山田」のみの情報だけでは個別に情報を取得することができません。
この場合、会社名と名前の2つの名称を参照する必要があります。

まず、リストの左の列に名前と会社名をつなげた名称が表示されるようにします。
G列が会社名、H列が名前、リストの先頭が5行目の場合、セルF5に次の数式を設定します。

=$G5&$H5

これでセルF5には会社名と名前がつながった名称が表示されます。このセルをドラッグ＆ドロップでリストの下までコピーすると、リストの下まで数式がコピーされます。

セルA4の会社名とセルB4の名前をリストから参照してその人の年齢を表示させる場合、年齢を表示させるセルの数式は次のように設定します。

=IF(COUNTA($A4,$B4)<2,"",VLOOKUP($A4&$B4,$F:$J,5,FALSE))

A4の会社名とB4の名前の両方がリストに一致したとき、その人の年齢が表示されます。

COUNTA関数は空白でないセルを数える関数です。ただし、空白処理されたセルの「""」は空白でないセルとして数えるので、空白処理のセルを参照するときは注意が必要です。
ここで使用した「COUNTA($A4,$B4)<2」はセルA4とセルB4の両方とも入力されていない時、2より小さくなるのでセルが空白で表示されます。
COUNTA関数を使った空白処理はセルの入力が2か所以上必要なときに使います。

検索値を検索する列の左側にある列から情報を取得する

複数の名称を参照する際、関数が崩れる等の理由によりリストの左側に数式を挿入できない場合があります。この場合、検索する列の右側の情報を取得するVLOOK関数が使えないので、INDIRECT関数とMATCH関数を組み合わせて使います。

まず、リストの右の列に名前と会社名をつなげた名称が表示されるようにします。
G列が会社名、H列が名前、リストの先頭が5行目の場合、セルK5に次の数式を設定します。

=$G5&$H5

これでセルK5には会社名と名前がつながった名称が表示されます。このセルをドラッグ＆ドロップでリストの下までコピーすると、リストの下まで数式がコピーされます。

セルA4の会社名とセルB4の名前をリストから参照してその人の年齢を表示させる場合、年齢を表示させるセルの数式は次のように設定します。

=IF(COUNTA($A4,$B4)<2,"",INDIRECT("J"&MATCH($A4&$B4,$K:$K,0)))

A4の会社名とB4の名前の両方がリストに一致したとき、その人の年齢が表示されます。

MATCH関数は検索値を検索範囲から検索して位置を返す関数です。ここでは「B社山田」をK列から検索しています。6行目にあるので「6」が返ります。
INDIRECT関数はカッコ内の文字列からセルを参照する関数です。ここでは年齢の列「J」とMATCHの戻り値「6」を「&」でつなげて「J6」の文字列を作成しているので、セルJ6の「20」がセルC4に表示されます。