【Excel】XLOOKUPで条件に合う最初の1件を取得する基本手順

商品名に合う価格を一つだけ取り出したい、担当者コードから名前を表示したい。XLOOKUPなら、探す値、探す範囲、返す範囲の三つを指定するところから始められます。標準では完全一致で検索し、範囲の先頭から見つかった最初の一致を返します。

ただし、「最初の1件」は最新、最安、優先度が最高という意味ではありません。同じキーが複数ある表では、並び順が結果を決めます。ここでは一つの条件で一つの値を取得する使い方に絞り、重複や未登録、参照範囲のずれを確認する方法まで説明します。

1. 使えるExcelと検索表を確認する

XLOOKUPはMicrosoft 365、Excel 2021、Excel 2024などで利用できます。Excel 2016とExcel 2019では使えないため、共有先の版も確認してください。関数名に_xlfn.が付いたり、#NAME?になったりする場合は、式の綴りに加えて対応版かどうかを確認します。

例としてA1を「商品」、B1を「価格」とし、A2:B5へ次の表を作ります。同じ商品Aを2行入れることで、どちらの価格が選ばれるかを確認できます。検索値はD2、結果はE2に置きます。

行	A列 商品	B列 価格
2	商品A	100
3	商品B	200
4	商品A	120
5	商品C	300

検索列と価格列は、同じ行が同じ商品を表すことが前提です。価格列だけを並べ替えたり、片方だけ空白を詰めたりした表では、関数の書き方が正しくても別商品の価格を返します。元表の対応を崩さず管理してください。

2. 三つの引数で最初の一致を取得する

D2に商品Aを入力し、E2に次の式を入れます。第1引数D2が探す値、第2引数A2:A5が検索範囲、第3引数B2:B5が返す範囲です。結果は100になります。

=XLOOKUP(D2,A2:A5,B2:B5)

上から検索するとA2で商品Aが見つかり、同じ位置にあるB2の100を返します。A4にも商品Aがありますが、標準の検索では先に見つかった行が使われます。この式は商品Aの価格をすべて一覧にしたり、100と120を合計したりするものではありません。

D2を商品Bへ変えると200、商品Cなら300です。式を直接書く際に商品名を指定するなら、=XLOOKUP("商品A",A2:A5,B2:B5)のように文字を二重引用符で囲みます。入力用セルを用意すれば、検索するたびに式の中を書き換えずに済みます。

完全一致は既定なので、この基本式に一致モードの0を追加する必要はありません。検索列の昇順への並べ替えも不要です。二分検索のモードには並べ替えの前提がありますが、最初の導入では検索モードを省略したまま使うと、不要な条件を増やさずに確認できます。

3. 未登録と入力待ちを別々に扱う

表にない商品DをD2へ入力すると、基本式では#N/Aになります。検索で一致が見つからなかった場合の表示は、第4引数で指定できます。未登録を空欄にすると見落としやすい表では、短い文言を出します。

=XLOOKUP(D2,A2:A5,B2:B5,"未登録")

第4引数は、一致が見つからなかった場合に使うものです。参照の大きさが合わない場合の#VALUE!、参照先が壊れた#REF!、返すセル自体のエラーまで一律に隠す設定ではありません。エラーが残るときは種類を読み、範囲や元表を直します。

また、商品は登録されていても価格セルが空白なら、未登録とは別です。空の返却セルが0として表示される場合があるため、0円の商品なのか、価格未入力なのかを元表で確認します。検索できたことと、必要なデータがそろっていることを同一視しないでください。

D2が空欄の間は何も表示しないようにするなら、外側にIFを加えます。検索範囲の空白を意図せず探すことも避けられます。

=IF(D2="","",XLOOKUP(D2,$A$2:$A$5,$B$2:$B$5,"未登録"))

この式では入力待ちを空欄、入力された値が見つからなければ未登録と区別します。計算結果のセルに手入力で「未登録」を入れるのではなく、式が返す表示として残すことで、D2の変更に追従できます。

4. 重複があるときは先頭と末尾の意味を決める

商品Aの100と120のどちらを使うべきかは、検索式だけでは決められません。コードが本来一意である表なら、重複を修正する必要があります。確認用に=COUNTIF(A2:A5,D2)を使うと、この例の商品Aは2件と分かります。重複を見つけても、正しい行を調べずに削除しないでください。

先頭の行を採用するルールなら、表を並べ替えると結果が変わることがあります。日付順、優先順位順など、なぜその行が先頭なのかを表の管理方法と合わせます。「最初だから正しい」ではなく、先頭を採用してよいデータかを確認します。

末尾から最初の一致を探したい場合は、第5引数を0、第6引数を-1にします。次の式は、例の表で120を返します。

=XLOOKUP(D2,A2:A5,B2:B5,"未登録",0,-1)

これはあくまで下から探す指定です。日付が古い順に並んでいるなどの前提がなければ、「最新価格」を返す保証はありません。最新日付を条件に選びたい場合は、その条件を別途設計します。複数の一致を全部出したい場合も、XLOOKUPで一つを選ぶ目的とは分けて考えます。

5. コピーや列変更で参照をずらさない

検索値をD2、D3、D4と並べ、結果を下へコピーするときは、検索表の範囲を固定します。E2に次の式を入れて下へコピーすれば、検索値だけがD3、D4へ変わり、元表はA2:A5とB2:B5のままです。

=XLOOKUP(D2,$A$2:$A$5,$B$2:$B$5,"未登録")

固定しないままコピーすると、次の行では検索範囲がA3:A6へ動き、先頭の商品を見落とすことがあります。検索範囲と返す範囲は、大きさだけでなく開始行もそろえます。A2:A5に対してB3:B6を指定すると、4行ずつでも1行下の価格を返す誤りになります。

XLOOKUPは返す列を直接指定するため、列番号を数えて指定する必要がありません。ただし、参照する列そのものを削除しても壊れないわけではありません。列の挿入や削除後は数式バーの参照先と代表商品の結果を確認します。行が増える表では、追加した行が検索範囲に含まれているかも点検します。

旧版と共有するためVLOOKUPを使う場合は、完全一致の第4引数FALSEを明示します。VLOOKUPの第4引数は省略可能ですが、省略時は完全一致ではありません。「XLOOKUPは三つ、VLOOKUPは必ず四つ」という構文の比較ではなく、望む検索方法を明示することが大切です。

6. 見つからないときは文字と型を確認する

見た目が同じ商品コードでも、数値の123と文字列の”123″、前後に空白がある文字列などは検索結果が合わない原因になります。検索値と元表をそれぞれISNUMBERやISTEXTで調べ、文字数はLENで比べます。空白を消す処理も、コード中の空白に意味がないことを確認してから行います。

先頭0や16桁以上の番号を含むIDは、照合のために安易に数値化しません。検索側と表側を同じ文字列の規則にそろえ、元のコードを保持します。型を合わせるつもりで00123を123にすると、別コードと区別できなくなる場合があります。

導入後は、商品A、商品B、未登録の商品D、空欄を順に入力して結果を確認します。作業用のコピーで商品Aの2行を入れ替え、最初の一致が変わることも確かめれば、重複時の動作を理解できます。試験後は元に戻し、検索表と数式の両方を保存してください。最小構成から始め、必要な条件だけを足すと、後から読める検索式になります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】