VLOOKUP関数は、表の左端の列を縦に検索し、同じ行にある右側の列の値を返す関数です。引数は4つで、最後の検索方法に FALSE を入れるのが実務の基本形。エラーの多くは「範囲を絶対参照にしていない」「検索値の型が違う」の2つです。
この記事でわかること
- VLOOKUP関数の4つの引数がそれぞれ何を指すか
- コピーしても壊れない書き方（範囲の絶対参照）
- 複数条件で引く方法と、列を挿入してもズレない書き方
#N/A・#REF!・#VALUE!の原因と直し方- XLOOKUP関数・INDEX＋MATCHとの使い分け
VLOOKUP関数の構文と引数
構文
=VLOOKUP(検索値, 範囲, 列番号, [検索方法])| 引数 | 指定するもの | 注意点 |
|---|---|---|
| 検索値 | 探したい値（商品コードなど） | 半角・全角、数値・文字列の違いが一致判定に影響する |
| 範囲 | 検索する表の全体 | 左端の列が検索対象。ここを取り違える人が最も多い |
| 列番号 | 返したい列が、範囲の左端から何列目か | 見出しの列位置ではなく、範囲内の相対位置 |
| 検索方法 | FALSE＝完全一致／TRUE＝近似一致 | 省略すると TRUE 扱い。実務では FALSE を明示する |
FALSE は 0、TRUE は 1 と書いても同じ動作です。
基本の使い方
まず最小の例です。商品コードから商品名を引く形で組みます。
商品マスタが F列（商品コード）・G列（商品名）・H列（単価）に並んでいるとします。A2に入力した商品コードから商品名を引くなら、次の式です。
=VLOOKUP($A2, $F$2:$H$50, 2, FALSE)列番号の 2 は、範囲 $F$2:$H$50 の左端であるF列を1として数えた2列目＝G列を指します。シート上の「B列だから2」ではありません。
範囲は必ず絶対参照にする
$F$2:$H$50 のようにドル記号で固定するのが要点です。固定せずに下へコピーすると、範囲が1行ずつ下へずれて、下の行だけエラーになります。
範囲を入力した直後に F4キーを押すと、絶対参照に切り替わります。
検索値の $A2 は列だけ固定にしています。こうすると右方向にコピーしても検索値の列がずれません。
実務でよく使う3パターン
① エラーのときだけ空欄にする
未入力の行に #N/A が並ぶのを避けるには、IFERROR関数で包みます。
=IFERROR(VLOOKUP($A2, $F$2:$H$50, 2, FALSE), "")ただし本当に必要な不一致まで隠してしまう点は理解しておいてください。マスタの登録漏れを見つけたい段階では、あえて包まないほうが安全です。
② 複数条件で引く（作業列を1本立てる）
VLOOKUPは条件を1つしか取れません。「店舗×商品」のように2つのキーで引くなら、マスタ側に連結キーの作業列を作ります。
- マスタの左端に列を挿入し、
=B2&"_"&C2で店舗と商品をつなぐ - 検索する側も同じ形で
=$A2&"_"&$B2と組み立てる - 作業列を左端に含めた範囲でVLOOKUPを実行する
連結記号にアンダースコアなどを挟むのは、「A1」+「23」と「A12」+「3」が同じ文字列になる事故を防ぐためです。
③ 列を挿入してもズレないようにする
列番号を数値で直書きすると、マスタに列を1本足しただけで全部の式が別の列を指し始めます。MATCH関数で列番号を計算させると、この事故が消えます。
=VLOOKUP($A2, $F$2:$H$50, MATCH("単価", $F$1:$H$1, 0), FALSE)MATCHが見出し行から「単価」の位置を毎回数え直すので、列を入れ替えても結果が変わりません。
うまくいかないときの原因と直し方
エラーの種類で原因はほぼ絞り込めます。
| 表示 | 原因 | 直し方 |
|---|---|---|
#N/A | 検索値が範囲の左端列に見つからない | 下の「#N/Aの4つの原因」を上から確認する |
#REF! | 列番号が範囲の列数を超えている | 範囲を広げるか、列番号を数え直す |
#VALUE! | 列番号が1未満、または検索値が長すぎる | 列番号を1以上にする。検索値は255文字以内に収める |
#NAME? | 関数名の綴り違い | VLOOKUP の綴りと、全角になっていないかを確認する |
| 一部の行だけエラー | 範囲が相対参照のままコピーされている | 範囲を $F$2:$H$50 の形に固定し直す |
#N/Aの4つの原因
見た目が同じでも一致しないことがあります。次の順に確認するのが早道です。
- 検索値がマスタに存在しない — マスタ側をフィルタして実在を確認する
- 前後に余分な空白がある —
=TRIM(A2)で確認し、必要なら置換で除去する - 数値と文字列が混ざっている —
=ISNUMBER(A2)と=ISNUMBER(F2)の結果が食い違えば型の不一致 - 全角と半角が混ざっている —
=ASC(A2)で半角に寄せて比較する
3の型の不一致は、セルの左寄せ・右寄せでも見分けられます。数値は既定で右寄せ、文字列は左寄せです。片方だけ左に寄っていたら型が違います。
文字列に揃えるなら =VLOOKUP($A2&"", …)、数値に揃えるなら =VLOOKUP(VALUE($A2), …) で片側を変換します。
検索方法を省略してはいけない理由
第4引数を省くと TRUE（近似一致）になり、完全に一致しなくても近い値を返します。しかも近似一致は範囲の左端列が昇順に並んでいることが前提で、並んでいなければ結果は不定です。
近似一致を使うのは、料金表や税率表のように段階で判定したいときだけです。その場合は左端列に各段階の下限値を昇順で並べます。
似た関数との使い分け
VLOOKUPには2つの制約があります。左側の列を取れないことと、列番号が位置依存であることです。この2つが問題になる場面では、別の関数を選びます。
| 関数・組み合わせ | 対応バージョン | 選ぶ場面 |
|---|---|---|
| VLOOKUP | 2016〜 | 古いExcelの相手にも渡すファイル |
| XLOOKUP | 2021〜 | 左側の列も取りたい。見つからない場合の表示も指定したい |
| INDEX＋MATCH | 2016〜 | 左側の列を取りたいが、2016・2019の相手にも渡す |
| HLOOKUP | 2016〜 | 表が横方向に並んでいる |
| FILTER | 2021〜 | 一致する行を1件でなく全件取り出したい |
XLOOKUPなら、同じ処理がこう書けます。
=XLOOKUP($A2, $F$2:$F$50, $G$2:$G$50, "該当なし")検索する列と返す列を別々に指定するので、位置関係の制約がありません。第4引数で見つからない場合の表示も決められるため、IFERROR関数で包む必要もなくなります。
ただしXLOOKUPはExcel 2021以降です。2016・2019の環境で開くと #NAME? になります。配布するブックではVLOOKUPのままにしておくのが安全です。
よくある質問
Q1：VLOOKUPで複数の該当行を全部取り出せますか？
できません。VLOOKUPは最初に一致した1件だけを返します。全件を取り出すには、Excel 2021以降のFILTER関数を使うか、作業列で連番を振って一致順に引く形にします。
Q2：検索値の大文字と小文字は区別されますか？
区別されません。ABC と abc は同じ値として一致します。大文字小文字を区別したい場合は、EXACT関数を組み合わせた別の方法が必要です。
Q3：検索値に「〇〇を含む」という条件は使えますか？
使えます。完全一致（FALSE）を指定したうえで、"*"&$A2&"*" のようにアスタリスクで挟むと部分一致になります。? は任意の1文字を表します。
Q4：別のブックにあるマスタを参照できますか？
参照できます。ただし参照先のブックを閉じていると値が更新されないことがあり、ファイルを移動するとリンクが切れます。運用が続くものは、同じブック内にマスタシートを持たせるほうが壊れません。
Q5：データが多くて再計算が重いのですが。
完全一致のVLOOKUPは、行数が増えるほど処理が重くなります。範囲を F:H のような列全体ではなく $F$2:$H$5000 のように必要な行数で区切るだけでも改善します。それでも重い場合は、Excel 2021以降のXLOOKUPへの置き換えを検討してください。
まとめ
- 引数は検索値・範囲・列番号・検索方法の4つ。検索方法は
FALSEを必ず書く - 検索は範囲の左端列だけ。列番号は範囲内の相対位置で数える
- 範囲はF4キーで絶対参照に固定する。一部の行だけエラーはこれが原因
#N/Aは「存在しない・空白・型違い・全角半角」の4つを順に確認する- 左側の列を取りたいならXLOOKUPかINDEX＋MATCH。配布用ならVLOOKUPのまま
※関数の対応バージョンはExcelの一般提供時点の情報にもとづいています。お使いの環境や更新プログラムの適用状況により、利用できる関数や表示が異なる場合があります。
あわせて読みたい
