ExcelのXLOOKUP関数の使い方｜完全一致・見つからない場合・重複の注意点

2026年10月13日

ExcelのXLOOKUPは、商品コードなどを手掛かりに、同じ行の商品名や単価を取り出す関数です。検索する列と返す列を別々に指定するため、列番号を数える必要がなく、検索列より左の列も取得できます。

まずは完全一致で一件を取り出す式から始めましょう。見つからない場合の表示、複数の一致がある場合、文字列と数値の違いまで押さえると、実務の表で使いやすくなります。

対応するExcelか確認する

Microsoft 365、Excel 2021・2024などが対象です。Excel 2016・2019ではXLOOKUPを利用できません。対応状況はMicrosoft公式のXLOOKUP関数ページを確認してください。自分の環境で動いても、古いExcelを使う相手に渡す場合は注意が必要です。

A1:C4に次の表を用意し、E2に検索するコードを入力する例です。F2へXLOOKUPを入れます。

=XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"該当なし")

E2がP102なら、返る値は「ペン」です。E2がP999なら「該当なし」になります。A列が検索範囲、B列が取得する範囲で、同じ行数を指定しています。ドル記号は、数式を下へコピーしても商品表の参照先がずれないようにするためのものです。

単価を返したい場合は、3番目の引数だけC列へ変えます。

=XLOOKUP(E2,$A$2:$A$4,$C$2:$C$4,"該当なし")

XLOOKUPは、追加の指定をしなければ完全一致で検索します。商品コード、社員番号、注文番号のようなID検索では、この方法を使うのが分かりやすいでしょう。

5番目の引数では一致方法を変更できますが、段階別料金などの表で近い値を返す処理と、IDが正確に存在するか確認する処理は分けて考えます。存在しない商品コードに近い別の商品が返る設計は、入力ミスを見逃す原因になります。

とくに、検索モードの2・-2によるバイナリ検索は、検索範囲の並び順に条件があります。大量データだからという理由だけで変更せず、基本の検索で目的を満たすか先に確認してください。

入力待ちの行で「該当なし」を並べたくない場合は、検索セルが空欄かどうかを先に判定できます。

=IF(E2="","",XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"該当なし"))

検索欄の空白と、検索した結果が見つからない状態を区別する式です。なお、商品表の返却先が空欄だった場合の扱いとは別です。商品情報が未登録の行もあるなら、検索キーの有無だけでなく、商品表の入力状況も確認しましょう。

商品表にP102が2行あっても、XLOOKUPは一致した行を全部一覧にする関数ではありません。通常は上から検索し、最初に一致した値を返します。

例えば、旧価格と新価格が同じコードで並ぶ表では、意図しない価格を取得するおそれがあります。商品マスターはコードを一意にするか、コードと適用日を組み合わせるなど、表の設計から確認します。

最後の一致を探す場合には、6番目の引数を-1にできます。ただし、「最後の行」が「最新の日付」と同じかどうかは別問題です。行順だけで最新情報を判断する式にはしないでください。

=XLOOKUP(E2,$A$2:$A$100,$C$2:$C$100,"該当なし",0,-1)

「0012」という商品コードを数値の12へ一括変換すると、別の意味になりかねません。数値へ揃える前に、その列が計算用の数字なのか、桁数も意味を持つ識別子なのかを確認します。

=XLOOKUP(E2,$A$2:$A$4,$B$2:$C$4,"該当なし")

この式は商品名と単価を横2列へ返します。出力先に別の値があると展開できないため、周囲を空けます。複数セルへ結果が広がる動作は、Microsoftのスピルに関する説明で確認できます。

まずP101・P102など既知のコードと、存在しないコードの両方で結果を確認し、元の表と照合してください。空欄や重複も含めて確かめてから、行数の多い業務表へ広げると安心です。

よかったらシェアしてね！