更新日：、 作成日：
エクセル VLOOKUP 関数：検索して一致した行の右方向にある値を取得する
はじめに
エクセルの VLOOKUP 関数の使い方を紹介します。
VLOOKUP 関数は検索して一致した行の右方向にある値を取得します。好きな列から検索できるようにした上位互換の「XLOOKUP 関数」があります。
=VLOOKUP(100,A1:B3,2,FALSE) のようにして、100 を列「A」から検索して一致する行の列「B」の値を取得できます。
=VLOOKUP("エクセル",A1:B3,2,FALSE) のようにして、エクセル を列「A」から検索して一致する行の列「B」の値を取得できます。
=IFERROR(VLOOKUP(100,A1:B3,2,FALSE),0) のようにして、検索結果が見つからないときに 0 を表示できます。
行全体の値を取得したり、複数条件に一致する値を取得できます。
VLOOKUP 関数の引数
VLOOKUP(検索値, 範囲, 列番号)
検索値を範囲の 1 列目のデータと比較し、一番近い行の列番号の値を取得します。
VLOOKUP(検索値, 範囲, 列番号, 一致モード)
一致モードで検索値と一致するデータを取得するように指定できます。
| 引数「検索値」 | 検索する値を、数値、文字列、セル参照、関数などで指定します。 引数「一致モード」が FALSE のときだけ、ワイルドカードを使用できます。 | スピル化 |
| 引数「範囲」 | 検索対象となるデータと取得するデータが含まれたセルの範囲を指定します。この範囲の一番左の列を検索します。 | |
| 引数「列番号」 | 取得する列番号を指定します。引数「範囲」の一番左の列が 1 です。左から 2 番目の列は 2 になります。 | スピル化 |
| 引数「一致モード」 | 省略できます。 TRUE または省略：検索する値と同じまたは、それ以下の最大値と一致します。引数「範囲」の一番左の列が昇順に並べられている必要があります。 FALSE：検索する値と同じ値と一致します。 | スピル化 |
スピル化：セルの範囲や配列を指定すると結果が「スピル」します。
引数「検索値」
引数「一致モード」が FALSE のときに使用できるワイルドカードには次のものがあります。
| パターン | 説明 | 使用例 | 一致例 |
| * | 任意の長さの文字 | "あ*" | あ, あい, あいう |
| ? | 任意の 1 文字 | "あ?" | あい, あか, あき |
| ~ | ワイルドカードの文字 * ? を検索する | "あ~?~*" | あ?* |
スポンサーリンク
使い方をわかりやすく
VLOOKUP 関数の使い方を簡単に説明すると次のようになります。
1. 何を：引数「検索値」に指定した値を検索します。
2. どの列から検索：引数「範囲」の 1 列目のデータと一致するか検索します。
3. どの列を取得：「2.」で一致した行の引数「列番号」の値を取得します。
VLOOKUP 関数の引数に何を指定するのかわかりやすく解説します。
引数「検索値」、何を検索しますか？
次のようなデータがあります。この中から何を検索したいですか？
No. を検索して名前を取得したい。名前を検索してバージョンを取得したい。その検索する値を引数「検索値」に指定します。検索する値がセル「B8」に入っているのなら次のように入力します。
文字列を直接入力するには "エクセル" のように "" で囲みます。
引数「範囲」、どこから検索して何を取得しますか？
No. を検索して名前を取得するなら、その範囲を引数「範囲」に指定します。次のように入力します。
=VLOOKUP(B8,B3:B5 のように名前までの範囲でも大丈夫です。
名前を検索してバージョンを取得するなら、次のように入力します。
重要なのは、引数「範囲」の左端の列が検索対象になることです。逆に言えば名前を検索して No. を取得することはできません。
引数「列番号」、どの値を取得しますか？
No. から名前を取得するように引数「範囲」を指定したとき、名前の列は何番目ですか？
一番左の No. の列を基準とした 1 からの連番です。名前の列は 2、バージョンの列は 3 になります。取得したい列番号を引数「列番号」に指定します。次のように入力します。
引数「一致モード」、等しい値を検索しますか？
FALSE を指定して次のように入力します。
FALSE を指定すると引数「検索値」と同じ値のデータを引数「範囲」から検索します。TRUE や省略すると一致しなくても近いデータを取得します。文字列を検索するときは必ず FALSE にします。
使い方
VLOOKUP 関数の使い方を紹介します。
No. を検索して名前を取得する
No. が 1 の行の名前を取得します。引数「列番号」に名前の列の番号 2 を入力してその値を取得できます。
No. を検索して行の値を取得する
No. が 2 の行のすべての列の値を取得します。引数「列番号」に引数「範囲」のすべての列の番号を入力して行の値を取得できます。
結果を右方向にコピーしています。
「COLUMN 関数」を使用して、セルをコピーして列番号を 1 からの連番にできます。
結果を右方向にコピーしています。
COLUMN(B3)-1 のようにして、列「B」を基準に 1 からの連番にできます。
列「C」を基準にするには COLUMN(C3)-2 のように、引き算する値が増えます。
特定の文字が入っている名前を検索する
ワイルドカードを使用して、名前に ワード が入っている行の個数を取得します。
=VLOOKUP("ワード*",C3:E6,3,FALSE)
=VLOOKUP("*ワード",C3:E6,3,FALSE)
名前が ワード で始まる値は "ワード*" のように入力します。
名前が ワード で終わる値は "*ワード" のように入力します。
見つからないときに 0 にする
検索結果が見つからないときに 0 を表示します。「IFERROR 関数」を使用して、エラーのときの値を表示できます。
=VLOOKUP(B8,B3:E5,1,FALSE)
VLOOKUP 関数は検索結果が見つからないときにエラーになります。
値が基準値以下の最大値を検索する
基準値が 80 のとき、値が 80 以下の最大値の名前を取得します。引数「一致モード」に TRUE を指定して、引数「検索値」以下の最も近い値を検索できます。
日付ならその日以前の一番近い日付になります。
検索対象の列が昇順に並べ替えられている必要があります。
高度な使い方
VLOOKUP 関数と他の関数を組み合わせた使い方などを紹介します。
好きな列を検索する
名前を検索して No. を取得します。「INDEX 関数」と「MATCH 関数」を組み合わせて好きな列から検索できます。
=XLOOKUP(B9,$C$3:$C$5,$B$3:$B$5)
複数条件で検索する
名前とバージョンを検索して両方に一致する個数を取得します。
検索専用の列を左端に作成して、その中に名前とバージョンを区切り文字を付けて結合します。
引数「検索値」に検索専用と同じように名前とバージョンを区切り文字を付けて結合した値を入力して、複数条件に対応できます。
=XLOOKUP(B7&","&C7,C3:C5&","&D3:D5,E3:E5)
解説
好きな列から検索できるようにした上位互換である「XLOOKUP 関数」があります。通常はそちらを使用します。
引数「範囲」の一番左の列が検索対象になります。それ以外の列からは検索できません。
引数「検索値」が引数「範囲」から見つからないときはエラー #N/A になります。
引数「列番号」は、引数「範囲」の一番左の列を基準とした 1 からの連番です。
引数「列番号」が引数「範囲」の列数を超えるときはエラー #REF! になります。
文字列を検索するには、引数「一致モード」を FALSE にします。一致する文字列を検索できます。
引数「一致モード」が FALSE なら、文字列の大文字と小文字は区別しません。"ABC" と "abc" は等しいです。
引数「一致モード」が FALSE なら、引数「検索値」にワイルドカードを使用できます。
引数「一致モード」が TRUE のとき、引数「範囲」の一番左の列が昇順に並べ替えられている必要があります。そうでないときは正しい結果を取得できません。
例えば、引数「検索値」が 2 なら、次の画像では 1, 10, 2, 20 の順で検索します。検索値より大きい値が見つかった時点で、それ以降の値は検索しません。昇順のときにそれ以降に一致する値はないからです。10 と比較したとき 2 より大きいのでこれ以降の値は検索されません。結果を 10 の 1 つ前の行から取得します。
スポンサーリンク