VLOOKUPでできること
受注の一覧に商品コードを打ったら、商品名と単価が自動で入る。社員番号を入れたら、所属と氏名が出る。こうした「コードから別の表の情報を引いてくる」作業に使うのが VLOOKUP 関数です。
公式ヘルプでは、範囲の最初の列を縦方向に検索し、一致した行の指定した列の値を返す関数として説明されています。
手で転記する場合
- 商品一覧を開いてコードを探す
- 商品名と単価を書き写す
- 単価が変わるたびに直し漏れが出る
VLOOKUPを使う場合
- コードを打つだけで商品名と単価が入る
- 商品一覧を直せば全体に反映される
- 写し間違いが起きない
VLOOKUPの書き方
構文は次のとおりです。
=VLOOKUP(検索キー, 範囲, 番号, 並べ替え済み)
VLOOKUPの4つの引数
| 引数 | 意味 | 例 |
|---|---|---|
| 検索キー | 探す値。範囲の最初の列から探す | A2 (商品コード) |
| 範囲 | 探す表。左端の列に検索キーが入っている必要がある | 商品マスタ!$A$2:$C$200 |
| 番号 | 範囲の左から何列目の値を返すか (1 から数える) | 2 (商品名) |
| 並べ替え済み | FALSE で完全一致、TRUE で近似照合。省略すると TRUE | FALSE |
別のシートの商品マスタから引く例
よくある形として、「商品マスタ」シートに商品の一覧を置き、「受注」シートでコードから商品名と単価を引く例を作ります。
商品マスタのシート
| A | B | C | |
|---|---|---|---|
| 1 | 商品コード | 商品名 | 単価 |
| 2 | P-001 | コピー用紙 A4 | 2,800 |
| 3 | P-002 | ボールペン 黒 | 120 |
| 4 | P-003 | クリアファイル | 30 |
「受注」シートの A 列に商品コードを入れ、B2 と C2 に次の式を入れて下の行へコピーします。
=VLOOKUP($A2,商品マスタ!$A$2:$C$200,2,FALSE)
=VLOOKUP($A2,商品マスタ!$A$2:$C$200,3,FALSE)
別のシートの範囲は シート名!範囲 の形で書きます。範囲は $ を付けて固定しておくと、下へコピーしても範囲がずれません。公式ヘルプでも絶対参照の使用が勧められています。
未入力の行を空欄にする
A 列が空の行では #N/A が出て見づらくなります。見つからないときに空欄を出すなら、IFNA で囲みます。
=IFNA(VLOOKUP($A2,商品マスタ!$A$2:$C$200,2,FALSE),"")
IFNA は #N/A だけを置き換えます。範囲の指定ミスなど別のエラーまで隠れることがないので、IFERROR より原因を見落としにくい書き方です。
エラーが出たときの原因と直し方
VLOOKUPで出る主なエラー
| エラー | 主な原因 | 直し方 |
|---|---|---|
| #N/A | 検索キーが範囲の左端の列に見つからない | 値の前後の空白や、数字が文字として入っていないかを確かめる |
| #REF! | 番号が範囲の列数より大きい | 範囲を広げるか、番号を直す |
| #VALUE! | 番号が 1 より小さい、または文字になっている | 番号を 1 以上の数字にする |
| #NAME? | 文字の検索キーを " で囲み忘れた | "P-001" のように囲む |
#N/A がいちばん多いエラーです。見た目は同じでも、片方だけ末尾に空白が入っている、片方は数字でもう片方は文字として入っている、といった違いがあると一致しません。公式ヘルプでも、先頭や末尾の空白と、数値や日付が文字として入っていないかを確かめるよう案内されています。
左側の列を引きたいときはXLOOKUP
VLOOKUP は範囲の左端の列でしか探せません。商品名から商品コードを引くように、探す列より左にある値を返したいときは XLOOKUP 関数を使います。
=XLOOKUP(B2,商品マスタ!$B$2:$B$200,商品マスタ!$A$2:$A$200,"")
XLOOKUP は探す範囲と返す範囲を別々に指定するので、列の位置関係を気にせずに書けます。4 つ目の引数に見つからない場合の値を書けるため、IFNA で囲む必要もありません。一致の方法は既定で完全一致です。
VLOOKUP
- 範囲の左端の列でしか探せない
- 列の番号で返す列を指定する
- 既定は近似照合で
FALSEが必要
XLOOKUP
- 探す列と返す列を別に指定する
- 左側の列も返せる
- 既定が完全一致
複数の条件で探したいとき
VLOOKUP は条件を 1 つしか指定できません。「店舗」と「商品コード」の組み合わせで探したいときは、公式ヘルプのとおり、2 つの列をつないだ補助の列を作ります。
商品マスタ側の左端に補助の列を入れ、=B2&"-"&C2 のように店舗とコードをつないでおき、探す側も同じようにつないだ値で検索します。
=VLOOKUP($A2&"-"&$B2,単価表!$A$2:$D$500,4,FALSE)
自分で作るのが難しいときは
VLOOKUP は、マスタが 1 つで行数も数百件までなら十分に使えます。ただ、マスタが複数のファイルに分かれている、毎日何千行も追加される、といった状況になると、式が重くなったり、範囲の指定漏れで値がずれたりしやすくなります。
そうした表は、GAS で決まった時刻に値を書き込む形にしたり、データの持ち方から作り直したりすると安定します。どこから直すか決めかねているときは、シートナビの相談フォームからお気軽にご相談ください。
よくある質問
同じ検索キーが複数あるときはどの値が返りますか？
公式ヘルプのとおり、範囲の中で最初に一致した行の値が返ります。2 件目以降は返らないので、マスタの重複は先に取り除いておきます。
大文字と小文字は区別されますか？
P-001 と p-001 のような違いが混ざるときは、マスタと入力側の書き方をそろえておくのが確実です。区別して探したい場合は、EXACT 関数を使った判定など別の方法を組み合わせます。
ワイルドカードで部分一致の検索はできますか？
できます。公式ヘルプによると、? は任意の 1 文字、* は任意の文字の並びに一致します。ワイルドカードを使うときは、4 つ目の引数を FALSE (完全一致) にする必要があります。
