ExcelのVLOOKUPとXLOOKUP｜別の表から値を引く書き方と、#N/Aになる原因の直し方
Excelで商品コードから単価を引く例を使い、VLOOKUPの4つの引数、完全一致と絶対参照、XLOOKUPへの置き換えを説明します。INDEXとMATCHの代案、#N/Aの原因を探す順番、文字列の数値や空白の確認、重複と近似一致の扱い、値に貼り付ける前の照合まで、同じ表と数式で確かめます。
注文表の商品コードを見ながら、別の価格表から単価を探して入力する。数件ならできても、行が増えると探し間違いや転記漏れが気になります。VLOOKUPやXLOOKUPは、この「同じコードの行を探し、その行にある単価を返す」作業に使う関数です。
数式を入れたのに#N/Aが出る場合は、書き方だけでなく、コードの保存形式や余分な空白も確認します。ここでは架空の商品マスターと注文表を使い、基本の書き方から、見つからない原因の切り分け、結果の照合までを同じセル番地で確かめます。
商品コードで注文表と商品マスターを結び付ける
二つの表に共通する値を決める
注文表には、何を何個注文したかを記録します。商品マスターには、商品コード、商品名、単価を登録します。マスターとは、繰り返し参照する基本情報の一覧です。注文のたびに単価を書き写す代わりに、注文表の商品コードを手がかりとしてマスターの該当行を探します。
この手がかりになる値を「検索キー」と呼びます。商品名は表記が変わることがあるため、ここでは商品コードをキーにします。同じ商品に同じコードが付いていること、一つのコードに対して単価を一つに決められることが、検索を始める前の条件です。
練習では一枚のシートに次のように配置します。A～D列が注文表、F～H列が商品マスターで、E列は空けます。表の左端の「行」は場所を示すための表示なので、セルへ入力しません。単価と数量は数値として入力し、C列とD列は数式を入れるまで空欄にします。
| 行 | A：商品コード | B：数量 | C：単価 | D：金額 | F：商品コード | G：商品名 | H：単価 |
|---|---|---|---|---|---|---|---|
| 1 | 商品コード | 数量 | 単価 | 金額 | 商品コード | 商品名 | 単価 |
| 2 | P102 | 3 | 空欄 | 空欄 | P101 | メモ帳 | 120 |
| 3 | P104 | 2 | 空欄 | 空欄 | P102 | ボールペン | 180 |
| 4 | P101 | 5 | 空欄 | 空欄 | P103 | クリアファイル | 250 |
| 5 | P999 | 1 | 空欄 | 空欄 | P104 | 付箋 | 90 |
価格は説明用の架空の値です。見出しは1行目、マスターのデータは2～5行目なので、検索に使う範囲はF2:H5です。最初の注文はA2のP102を探し、F3で見つかったら、同じ行のH3にある180をC2へ返す、という流れになります。
行の順番が違っても照合できる
注文表とマスターの行順はそろっていません。A2とF2をそのまま比較するのではなく、A2をF2～F5の中から探すため、P102の注文を先に書いても構いません。完全一致の検索なら、商品コードを昇順に並べる必要もありません。
P999は、見つからない場合を試すための未登録コードです。正常な三件と未登録の一件を一緒に用意すると、「何か数値が出た」だけで判断せず、検索に成功する場合と失敗する場合を区別できます。実務でも、登録済みと未登録の入力を少数ずつ試すと、数式の設定を確認しやすくなります。
VLOOKUPの4つの引数と完全一致
まずはC2で単価180を返す
C2に次の式を入力します。関数の引数とは、何を探すか、どの範囲を使うかなど、関数へ渡す指定のことです。カンマで区切った四つの指定を、左から順に読みます。
=VLOOKUP(A2,$F$2:$H$5,3,FALSE)A2は検索値です。注文表のP102を使います。$F$2:$H$5は検索に使う表の範囲です。左端のF列でコードを探します。3は返す列の番号です。範囲の左端から数えて3列目のH列を指定します。FALSEは完全一致の指定です。P102と一致するコードの行を探します。
結果は180です。F列で見つけた行を基準にH列を読むため、G列の商品名を経由して単価を探すわけではありません。商品名が必要なら列番号を2にすれば、同じP102に対応するボールペンが返ります。
列番号は、シート全体のA列から数える番号ではありません。H列はシートでは8列目ですが、指定範囲F2:H5ではF列が1、G列が2、H列が3です。8を指定すると、三列しかない範囲から8列目を返すことになり、#REF!の原因になります。
商品コードにはFALSEか0を指定する
第4引数のFALSEは、数値の0でも指定できます。商品コードでは、似たコードの商品の単価を代用してはいけないため、完全一致を使います。コードの一部が似ていることと、同じ商品であることは別です。
第4引数を省略するとTRUE、つまり近似一致として扱われます。「見つからなければエラーになるはず」と思って省略すると、別の商品の単価が返っても気づけません。完全一致と近似一致の指定や列番号の数え方は、MicrosoftのVLOOKUP関数の説明でも確認できます。
注文金額は、単価が正しいことを確認してからD2へ=B2*C2と入力します。数量3に単価180を掛けるので540です。単価を取る式と金額を出す式を分けておくと、金額が違うときに、検索結果と掛け算のどちらを調べればよいか分かります。
絶対参照でコピー後の検索範囲を固定する
動かすセルと固定する範囲を分ける
一件が正しく返ったら、C2をC5まで下へコピーします。A2はA3、A4、A5へ変わり、各行の商品コードを検索します。一方、マスターはどの注文でも同じ範囲を使うので、$F$2:$H$5を固定します。このように$で行や列を固定する指定が絶対参照です。
=VLOOKUP(A3,$F$2:$H$5,3,FALSE)
=VLOOKUP(A4,$F$2:$H$5,3,FALSE)
=VLOOKUP(A5,$F$2:$H$5,3,FALSE)コピー後はC3が90、C4が120、C5が#N/Aになります。C3とC4はマスターの単価と一致し、C5は未登録のP999なので見つからない状態です。まずこの並びを確かめてから、D2の掛け算をD4までコピーすると、D3は180、D4は600になります。
$なしのF2:H5をコピーすると、次の行ではF3:H6、さらに次はF4:H7へ動きます。練習表ではP104は途中までその範囲に残るため、C3だけ見れば成功しているように見えます。しかしP101のあるF2は外れてしまい、C4で見つからなくなります。
下へコピーするだけなら行番号の固定が中心になりますが、後で横方向へコピーする可能性も考え、ここでは列も行も固定しています。注文表の検索値を$A$2にしてしまうと、全行がP102を探し続けます。すべて180になったときは、検索範囲だけでなく検索値の固定も見直します。
別シートに分けても考え方は同じ
商品マスターを「商品一覧」シートの同じF2:H5へ移した場合は、範囲の前にシート名を付けます。注文表は元のシートに残す想定です。
=VLOOKUP(A2,'商品一覧'!$F$2:$H$5,3,FALSE)シートを分けるときは、先にデータの移動先を確認し、数式の範囲を選び直します。別シートにしたこと自体は、未登録や型の違いを解消しません。以降の例は、最初の一枚のシートの配置で説明します。
