Excelで16桁数字のVLOOKUPが正しく表示されない場合の対処法｜ハイフン付き番号や大きな数値の扱い方

ExcelでVLOOKUPを使用する際、16桁の数字を検索値として扱う場合、数字の末尾が0になってしまったり、正しく表示されないことがあります。この記事では、ハイフン付きの番号や長い数値を正しくVLOOKUPで参照するための方法を具体例を交えて解説します。

Excelで16桁以上の数字を扱う注意点

Excelは15桁以上の数字になると、自動的に科学技術表記に変換され、末尾の数字が0になってしまうことがあります。

そのため、単純に数値として扱うと、正確なVLOOKUPの一致ができなくなります。16桁以上の番号は文字列として扱うことが重要です。

資料Bのように4桁ごとにハイフンがある番号は、SUBSTITUTE関数を使ってハイフンを取り除き、文字列として保持します。

例えば、セルB2に「1234-5678-9012-3456」がある場合、=SUBSTITUTE(B2,”-“,””)を使用すると、ハイフンを削除した文字列を取得できます。

VLOOKUPの検索値も文字列として扱う必要があります。検索値がセルA2にある場合、=VLOOKUP(TEXT(A2,”0″),範囲,列番号,FALSE)のようにTEXT関数で文字列に変換します。

これにより、末尾が0に丸められる問題を回避できます。

資料Aと資料Bの両方の番号を文字列として統一しておくことが重要です。セルの表示形式を「文字列」に設定するか、数値をTEXT関数で文字列に変換してください。

これにより、VLOOKUPは正確に一致するセルを検索でき、16桁の番号でも問題なく参照できます。

変換後は、検索値と検索範囲が文字列として一致しているか、LEN関数で桁数を確認すると確実です。16桁すべてが正しく保持されているかを確認しましょう。

Excelで16桁の番号をVLOOKUPで正しく参照するには、数値ではなく文字列として扱うことがポイントです。ハイフンをSUBSTITUTEで削除し、TEXT関数で検索値を文字列化することで、末尾が0になる問題を回避できます。検索範囲も文字列に統一することで、正確なVLOOKUPが可能になります。