Googleスプレッドシート VLOOKUP関数を使って垂直方向の検索をして指定した列の値を返す

「名簿から名前を検索して連絡先を取り出したい」
「売上金額から担当者名を取り出したい」
「スポーツのデータで記録の値から選手名や日付などを取り出したい」

Googleスプレッドシートに入力されたデータを有効に使うには、適切に検索が出来るか否かで大きく変わります。

せっかく貴重なデータを揃える事ができても、必要な情報を取り出すことが出来なければ宝の持ち腐れ状態になりかねません。

Googleスプレッドシートは、メニューの　編集 > 検索と置換　で検索する事はできますが、関数を使うと検索でヒットした行内にある指定したセルの値を受け取る事ができます。

検索でヒットした行内にある指定したセルの値を受け取れると何が出来る？

ヒットした行内のセルの値を受け取れると、膨大なデータからテーマに即した新たなデータ表を即座に作成する事が可能になります。

Googleスプレッドシートで数式を使って垂直方向の検索する場合の関数は

VLOOKUP(検索キー, 範囲, 値を返す列の番号, [近似照合の有無])

この記事ではGoogleスプレッドシートで直方向に検索して指定したセルの値を返す関数、VLOOKUP()の使い方を紹介したと思います。

GoogleスプレッドシートのVLOOKUP()関数

VLOOKUP()は検索キーで垂直方向に検索をして、ヒットした列の行内にある指定した列番号のセルの値を返す関数です。

VLOOKUP(検索キー, 範囲, 値を返す列の番号, [近似照合の有無])

検索キー

VLOOKUP(検索キー, 範囲, 値を返す列の番号, [近似照合の有無])

第1引数で検索キー（例: 42、”イヌ”、B14）を指定します。

検索キーは 数値、文字列、セル番号　を指定する事ができます。

文字列を指定する場合は「”文字列”」のようにダブルクォーテーションで囲みます。

正規表現に基づく検索キーはサポートされていません。

疑問符「?」とアスタリスク「*」をワイルドカードで使用できます。

範囲

VLOOKUP(検索キー, 範囲, 値を返す列の番号, [近似照合の有無])

第2引数で範囲を指定しますが、検索するのは指定した範囲の一列目のみです。

下の画像で説明すると検索対象なのは[B5]から[B19]までの都道府県だけで、それ以外は範囲を指定した部分も含め検索対象から外れます。

値を返す行内のセルの列番号

VLOOKUP(検索キー, 範囲, 値を返す列の番号, [近似照合の有無])

第3引数で値を返す列番号を指定します。

下の画像で説明すると検索キー”東京都”でヒットした[14]行で範囲指定した左から[2]番目のセル「人口」の値が返されます。

列番号は指定した範囲の左から1,2,3…と数えます。

近似照合

VLOOKUP(検索キー, 範囲, 値を返す列の番号, [近似照合の有無])

第4引数で近似照合を有効にするかどうかを指定します。

• 検索キーと完全一致位する時のみ値を返したい場合は　FALSE を指定
• 検索キーに最も近い（検索の値以下）近似値の場合も返したい場合は TRUE を設定

既定値はTRUEが設定されていますが、通常はFALSEを推奨しています。
大小バラバラの数値の順序が並べ替えられていればTRUEを指定して、近似照合できますが、並べ替えられていなければ正常に機能しません。

VLOOKUP()関数で検索、値を受け取る

VLOOKUP()関数で検索し、指定したセルの値を受け取ってみたいと思います。

VLOOKUP関数を文字列で検索

=VLOOKUP("富山県",B5:D19,3,FALSE)

検索キーが”富山県”、[B5:D19]の範囲、指定範囲の左から3番目のセルの値、完全一致

[17]行の面積の値 [4,248] が返されました。

VLOOKUP関数をワイルドカードを使って検索

疑問符「?」とアスタリスク「*」をワイルドカードで使用できます。
疑問符は 1 文字を、アスタリスクは任意の数の文字を表します。

=VLOOKUP("福?県",B5:D19,3,FALSE)

ワイルドカード「?」を使って検索キー”福?県”と指定。[B5:D19]の範囲、指定範囲の左から3番目のセルの値、完全一致

