VLOOKUP関数とHLOOKUP関数の使い方とポイント【Excel】

PC

今回は、VLOOKUP関数とHLOOKUP関数の使い方とポイントについてです。VLOOKUP関数は、表や範囲などから値を取得(ルックアップ)する関数です。Excelにおける重要な関数であるため、できるだけ分かりやすく書いていこうと思います。

VLOOKUP関数(Vertical Lookup)

VLOOKUP関数は、検索値と列番号を指定して、表や範囲などから値を取得(ルックアップ)する関数です。言葉では難しいので、例を用いて確認していきましょう。

tayuyu
tayuyu

VLOOKUP関数は、イメージで覚えた方がわかりやすいと思うよ。

VLOOKUP関数の書式

VLOOKUP(検索値, 範囲, 列番号, [検索の型])

検索値必須検索する値を指定します。値、参照、文字列を指定します。
範囲必須検索値と戻り値(列番号の列)を含むセル範囲を指定します。ただし、範囲の1番左側の列が検索値を検索する列になるようにします。範囲に名前付き範囲またはテーブル名を使用することもできます。
列番号必須戻り値の列を指定します。引数「範囲」の一番左側の列(検索値の列)を1とします。
検索の型任意TRUEを指定または省略:最も近い値(近似一致)を検索します。
FALSEを指定:完全に一致した値(完全一致)を検索します。
表1:VLOOKUP関数の引数

VLOOKUP関数の使用例

図1のような場合を考えます。

図1:VLOOKUP関数(例)

B2セルからD7セルには、商品コードと商品名と価格で構成される表があります。

今回は、VLOOKUP関数を使用して、F3セルに入力されている「f03」という商品コードに対応する商品名である「すいか」をG3セルに表示してみたいと思います。

VLOOKUP関数の動作イメージ

まずは、VLOOKUP関数の動作のイメージを確認してみたいと思います。

図2:VLOOKUP関数の動作イメージ
  1. 一番左側の列(検索列)の上から順に、検索値である「f03」を検索していきます。(図2赤矢印)
  2. 検索値である「f03」があった行の左から2番目(列番号2)のセルの値が戻り値「すいか」となります。(図2紫矢印)
tayuyu
tayuyu

VLOOKUP関数は、検索値を垂直(Vertical)に検索するからVLOOKUPなんだね。

今回の例の場合、G3セルには、下記のように入力します。

=VLOOKUP(F3,$B$3:$D$7,2,FALSE)

1つ目の引数の検索値は、F3セルの内容「f03」を検索値とするので「F3」です。

2つ目の引数の範囲は、表の範囲である「$B$3:$D$7」です。範囲の一番左側の列は、検索値を検索する列となります。今回、表の範囲は、絶対参照となっていますが、状況に合わせて相対参照とすることも可能です。相対参照や絶対参照については、下記をご覧ください。

3つ目の引数の列番号は、商品名が欲しいので商品名の列である「2」です。(2つ目の引数である範囲の一番左側の列を1とします。)

3つ目の引数の列番号には、相性の良いMATCH関数が使われることもあります。

4つ目の引数である検索の型は、完全一致で検索したいので「FALSE」を指定しています。

VLOOKUP関数のポイント

検索値より左側の列を列番号に指定できない

VLOOKUP関数の仕様により、範囲の一番左側の列は検索値の列としなければならないため、検索値より左側の列を列番号に指定できません。

例えば、先程の例(図2)の場合、商品名を検索値として、商品コードを列番号に指定することはできません。

tayuyu
tayuyu

図2のような場合は、商品名「ぶどう」から商品コード「f01」はできないってことかあ。

対策

VLOOKUP関数の代わりに、INDEX関数とMATCH関数を使用することで、検索値より左側の列を検索することが可能です。(記事公開予定)

検索値に重複がある場合

検索値に重複がある場合は、範囲の上にある値が返されます。

例えば、図3の場合において、下記の数式をG3セルに入力します。

=VLOOKUP(F3,$B$3:$D$7,2,FALSE)

図3:検索値の重複

商品コード「f03」は図3の赤枠と青枠の2つありますが、範囲の上にある「f03」(赤枠)の行の商品名である「orange」が返されます。

大文字と小文字を区別できない

検索値は、大文字と小文字の区別ができません。

例えば、図4の場合において、下記の数式をG3セルに入力します。

=VLOOKUP(F3,$B$3:$D$7,2,FALSE)

図4:大文字と小文字の区別

