VLOOKUPは、表の中から目的の値を探して、同じ行の別の列を取り出す関数です。「商品コードから商品名を引く」「社員番号から部署名を引く」といった、表と表を突き合わせる作業で使います。
スプレッドシートで最もよく使われる関数のひとつですが、同時にエラーで詰まりやすい関数でもあります。この記事では基本の使い方に加えて、#N/A が出たときの原因の切り分け方まで扱います。
書式
=VLOOKUP(検索キー, 範囲, 列番号, [検索の型])| 引数 | 内容 |
|---|---|
| 検索キー | 探したい値。セル参照でも直接入力でも可 |
| 範囲 | 探しに行く表の範囲。左端の列が検索対象になる |
| 列番号 | 範囲の左端を1として、取り出したい列が何番目か |
| 検索の型 | FALSE＝完全一致 / TRUE＝近似一致（省略時はTRUE） |
4つ目の引数は必ず FALSE を書いてください。 省略すると近似一致になり、意図しない値が返ります。理由は後述します。
基本の使い方
こんな商品マスタがあるとします。
| A | B | C | |
|---|---|---|---|
| 1 | 商品コード | 商品名 | 価格 |
| 2 | A-101 | ボールペン | 150 |
| 3 | A-102 | ノート | 320 |
| 4 | B-201 | ハサミ | 480 |
| 5 | B-202 | ホチキス | 890 |
ここから「B-201」の商品名を取り出します。
=VLOOKUP("B-201", A2:C5, 2, FALSE)結果：ハサミ
引数を順番に読むとこうなります。
- 1.
"B-201"を探す - 2.
A2:C5の左端の列（A列）から探す - 3. 見つかった行の2列目（B列）を返す
- 4.
FALSEなので完全に一致するものだけ
価格が欲しいなら、列番号を 3 に変えます。
=VLOOKUP("B-201", A2:C5, 3, FALSE) → 480実務での書き方
実際には検索キーをセルで指定し、数式を下方向にコピーします。このとき範囲は絶対参照（$付き）にするのが必須です。
=VLOOKUP(E2, $A$2:$C$5, 2, FALSE)$ を付けないと、下にコピーしたときに範囲がずれて A3:C6、A4:C7…と下がっていき、表の下のほうがヒットしなくなります。これが「途中から急に#N/Aになる」典型的な原因です。
範囲の指定は $A$2:$C$5 のように行を固定してもいいですが、行が増える可能性があるなら A:C と列全体で指定するほうが安全です。
=VLOOKUP(E2, A:C, 2, FALSE)4つ目の引数を省略してはいけない理由
FALSE を省略すると TRUE（近似一致）として扱われます。近似一致は範囲の左端列が昇順に並んでいる前提で動き、検索キー以下の最大値を拾います。
先ほどの表で =VLOOKUP("B-999", A2:C5, 2) とすると、完全一致する「B-999」は無いのに、エラーにならず ホチキス（B-202の行）が返ります。間違いに気づけないのが最大の問題です。
近似一致には「点数から評価を出す」といった正当な用途もありますが、意図して使う場合以外は必ず FALSE を書いてください。
エラーの原因と対処
#N/A が出る
「見つからなかった」という意味です。原因は次のどれかがほとんどです。
1. 検索キーが範囲の左端列にない
VLOOKUPは範囲の左端列しか検索しません。商品名から商品コードを引く（＝右から左）ことはできません。→ XLOOKUP か INDEX+MATCH を使ってください。
2. 余分なスペースが入っている
"B-201 " のように末尾に半角スペースがあると一致しません。見た目では気づけないので、疑わしいときは検索キーを TRIM() で囲みます。
=VLOOKUP(TRIM(E2), A:C, 2, FALSE)3. 数値と文字列が食い違っている
101（数値）と "101"（文字列）は別物として扱われます。片方がセルの左寄せ、もう片方が右寄せになっていたらこれです。
4. 範囲がずれている
前述の絶対参照の問題です。
#N/A を空欄にしたい場合は IFERROR で包みます。
=IFERROR(VLOOKUP(E2, A:C, 2, FALSE), "")ただし原因を確かめる前にIFERRORで隠すのは危険です。本当は取得できるはずのデータが黙って消えます。まず原因を特定してから包んでください。
#REF! が出る
列番号が範囲の列数を超えています。範囲が A:C（3列）なのに列番号に 4 を指定した、というケース。
結果が全部同じ値になる
検索キーを絶対参照にしてしまっている可能性があります。$E$2 ではなく E2 です。範囲とは逆なので混同しやすいところです。
列を挿入すると壊れる問題
VLOOKUPの列番号は「範囲の左から何番目」という位置で指定します。そのため、表の途中に列を挿入すると、数式の指す列がずれて壊れます。
対策は2つあります。
=VLOOKUP(E2, A:C, MATCH("価格", A1:C1, 0), FALSE)Excelとの違い
書式と挙動はExcelと同じです。ただしExcelでは VLOOKUP の後継として XLOOKUP が推奨されており、スプレッドシートでも同様に XLOOKUP が使えます。新しく作るならXLOOKUPのほうが素直です。
両者の使い分けは VLOOKUPとXLOOKUPの違い にまとめています。
まとめ
- – 書式は
=VLOOKUP(検索キー, 範囲, 列番号, FALSE) - – 4つ目の引数
FALSEは必ず書く - – 範囲は絶対参照か列全体で指定する
- – 検索できるのは範囲の左端列だけ
- –
#N/Aの多くはスペース・型違い・範囲ずれが原因
コメントを残す