XLOOKUPで「値があるのに見つからない」原因と解決方法

GoogleスプレッドシートのXLOOKUP関数で、「セルのデータに検索値があるのに、なぜか#N/Aになってしまう」場合の原因と解決策をご紹介します。それぞれ「日付・数字・文字列」といったデータの形式ごとに解説しているので、ぜひ参考にしてみてください。

検索値が日付の場合

原因：

検索値の文字列（例："7/20"）と、セルのデータ（例：7/20）は、見た目が同じでも、データの種類が異なるため、XLOOKUPは別の値だと判断します。

解決策

最も確実な方法：検索値をセル参照にする
- 検索したい日付が入力されているセルを、検索値として直接参照します。これにより、XLOOKUPは日付という同じ種類のデータ同士を比較できます。

手入力する場合：DATE関数を使う
- 日付を手入力する必要がある場合は、DATE関数を使って、=XLOOKUP(DATE(2025,7,20),A2:A4,B2:B4)のように文字列を日付に変換します。

DATE関数の引数：(年, 月, 日)

原因：

「数値の 1」 と 「文字としての “1”」 は、別のデータとして扱われます。

検索値に二重引用符（""）を付けて指定したり、表示形式が「書式なしテキスト」に設定されていると、XLOOKUPは「別の値」と判断します。

解決策

最も確実な方法：検索値を数字として指定する
- 検索値を"1"ではなく、単に1と指定します。これにより、XLOOKUPは数字という同じ種類のデータ同士を比較できます。
- 検索値が入っているセルを選択し、「表示形式」→「数字」→「自動」に設定します。

参照先のデータが文字列の場合：VALUE関数を使う
- もし、参照先のデータが文字列になっている場合は、VALUE関数を使って、=ArrayFormula(XLOOKUP(1,VALUE(A2:A4),B2:B4))のように数値に変換します。

VALUE関数の引数：(テキスト)

原因：

XLOOKUPはアとｱのように、意味が同じ文字の全角と半角は区別しない一方、‐（全角ハイフン）と-（半角ハイフン）のように、違う文字として認識される記号は区別します。

解決策

確実な方法：関数でデータを統一する
- もし参照先のデータに見えないスペースなどが混在している可能性がある場合、REGEXREPLACE関数を使って、=ArrayFormula(XLOOKUP("1ー3",REGEXREPLACE(A2:A4," |　",""),B2:B4))のようにスペースを削除します。

REGEXREPLACE関数の引数：(テキスト, 正規表現, 置換)

XLOOKUP関数の#N/Aエラーは、多くの場合、セルの『データの種類』に原因があります。

「見た目は同じでも、種類が違う」データを別のものだと判断するため、XLOOKUPを使う際は、日付、数字、文字列といったデータの形式が統一されているかを意識することが重要です。