ExcelのVLOOKUPとXLOOKUP｜別の表から値を引く書き方と、#N/Aになる原因の直し方
XLOOKUPで検索列と単価列を直接指定する
左端の制約と列番号の弱点
VLOOKUPは、指定した範囲の左端の列で検索します。練習表は商品コードがF列にあるため使えますが、G列の商品名から左側のF列の商品コードを返す用途には、そのままでは対応できません。範囲を広げても、左端以外の列を検索する指定はありません。
列番号にも注意が必要です。F～H列の間へ新しい列を挿入すると、単価列が右へ動いても、数式に書いた3は自動では4に変わりません。参照範囲は広がっても番号はそのままなので、別の列の値を返すことになります。$はコピーに備える指定であり、列番号が持つこの弱点を解消するものではありません。
XLOOKUPでは、検索する列と、結果を返す列を別々に指定します。同じC2の単価は次の式で取得できます。
=XLOOKUP(A2,$F$2:$F$5,$H$2:$H$5,"要確認")最初の引数が検索値、次がコードを探す範囲、その次が単価を返す範囲です。四つ目の"要確認"は、検索値が見つからなかった場合の表示です。C2は180になり、C5までコピーすると未登録のP999だけが要確認になります。文字列を指定するため、半角の二重引用符で囲みます。
見つからない表示と一致モード
XLOOKUPは通常、完全一致が既定です。ここでの第4引数は「見つからない場合」の指定であり、VLOOKUPの第4引数とは意味が違います。VLOOKUPの式を見ながら、同じ位置へ機械的にFALSEを入れ替えないようにします。
"要確認"は、登録がない場合だけでなく、空白や保存形式の違いで一致しない場合にも表示されます。表示が変わっても原因は残るため、要確認の行は確認対象として残します。また、見つかった商品の単価欄自体が空なら、見つからない場合の指定で補うことはできません。コードの有無と単価の入力状況は別に点検します。
検索範囲をF2:F5、戻り範囲をH3:H6にすると、長さは同じでも対応する行が一つずれます。XLOOKUPは位置で対応させるため、P102の位置に別商品の単価を割り当てかねません。同じ件数に加え、開始行と終了行もそろえて選ぶことが必要です。
自分と共有相手の環境で確認する
XLOOKUPが使えないExcelの環境もあります。空きセルで=XLOOKUPと入力し、関数の入力候補に出るか確認します。候補が出ない場合は、入力候補の設定も考え、関数の挿入画面で探すか、利用している製品の案内を確かめます。
共有するブックでは、自分のパソコンで使えるだけでは足りません。受け取る人が数式を再計算できるかも確認します。対応状況や引数の最新情報は、MicrosoftのXLOOKUP関数の説明で確認してください。新しい関数への統一より、受け渡し先で結果を維持できることを優先して選びます。
INDEXとMATCHの代案と関数の選び方
検索した位置を別の関数へ渡す
XLOOKUPを使えない環境で、左方向への検索や列番号への依存を避けたい場合は、INDEXとMATCHを組み合わせる方法があります。MATCHでコードの位置を求め、INDEXで同じ位置の単価を取り出します。練習表のC2では、次の式が180を返します。
=INDEX($H$2:$H$5,MATCH(A2,$F$2:$F$5,0))内側のMATCH(A2,$F$2:$F$5,0)は、F2:F5からP102を完全一致で探します。結果は2です。これはシートの2行目という意味ではなく、範囲の中で2番目という意味です。外側のINDEXはH2:H5の2番目、つまりH3の180を返します。
MATCHの最後の0も省略しません。VLOOKUPと同じく、コードの照合では一致の指定を明示することが必要です。式が読みにくいときは、空きセルでMATCHだけを計算し、2になることを確かめてから組み合わせます。Microsoftの検索関数を使う案内にも、INDEXとMATCHの組み合わせが紹介されています。
維持しやすさと共有条件で比べる
| 方法 | 検索列と結果列 | 列の追加・移動への備え | 見つからないとき | 選びやすい場面 |
|---|---|---|---|---|
| VLOOKUP | 範囲の左端で検索し右側を返す | 固定した列番号を確認する | 通常は#N/A | 既存の式を保ち、表の構成が安定している |
| XLOOKUP | 検索範囲と戻り範囲を直接指定する | 列番号を数えず列を参照する | 専用の引数で表示を指定できる | 利用者全員の環境で使える |
| INDEX＋MATCH | 位置を求め、指定列の値を返す | 検索列と結果列を別々に参照する | 通常は#N/A | 古い環境も含めて左右の検索を行う |
XLOOKUPやINDEX＋MATCHも、元データの削除や対応する行のずれまでは防げません。列を直接参照すれば、通常の列挿入で参照が追随する利点はありますが、表の構成を変えた後は検索値と返す項目の両方を点検します。
すでにVLOOKUPが使われ、商品コードが左端にあり、列の構成も変えない表なら、関数を置き換えるより完全一致と参照範囲を直す方が小さな修正で済みます。単価列を頻繁に動かす表や、コードの左側にある情報も返したい表なら、XLOOKUPかINDEX＋MATCHが候補になります。
関数を置き換える場合は、元の式を残した列と新しい式の列を並べ、登録済みの三件で単価が一致するか比べます。未登録の一件も試し、見つからないときの表示と、単価そのものの変化を区別して確認してください。
元のコードや単価を直接書き換える前に、作業用のコピーを用意してください。桁数や文字種をそろえる処理は、別の商品コードを同じ値に変えてしまう場合があります。
受け渡しの際は、式を選んだ理由も短く残すと引き継ぎやすくなります。「共有相手がXLOOKUPを使えないためINDEX＋MATCHを使う」「単価を返す範囲はH2:H5」のように、環境と参照先が分かれば十分です。関数名だけを残すより、表の行が増えたときの直し方まで追いやすくなります。
