GoogleスプレッドシートのVLOOKUP(ブイルックアップ)関数は、大量データから指定した値や単語を探したい、指定した値に一致するデータを見つける場合に使用する関数です。
複数のシートにデータが存在していて、共通するIDなどで一致するデータを別シートから取得するなどの操作ができる関数です。
VLOOKUP関数は最初は理解しにくい関数ですが、非常に便利で使用頻度の高い社会人必須の関数のため、必ず覚えましょう。
INDEX
スプレッドシートのVLOOKUP関数とは
よく使われる例では、商品IDを入力で商品名を自動で表示させる、社員番号を入力で社員名を自動で表示させる、などがあります。
今回は商品一覧にあるデータを使った例で解説します。
商品コードを入れたら商品名を自動で表示する
VLOOKUP関数の使い方
1. 商品名を表示したいセルを選択します(今回はセルC3)
2. 以下のようにVLOOKUPの数式を入力します。
=VLOOKUP(B3,'商品一覧!$A$3:$C$9',2,FALSE)
VLOOKUP関数を使用するには、以下の4つの引数を指定します。
- 検索キー:検索をする時に使用するデータ(表からデータを探すための検索する値)
- 範囲:データを探しに行くセル範囲
- 番号:範囲指定した中の何列目を答えとして返したいかの列の番号
- 並び替え済み:完全一致でデータを検索する場合は「FALSE」を指定
(TRUE =近似一致)
B3に入力された検索キー(商品コード)を商品一覧シートの表の範囲の一番左の列から探す。
見つかったら、その範囲の左から2列目のデータを返す。
答えが表示されるのは、数式を入力したセルC3。
上記のように最初に指定した検索キーを範囲の一番左の列で探すため、検索キーが範囲の一番左の列にあるように指定します。
上記の表では、商品コードは一番左側の列にあり、商品名は2列目にあります。
- 範囲を選択する時は通常、絶対参照($付き)で指定します。
絶対参照を使用することで、他のセルに数式をコピーしたときに範囲は固定され同じ範囲を使用することができます。 - 検索キーに一致するデータが範囲に複数存在するとき、一番最初に見つかった値を返します。
- 並び替え済みは一般的には「FALSE」を指定します。
TRUE(もしくは省略)の使い方はページ下部にある詳しい使い方をご覧ください。 - 設定したデータの左側を取り出すことができません。
- 表に同じ検索キーが複数ある場合、最初の値が表示されます。
- VLOOKUPはアルファベットの大文字と小文字の判別はされないため、"ABC"と"abc"を同じ文字として扱います。
- [並び替え済み]のデフォルトはTRUEです。入力し忘れると省略扱いとなり、FALSE(完全一致)ではなく、TRUE(近似一致)となり、検索キーと同じ値が表に見つからなかった場合、異なる行の値が返されます。
部分一致で検索するには
検索キーを部分一致(○○を含む、○○から始まる、○○で終わる)で一致するデータを探す場合にはワイルドカードを使用することで部分一致での検索ができます。
ワイルドカードは「*(アスタリスク)」をダブルクォーテーションで囲み「&」で結合します。
ワイルドカードとは?
ワイルドカードとは、特定文字や単語等を含むなど部分一致での条件を指定する際に*や?の記号を使用して指定する方法です。
?(疑問符)は任意の1文字、*(アスタリスク)は任意の文字列に一致します。
例:条件を「山?」
〇:山田、山下、山内、山中、山崎、、など山の後ろが一文字のもの。
×:山野井、など山の後ろに2文字以上あるもの
例:条件を「山*」
〇: 山田、山下、山内、 山野井、など山の後ろに何文字あっても一致とみなします。
ワイルドカードの使い方
「含む」の数式
=VLOOKUP("*"&B16&"*",商品一覧!$A$4:$C$8,2,FALSE)
=VLOOKUP(B16&"*",商品一覧!$A$4:$C$8,2,FALSE)
=VLOOKUP("*"&B16,商品一覧!$A$4:$C$8,2,FALSE)
条件ごとに結果を複数にわける近似値の使い方(TRUE)
○以上△未満のような条件でデータを見つけたい場合は、4つ目の指定項目である並び替え済みを「TRUE」に設定します。
購入金額に応じた配送料が表示されるようにする
=VLOOKUP(B2,D3:F7,3,TRUE)
省略(もしくはTRUEを指定)した場合や一致するデータが見つからない場合は、最も近いデータを検索します。
この時、範囲で指定したデータは昇順に並べ替えておく必要があります。
並び替えをしていない場合、間違った値が表示されてしまいます。
条件ごとに結果をわけたい場合、 通常はIF関数を複数入れ子にするかIFS関数を使用して条件に合わせて結果を複数に分けるのが一般的です。
分類が多いとIFやIFS関数では入力項目が増え入力が大変です。
そのような場合は上記のようにVLOOKUP関数の並び替え済みの項目を「TRUE」にすると近似値での使い方で同様のことが簡単にできます。
VLOOKUP関数でエラーを出さないようにするには
VLOOKUP関数で一致するデータが存在しない場合や検索するIDなどが入力されていないと数式の結果はエラー表示になります。
エラーを表示させたくない場合は、IFERROR関数を使用し、エラーを任意の文字などに変換します。
=IFERROR(VLOOKUP("*"&B16&"*",商品一覧!$A$4:$C$8,2,FALSE),"-")