「列を追加したら関数が全部壊れた」「#N/Aエラーが出て直し方がわからない」
ExcelでVLOOKUPを使っている方なら、一度はこういったトラブルを経験しているのではないでしょうか。
この記事では、VLOOKUPの弱点をすべてカバーするXLOOKUP関数の基本的な使い方を、実務での使用例とあわせて解説します。
まず結論
- VLOOKUPの「列番号がズレる」「左端制限」「エラー処理が複雑」という3つの問題は、XLOOKUPに切り替えることで解消できる
- 基本の引数は3つだけで、VLOOKUPより書き方がシンプル
- Excel 2019・Microsoft 365で使用可能
VLOOKUPでよく起きる3つの問題
① 列を追加・削除すると関数が壊れる
VLOOKUPは「3列目のデータを返す」のように、列の位置を数字で指定します。
表に列を1つ追加した途端にその番号がズレ、意図しないデータが返ってくるか、エラーになります。
- 修正前：
=VLOOKUP(A2, D:G, 3, 0)→ 「商品名」を取得 - 列を1つ追加後：同じ式が「単価」を返してしまう
この修正作業だけで、月に何度も15〜20分が消えていきます。
② 検索する値が左端の列にないと使えない
VLOOKUPは、検索する値が必ず表の一番左の列にある前提で動きます。
「コードは右側にある」「氏名から番号を逆引きしたい」という場合は、そのままでは使えません。
③ エラーを非表示にするためにIFERRORを重ねる必要がある
データが見つからないときの「#N/A」を消すには、IFERRORで外側から囲む必要があります。
=IFERROR(VLOOKUP(A2, D:G, 3, 0), "")
関数を重ねるほど、あとで読み返したときに構造がわかりにくくなります。
XLOOKUPの基本的な使い方
基本構文
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合])
最初の3つの引数だけ覚えれば、ほとんどの場面で使えます。
使用例
社員番号（A2）をもとに、社員名簿のD列で検索してE列の氏名を返す場合：
=XLOOKUP(A2, D:D, E:E, "未登録")
| 引数 | 内容 | この例 |
|---|---|---|
| 検索値 | 探したい値 | A2（社員番号） |
| 検索範囲 | どの列で探すか | D:D（社員番号列） |
| 戻り範囲 | 何を返すか | E:E（氏名列） |
| 見つからない場合 | エラー時の表示 | “未登録” |
VLOOKUPと比較したときのメリット
- 列番号の指定が不要：列を追加・削除しても関数が壊れない
- 左端制限なし：検索する値がどの列にあっても使える
- エラー処理が1つの関数で完結：IFERRORを重ねる必要がない
- 逆引きも可能：検索範囲と戻り範囲を入れ替えるだけで右から左への検索ができる
実務でよくあるケースへの対応
検索範囲・戻り範囲は列全体（D:D）で指定する
行番号を固定せず D:D のように列全体を指定しておくと、データが増えても関数を修正する必要がありません。
エラー表示は必ず設定しておく
第4引数を省略すると、見つからないときに「#N/A」が表示されます。
"－" や "未登録" など、用途に応じた表示を設定しておくとシートが見やすくなります。
既存のVLOOKUPを置き換える場合
全部一度に変える必要はありません。エラーが頻発しているセルや、今後列の追加が見込まれる表から順番に切り替えていくのが現実的です。
よくある質問
Q. VLOOKUPとXLOOKUPは同じ結果になりますか？
基本的な検索では同じ結果になります。ただしXLOOKUPはデフォルトが完全一致なので、VLOOKUPで第4引数を0（完全一致）にしていた場合と同じ動作です。
Q. ExcelのどのバージョンからXLOOKUPは使えますか？
Excel 2019以降およびMicrosoft 365で使用できます。Excel 2016以前では使えないため、VLOOKUPを継続する必要があります。
まとめ
- VLOOKUPは「列番号の手動指定」「左端制限」「エラー処理の複雑さ」という構造的な弱点がある
- XLOOKUPはこれらを解消し、引数がシンプルで扱いやすい
- 検索範囲・戻り範囲を列全体で指定し、第4引数でエラー処理を設定するのが基本
- 既存のVLOOKUPはエラーが多いものから順番に置き換えていくと無理がない
コメント