検索値は「F01」ですが、VLOOKUP関数の検索値は大文字と小文字の区別がつかないため、範囲の上にある商品コード「f01」の商品名である「apple」が返されます。

  • 大文字と小文字を区別するルックアップ

HLOOKUP関数(Horizontal Lookup)

HLOOKUP関数は、検索値と行番号を指定して、表や範囲などから欲しいデータを取り出す関数です。

tayuyu
tayuyu

VLOOKUP関数とHLOOKUP関数の違いは、検索値を垂直方向に検索するか、水平方向に検索するかの違いで、イメージはどちらも同じだよ。

HLOOKUP関数の書式

HLOOKUP(検索値, 範囲, 行番号, [検索の型])

検索値必須検索する値を指定します。値、参照、文字列を指定します。
範囲必須検索値と戻り値(行番号の行)を含むセル範囲を指定します。ただし、範囲の1番上の行が検索値を検索する行になるようにします。範囲に名前付き範囲またはテーブル名を使用することもできます。
行番号必須戻り値の行を指定します。引数「範囲」の一番上の行(検索値の行)を1とします。
検索の型任意TRUEを指定または省略:最も近い値(近似一致)を検索します。
FALSEを指定:完全に一致した値(完全一致)を検索します。
表2:HLOOKUP関数の引数

HLOOKUP関数の使用例

図5のような場合を考えます。

図5:HLOOKUP関数(例)

B2セルからG4セルには、商品コードと商品名と価格で構成される表があります。先ほどのVLOOKUP関数の例の表(図1)について、行と列を入れ替えたものになります。

HLOOKUP関数を使用して、B6セルに入力されている「f03」という商品コードに対応する商品名である「すいか」をC7セルに表示してみたいと思います。

HLOOKUP関数の動作イメージ

HLOOKUP関数の動作のイメージを確認してみたいと思います。

図6:HLOOKUP関数の動作イメージ
  1. 一番上の行(検索行)の左から順に、検索値である「f03」を検索していきます。(図6赤矢印)
  2. 検索値である「f03」があった列の上から2番目(行番号2)のセルの値が戻り値「すいか」となります。(図6紫矢印)
tayuyu
tayuyu

HLOOKUP関数は、検索値を水平(Horizontal)に検索するからHLOOKUPなんだね。

今回の例の場合、C7セルには、下記ように入力します。

=HLOOKUP(C6,$C$2:$G$4,2,FALSE)

1つ目の引数の検索値は、C6セルの内容「f03」を検索値とするので「C6」です。

2つ目の引数の範囲は、表の範囲である「$C$2:$G$4」です。範囲の一番上の行は、検索値を検索する行となります。

3つ目の引数の行番号は、商品名が欲しいので商品名の行である「2」です。(2つ目の引数である範囲の一番上の行を1とします。)

4つ目の引数である検索の型は、完全一致で検索したいので「FALSE」を指定しています。

HLOOKUP関数のポイント

HLOOKUP関数のポイントは、VLOOKUP関数のポイントと同様です。

  • 検索値より上の行を検索できない
  • 検索値が重複した場合は左側にある値が戻り値となる
  • 検索値は大文字と小文字を区別できない

補足

VLOOKUP関数とHLOOKUP関数の違い

VLOOKUP関数とHLOOKUP関数の違いは、表3のようになります。

VLOOKUP関数VLOOKUP(検索値, 範囲, 番号, [検索の型])

検索値を垂直方向に検索する。
垂直(Vertical)の「V」
HLOOKUP関数HLOOKUP(検索値, 範囲, 番号, [検索の型])

検索値を垂直方向に検索する。
水平(Horizontal)の「H」
表3:VLOOKUP関数とHLOOKUP関数の違い

XLOOKUP関数について

Excel365を使用している場合は、VLOOKUP関数、HLOOKUP関数の上位互換の関数であるXLOOKUP関数がありますので、XLOOKUP関数を使用しましょう。

テーブルとMATCH関数を使用した場合

テーブル(または名前付き範囲)とMATCH関数を使用して、図7のような数式を作成することもできます。

=VLOOKUP(F3,商品表,MATCH(“商品名”,商品表[#見出し],0),FALSE)

図7
tayuyu
tayuyu

VLOOKUP関数の引数「範囲」がセル範囲ではなくテーブル名になっているから範囲の意味するものが分かるよ。

また、VLOOKUP関数の引数「列番号」がMATCH関数になっていて、MATCH関数の引数が「”商品名”」となっているから、どの列の値を調べているかが分かりやすいね。

テーブルについては、以前に記事を書いています。よろしければ、ご覧ください。

MATCH関数の詳細については、こちらをご覧ください。

コメント

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