検索キーが”福?県”から”福島県”がヒットして面積 [13,784] が返されました。検索キー”福?県”なので”福島県”と”福井県”がヒットされそうですが、検索キーの一致が複数ある場合、最初にヒットした値を返します。

VLOOKUP関数の近似照合を無効[FALSE]に指定し数値を検索

=VLOOKUP(125,C2:E18,3,FALSE)

検索キーが 125 、[C2:E18]の範囲、指定範囲の左から3番目のセル「脈拍」の値、完全一致

検索キー　125　で最初にヒットした[7]行目の脈拍の値[60]が返されました。

VLOOKUP関数の近似照合を有効[TRUE]に指定し数値を検索

=VLOOKUP(130,C2:E18,3,TRUE)

検索キーが 130 、[C2:E18]の範囲、指定範囲の左から3番目のセル「脈拍」の値、近似照合を有効にして130に近い値（検索値以下）を検索

検索キー　130　に近い値 [128] で最後にヒットした[13]行目の脈拍の値[64]が返されました。

VLOOKUP関数をセル番号で検索

=VLOOKUP(A21,A5:E12,2,FALSE)

検索キーが セル番号[A21] 、[A5:E12]の範囲、指定範囲の左から2番目のセル「計測時刻」の値、完全一致

セル番号[A21]の値「2019/03/13」で最初にヒットした[6]行目の「計測時刻」の値 [23:08]が返されました。

VLOOKUP関数のエラー対策

VLOOKUP関数は範囲内に検索キーが見つからない場合や、範囲外の列番号を指定するとエラーを返します。

もしVLOOKUP関数でエラー表示を回避したい場合は、次の様な対策をするのが良いでしょう。

IFERROR関数でエラー対策をする

IFERROR関数を使用すれば、確実にエラー表示を回避できます。

=IFERROR(VLOOKUP(A21,A5:E12,2,FALSE),"…")

エラーになった時に返す文字列を空白「””」すると、VLOOKUP関数で検索したセルの値が空白なのか、それとも何かしらのエラーなのか見分けがつかなくなるので、「”…”」や「”-“」などの記号文字を指定するのが良いと思います。

参照する範囲を絶対参照で指定する

参照するセル範囲を「A5:E12」のように記述した場合、数式を入力したセルをコピーすると参照するセル範囲が変化してしまい、セル範囲を記述し直す必要がでてきます。これでは、数式を使いまわそうとしても使いにくい。

これを回避するには、参照するセル範囲の指定を「A5:E12」のような（相対参照）ではなく、「$A$5:$E$12」のように記述する（絶対参照）にします。

絶対参照にすれば、参照するセル範囲を固定できるので、数式をコピーしてもセル範囲を記述し直す必要がありません。

行列番号の英数字の前に「$」を挿入すると絶対参照になりますが、手動で入力するのは非常に手間がかかるので、キーボードの「F4」で参照方式を切り替えるのが便利。

数式の参照するセル範囲を選択し「F4」を押すと、「$」が挿入され（相対参照）と（絶対参照）が切り替わり、また「F4」を押す回数で参照方式が4段階で変わります。

■数式の中の”A1″をキーボードの「F4」で切り替えた場合

・1回：$A$1（絶対参照）
・2回：A$1（複合参照（絶対行参照））
・3回：$A1（複合参照（絶対列参照））
・4回：A1（相対参照）

VLOOKUP関数に限らず、同じ数式を何度も使いたい場合に役に立つ機能なので、是非覚えておいてください。

まとめ：VLOOKUP()関数を使って大切なデータの活躍の場を広げよう

Googleスプレッドシートで直方向に検索して指定したセルの値を返す関数、VLOOKUP()の使い方を紹介しました。

膨大なデータ群はテーマに沿ってまとめ直すと新たな発見があったり別の価値を生んだりする事があります。

今夏はVLOOKUP()の使い方のみを紹介しましたが、取り出した数値を使って計算したり、他の関数と組み合わせる事で、色々な工夫ができます。

VLOOKUP()関数を使って上手に情報を取り出せば、大切なデータをもっと活用する事できるかもしれません。

Twitter Facebook Pocket LINE はてブ