はじめに
Virtical(縦方向)に Lookup(検索)をするという意味で
[検索値]で指定した値を
[範囲]で指定した一番左の列で上から下に検索し
一致した行の左から[列番号目]の値が返ってきます
検索値 : 探したい値またはセル番号
範囲 : 検索値と探したい値が含まれている範囲
列番号 : 範囲で指定した一番左の列から数えて何番目の列の値を探したいか
検索型 : 検索値を超える値を検索する(True)かしないか(False)。省略可能
なんだか難しそうに見えますが例えば下記の場合だと
VLOOKUP(“りんご”, B:F, 3)
「りんご」という言葉を : 最初の”りんご”
B列から探して : B:F のB
見つかった行のD列の値を返して : 最後の3 Bから数えて B, C, D 3番目
と言う意味になります
かみくだいて見るとシンプルだったりします
ではVLOOKUPがいわゆる通常の「検索」と違う点は何でしょうか？
自動で欲しい値が返ってくるという点です
数件なら手動の検索で探して、欲しい値をコピーするのもよいかもですが
数百、数千と検索したい場合は「検索」では非効率です
そこはVLOOKUPで自動取得しましょう
VLOOKUPはIF, SUM, COUNT, SUMIF, COUNTIFと並んでエクセルの覚えておきたい人気関数です
- 商品番号から、価格を自動で探す
- 1000件の店舗番号が分かっていて、数万行の店舗シートから一致した店舗住所を探す
- 数万行ある売上シートと数万行ある顧客シートを顧客番号で一致させメールアドレスを探す
上記の便利例 最初の商品価格の例をみていきましょう
下記商品一覧の中から商品番号”P1011″の商品価格を探すとします
| A | B | C | D | E | F | G | ||
| 1 | 連番 | 商品番号 | カテゴリー | 商品名 | サイズ | 価格(円) | 販売日 | |
| 2 | 1 | P1001 | コーヒー | ドリップコーヒー – ダークロースト | ショート | 290 | 2021/01/01 | |
| 3 | 2 | P1002 | コーヒー | ドリップコーヒー – ダークロースト | トール | 330 | 2021/02/01 | |
| 4 | 3 | P1003 | コーヒー | ドリップコーヒー – ダークロースト | グランデ | 370 | 2021/03/01 | |
| 5 | 4 | P1004 | コーヒー | ドリップコーヒー – ダークロースト | ベンティ | 410 | 2021/04/01 | |
| 6 | 5 | P1010 | コーヒー | カフェミスト | ショート | 340 | 2021/01/01 | |
| 7 | 6 |
P1011
|
コーヒー | カフェミスト | トール |
380
|
2021/02/01 | |
| 8 | 7 | P1012 | コーヒー | カフェミスト | グランデ | 420 | 2021/03/01 | |
| 9 | 8 | P1013 | コーヒー | カフェミスト | ベンティ | 460 | 2021/04/01 | |
検索値 商品番号の”P1011″を指定します。セル番号を指定する事も可能で、例えばA100のようにすると、セルの値を変えるだけで関数を変えなくても自動的にその検索値で探してくれます。むしろセル番号で指定するケースが多いです
範囲 商品番号のB列を検索し、F列の価格を探したいのでB1:F9を指定します。列全体のB:Fと指定しても大丈夫です
列番号 一番左がB列から、F列までを、B,C,D,E,Fと数えて5番目なので5となります。ちなみに1とするとB列が返されます
検索型 近似値にするか？(True)完全一致か？(False)を指定します。False
つづいて便利例の2つ目をみていきましょう
スペースの関係で1000件のデータは使わないですが考え方は同じです(この例では3つだけですが別の1000件でも同手順)
A3~A5の店舗番号から住所を探したいとします
7行目以降のデータから住所を抜き出します
検索値の値に前回のような文字列ではなく、セル番号を指定してあげます
A3の店舗番号を検索したいので、B3のセルに =VLOOKUP(A3,A8:F11,5,FALSE) と入力します
| A | B | C | D | E | ||||||
| 1 | 検索 | |||||||||
| 2 | 店舗番号 | 店舗名 | 住所 | |||||||
| 3 | S1001 | =VLOOKUP(A3,A8:F11,2,FALSE) | =VLOOKUP(A3,A8:F11,3,FALSE) | |||||||
| 4 | S1002 | =VLOOKUP(A4,A8:F11,2,FALSE) | =VLOOKUP(A4,A8:F11,3,FALSE) | |||||||
| 5 | S1003 | =VLOOKUP(A5,A8:F11,2,FALSE) | =VLOOKUP(A5,A8:F11,3,FALSE) | |||||||
| 6 | ||||||||||
| 7 | 連番 | 店舗番号 | 店舗名 | 住所 | ||||||
| 8 | 1 | S1001 | ルミネ横浜店 | 神奈川県横浜市西区高島2-16-1 ルミネ横浜店2F | ||||||
| 9 | 2 | S1002 | アクアシティお台場店 | ドリップコーヒー – ダークロースト | ||||||
| 10 | 3 | S1003 | 八王子オクトーレ店 | ドリップコーヒー – ダークロースト | ||||||
| 11 | 4 | S1004 | LAQUE四条烏丸店 | ドリップコーヒー – ダークロースト | ||||||
VLOOKUPは月次売上などのレポートでよく使われます
会議前にいざレポートを人数分印刷して、ふとVLOOKUPのセルを見たら #N/A と謎の文字が出てきてしまってるケースありませんか？
これはVLOOKUPが検索値を見つけられなかった場合などに表示されるエラーメッセージです
せっかく作り込んだレポートなのにエラーが印刷に出てしまうのは悲しいので
エラーが出た場合の対策をしておきましょう
いたってシンプルで IFERROR構文を追加し
VLOOKUPがエラーを返した場合は、空白 (“”)を表示するとします
=IFERROR(VLOOKUP(A1,B:D,3,0),””)
IFERRORの詳細はIFERRORのページを参考にしてみてください
VLOOKUPとIFERRORはセットで覚えておくと便利です
- 検索範囲の一番左に必ず検索したい列が来る事
- 検索値をセルで参照する場合、検索したいセルのデータタイプと検索範囲のデータタイプが一致している事
Excelが得意なケースとそうでない場合とでベストなツールを使い分けられるようにしましょう
VLOOKUPと同じようなことを他のツールでどうやってやるかもあわせて見てみましょう
