Excelで郵便番号から住所変換できない原因と解決方法｜XLOOKUP関数で住所録を作成する手順

Excelで住所録を作成するとき、郵便番号を入力して自動的に住所を表示できるようにすると作業効率が大きく向上します。しかし、XLOOKUP関数を使った検索では、参照範囲の指定ミスや郵便番号データの形式違いなどが原因で、住所が表示されないことがあります。

この記事では、Excelで郵便番号から住所を取得する仕組みや、XLOOKUP関数でエラーになる原因、正しい数式の書き方、郵便番号データを扱う際の注意点について解説します。

Excelで郵便番号から住所を表示する仕組み

郵便番号から住所を自動入力するには、郵便番号と住所が対応した一覧表をExcelに用意し、その表から該当する住所を検索します。

一般的には、日本郵便が公開している郵便番号データをExcelに取り込み、郵便番号を検索キーとして住所列を取得します。

例えば、入力欄に「1000001」と入力すると、検索表から東京都千代田区千代田などの住所情報を取得して表示するという仕組みです。

XLOOKUP関数を使う場合、検索値・検索範囲・戻り範囲の指定が正しくないと結果が表示されません。

例えば、以下のような数式では問題が発生する可能性があります。

=IF(D21="","",XLOOKUP(D21,KEN_ALL!C:C,KEN_ALL!G20:I20))

この数式では、検索範囲は列全体（KEN_ALL!C:C）を指定していますが、戻り範囲が「G20:I20」と1行だけになっています。XLOOKUPでは検索範囲と戻り範囲の行数を一致させる必要があります。

郵便番号一覧から住所を取得する場合は、検索列と住所列を同じ行範囲で指定します。

例えば、日本郵便のデータでC列が郵便番号、G列からI列が住所情報の場合、次のように指定します。

=IF(D21="","",XLOOKUP(D21,KEN_ALL!C:C,KEN_ALL!G:G))

ただし、この場合は都道府県、市区町村、町域のいずれか1列だけが返ります。3つの住所列をまとめて表示したい場合は、複数列を返せるExcelのバージョンで配列結果を利用します。

Excel 365やExcel 2021以降では、XLOOKUPで複数列を返すことができます。

例えば、都道府県・市区町村・町域がG列からI列にある場合は、以下のように指定できます。

=IF(D21="","",XLOOKUP(D21,KEN_ALL!C:C,KEN_ALL!G:I))

この場合、検索結果は横方向に3つのセルへ展開されます。1つのセルにまとめたい場合は、TEXTJOIN関数などを組み合わせます。

例。

=IF(D21="","",TEXTJOIN("",TRUE,XLOOKUP(D21,KEN_ALL!C:C,KEN_ALL!G:I)))

これにより、都道府県・市区町村・町域を連結した住所として表示できます。

XLOOKUPの設定が正しくても、郵便番号データの形式が違うと検索できません。

特に多い問題は、郵便番号にハイフンが入っているケースです。

例えば検索値が「100-0001」で、検索表が「1000001」の場合、文字列としては別のデータになるため一致しません。

その場合はSUBSTITUTE関数などでハイフンを削除するか、データ側の形式を統一します。

郵便番号は先頭に0が含まれる場合があります。Excelでは数値として入力すると先頭の0が消えてしまうことがあります。

例えば「0010001」という郵便番号を数値として入力すると「10001」となり、検索表のデータと一致しなくなる可能性があります。

郵便番号列は文字列として扱うため、セルの表示形式を「文字列」に設定しておくと安全です。

以前は郵便番号検索ではVLOOKUPがよく使われていましたが、XLOOKUPにはより柔軟な検索ができるメリットがあります。

XLOOKUPでは検索列が左端にある必要がなく、検索範囲と取得範囲を個別に指定できます。そのため、郵便番号データのような複数列の表でも扱いやすくなっています。

また、見つからなかった場合の表示内容も指定できるため、住所録作成ではエラー処理もしやすくなります。

Excelで郵便番号から住所へ変換できない場合、XLOOKUP関数そのものよりも、検索範囲と戻り範囲の指定、郵便番号データの形式が原因になっていることが多くあります。

特にXLOOKUPでは、検索範囲と戻り範囲の行数を合わせることが重要です。また、郵便番号のハイフン有無や文字列設定も確認すると解決しやすくなります。

正しい範囲指定とデータ形式を整えれば、Excelだけで効率的な住所録作成や郵便番号からの自動住所入力を実現できます。