VLOOKUP関数の使い方を手順で解説
VLOOKUP(ブイルックアップ)関数とは、表のいちばん左の列で値を探し、見つかった行の、右にある列の値を返す関数です。エクセル(Excel)で、注文の一覧に商品の価格を持ってくる、社員番号の一覧に名前を入れる、店舗の一覧に地域を入れる、といった作業に使います。
=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
- 検索値:探す値です。
E2のようなセルを指定するのが一般的です。 - 範囲:探す表です。表の最初の列が、検索値を含む列でなければなりません。
- 列番号:返す列を、表の左端から数えた番号です。1は表の最初の列で、シートのA列ではありません。
- 検索方法:完全一致なら
FALSEです。省略すると近似一致になります。
使い方の例
A1:C5に価格表があり、注文のシートでE2の商品コードの価格を出したいとします。
| A | B | C | |
|---|---|---|---|
| 1 | コード | 商品 | 価格 |
| 2 | K-104 | ケトル | 3,400 |
| 3 | T-220 | トースター | 2,950 |
| 4 | M-310 | ミキサー | 8,900 |
| 5 | B-415 | ブレンダー | 5,400 |
=VLOOKUP(E2, $A$2:$C$5, 3, FALSE)
E2がT-220なら、VLOOKUPはシートの3行目でコードを見つけ、表の3列目の2,950を返します。ドル記号で表を固定しているので、次の注文のために数式を下へコピーしても範囲は動かず、E2だけがE3に変わります。
完全一致と近似一致
コード、ID、名前を照合するときは、必ずFALSEを指定します。TRUEにするか最後の引数を省くと、VLOOKUPは最初の列が小さい順に並んでいるものとして、探す値以下で最も大きい値の行を返します。売上の段階ごとの手数料率や、重さごとの送料のような区分の表にはこれが合います。並んでいないデータでは、警告なしに違う行を返すので注意してください。
#N/Aが出てできないとき
#N/Aは、最初の列に値が見つからなかったという意味です。よくある原因は、入力の間違い、どちらかの値の末尾にある空白、片方は数値でもう片方は文字列の数字、の3つです。TRIMで余分な空白を消し、VALUEで数字の文字列を数値に変えられます。見つからないことが想定されている場合は、読めるメッセージを出すように包みます。
=IFNA(VLOOKUP(E2, $A$2:$C$5, 3, FALSE), "価格表にありません")
別シートから検索する
範囲の前にシート名と感嘆符を付けます。=VLOOKUP(E2, 価格!$A$2:$C$200, 3, FALSE)です。シート名に空白があるときは、'価格 一覧'!$A$2:$C$200のように単一引用符で囲みます。
VLOOKUPの限界とXLOOKUP
VLOOKUPは右方向しか見ないので、探す列より左の列は返せません。表に列が挿入されても列番号は変わらないため、気づかないうちに違う列を返し始めます。また、最初に一致した1件しか返しません。Microsoft 365とExcel 2021のXLOOKUPは、最初の2つの問題を解決します。=XLOOKUP(E2, A2:A5, C2:C5, "価格表にありません")は返す列を直接指定し、見つからないときの値も持っています。古いバージョンではINDEXとMATCHの組み合わせで同じことができます。
このレッスンの練習問題は、Excelのように動くスプレッドシートで解きます。数式を入力してEnterを押すと、セルの結果で答え合わせをするので、正しい数式であればどの書き方でも正解になります。