VLOOKUPは、表の左端列で検索値を縦方向に探し、同じ行の指定列から値を返す関数です。基本形は=VLOOKUP(検索値,範囲,列番号,FALSE)です。商品コードから単価を返すなら=VLOOKUP(A2,$F$2:$H$100,3,FALSE)のように使います。日常のID検索では第4引数を省略せずFALSEで完全一致を指定するのが安全です。TRUEまたは省略時は近似一致となり、左端列の並び順が要件になります。検索列が左端にない、左方向へ返したい、列挿入に強くしたい場合は、対応版でXLOOKUPを検討します。
四つの引数を一つずつ確認する
構文はVLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])です。検索値は探すID、範囲は検索列と返却列を含む表、列番号は範囲の左端を1として数えた返却列、最後は完全一致FALSEまたは近似一致TRUEです。
たとえば範囲F:HでF列の商品コードを探し、H列の価格を返すなら列番号は3です。ワークシート全体のH列が8番目だから8ではありません。返却列を含まない範囲や、列番号が範囲幅を超える指定は正しい結果になりません。式を作る前に四項目を紙やセルへ書き出します。
完全一致はFALSEを明示する
社員番号、注文番号、商品コードなど、同じ値だけを採用する検索では第4引数をFALSEにします。省略すると既定はTRUEの近似一致です。完全一致のつもりで省略すると、存在しないコードに近い別行が返り、エラーにならないまま誤データを使う危険があります。
=VLOOKUP(A2,$F$2:$H$100,3,FALSE)のように、FALSEまで式へ残すと意図が明確です。検索できない場合の#N/Aは異常を知らせる有用な結果です。最初からIFERRORで空欄に隠さず、マスター欠落、型違い、余分な空白、入力ミスを調べます。
近似一致は境界表だけに使う
TRUEの近似一致は、点数から評価、売上から料率、重量から送料など、下限値の境界表を検索する用途に向きます。Microsoftの仕様では、近似一致を使うときは範囲の左端列を数値または文字の昇順に並べる必要があります。検索値以下で最も近い境界が採用されます。
境界表は最小値を含め、空白や重複境界を避けます。検索値が最小境界より小さいと#N/Aになります。90以上をA、80以上をBとする表なら、左端は0、60、70、80、90のように下限を昇順へ置きます。降順や「上限値」の表をそのまま使わないでください。
検索列は範囲の左端に置く
VLOOKUPが検索するのはtable_arrayの最初の列です。検索値がG列にあるなら、範囲をF:HにしたままG列を検索することはできません。範囲をG:Hから始める、表の列順を見直す、INDEXとMATCHまたはXLOOKUPを使うなどの方法を選びます。
元帳の列を並べ替えると他の数式や業務へ影響するため、検索のためだけに共有表を破壊的に変更しません。XLOOKUPは検索配列と戻り配列を別々に指定でき、左右どちらへも返せます。ただし配布先のExcel版で利用できるかを先に確認します。
範囲を固定し、行追加に備える
式を下へコピーするとtable_arrayも相対的に移動します。$F$2:$H$100のような絶対参照、名前付き範囲、Excelテーブルの構造化参照でマスター範囲を固定します。検索値A2は行ごとに変わるので通常は相対参照のままです。
固定範囲の末尾より下へマスターを追加すると、新行が検索対象に入らないことがあります。テーブル化すれば行追加を範囲へ取り込みやすくなります。外部ブック参照ではファイル移動、権限、リンク更新にも注意し、重要な集計を個人PCの絶対パスへ依存させません。
重複キーは最初の一致だけ返す
検索列に同じキーが複数ある場合、VLOOKUPは最初に見つかった一致を返します。返却値が同じでも、マスターの重複はデータ品質の問題です。COUNTIFで検索列の件数を数え、1を超えるキーを先に解消します。
複数明細をすべて取り出す関数ではありません。注文番号に複数商品がある表から全行を得たい場合、FILTER、Power Query、ピボット、データベース照会など目的に合う手段を使います。一件だけ返ったことを「該当行が一件だけ」と解釈しません。
数値と文字列の型をそろえる
見た目が123でも、一方が数値、もう一方が文字列なら完全一致で見つからないことがあります。先頭ゼロを持つコードは文字列として統一し、数量や日付は数値として統一します。セル左上の表示だけに頼らず、ISTEXT、ISNUMBER、LENや数式バーで確認します。
TRIMやCLEANで余分な空白・制御文字を整理する場合は、マスターを直接上書きせず補助列で結果を比較します。全角数字を半角へ変換する、ハイフンを統一するなどの正規化も、業務上別コードを同じにしないルールが必要です。
主なエラーから原因を切り分ける
#N/Aは完全一致がない、または型・空白が異なる場合に起こります。#REF!は列番号が範囲幅を超えたとき、#VALUE!は引数や範囲指定の問題、#NAME?は関数名や文字列の引用符などを確認します。エラーごとに原因が異なります。
IFNAで「未登録」と表示する方法はありますが、元の#N/A件数を別に集計します。IFERRORで全エラーを同じ空欄へ変えると、列番号ミスまで未登録に見えます。監査用列ではエラーを保持し、利用者向け表示列だけ説明文へ変えると追跡しやすくなります。
ワイルドカードの意図を確認する
FALSEのテキスト検索では、?が任意の一文字、*が任意の文字列として使えます。実際の?や*を探すときは前にチルダを付けます。部分一致は便利ですが、似た複数候補の最初の行を返すため、ID検索へ安易に使いません。
商品名の一部から価格を返す式は、表の並び順が変わると別候補になる可能性があります。検索語、候補数、選択基準を別に確認し、ユーザーへ候補一覧を見せる方が安全です。完全一致できる一意コードをマスターへ用意することを優先します。
XLOOKUPへ移行する判断
MicrosoftはXLOOKUPをVLOOKUPの改良版として案内しており、任意方向へ検索でき、既定が完全一致です。=XLOOKUP(A2,F2:F100,H2:H100,"未登録")のように検索列と返却列を別指定でき、列番号を数える必要がありません。
ただし古いExcelでは利用できないため、社外共有や長期運用では利用環境を確認します。VLOOKUPから置き換える際は、一致なし、重複、空欄、最小・最大、数値文字列で新旧結果を比較します。近似一致や検索方向など、XLOOKUPの引数を既定のまま誤解しないよう仕様を記録します。
結果を件数とサンプルで検証する
導入後は検索件数、正常件数、#N/A、その他エラー、重複キー、空白キーを集計します。先頭、中間、末尾、存在しないID、先頭ゼロ、重複、近似境界の直前・一致・直後をテストします。返却値だけでなく参照したマスター行も確認します。
マスター更新後に再計算が自動か、外部リンクが更新されたか、フィルターで一部行だけ見ていないかも確認します。値貼り付けで配布する場合は作成日時とマスター版を記録します。VLOOKUPは検索を効率化しますが、元データの正確性や一意性までは保証しません。
確認チェックリスト
- 検索値がtable_arrayの左端列にあるか確認する
- ID検索では第4引数FALSEを明示する
- 近似一致は左端列を昇順にし、最小境界を用意する
- 検索範囲を固定し、追加行が含まれる仕組みにする
- 数値・文字列・空白・先頭ゼロをそろえる
- #N/A、重複キー、その他エラーを隠さず集計する
コメント