ExcelのXLOOKUP関数を使うには？完全一致・未登録データと参照範囲の整え方

※本記事にはPRを含む場合があります。

こんにちは。『脱会社員』脱サラを応援し続けるブログ、運営者の『なかびー』です。

商品コードから商品名を調べる、担当者IDから所属を表示するなど、別の表から情報を探して転記する仕事は少なくありません。ExcelのXLOOKUP関数は、検索の目印を一つ指定し、対応する場所にある値を取り出すときに使えます。手作業で探す回数を減らせますが、コードの重複や参照範囲のずれがあると、エラーを出さずに別の値を返すこともあります。

この記事では、三件の架空の商品一覧を使い、基本の式、未登録の扱い、重複の点検、複数列の取得、行を追加したときの確認まで進めます。最初の目標は、結果が出ることに加え、「どの目印を、どの範囲で探し、何を返したか」を説明できることです。複雑な関数を重ねる前に、小さな照合表で仕組みを確かめましょう。

XLOOKUPはMicrosoft 365、Excel 2021・2024など対応する環境で利用できますが、Excel 2016・2019では利用できません。受け取る人の版も確認してください。本文のコード・商品・単価は説明用の架空例で、実際の商品価格ではありません。画像は実際の操作画面ではなく、考え方を示す生成イメージです。

検索の目印を揃える｜コード・範囲・返す値を整理する

先に「何を手掛かりに、何を表示するか」を決める

XLOOKUPを使うときは、「P102という商品コードから、参考単価を表示する」のように、検索の目印と取り出す情報を一つずつ決めます。「別の表と照合したい」だけでは、名前で照合するのか番号で照合するのかが曖昧です。最初に決めておけば、式に入れる検索値・検索範囲・戻り範囲を整理しやすくなります。

人の名前や商品名は、同じものが複数あったり、表記が変わったりする場合があります。固有のコードが用意されているなら、そちらを使えるか確認しましょう。ただし、コードだから必ず一意で正しいとは限りません。枝番を省略していたり、年度ごとに同じ番号を再利用していたりする台帳もあるため、管理ルールを知ることが先です。

返す情報の意味も揃えます。単価と税込価格、所属部署と請求先部署など、似た見出しがあると選び間違えやすくなります。式を作る前に、元の一覧でどの列が必要かを確認してください。関数は指定した場所から値を取るだけなので、業務上必要な列を代わりに選んでくれるわけではありません。

三件の商品一覧で対応関係を見えるようにする

練習用シートのA1:C1へ「商品コード・商品名・参考単価」と入力します。A2:C4には、次の三件を入力します。ここでの参考単価は、対応する行を確かめるために設定した数字です。購入や仕入れの判断に使う価格ではありません。コードも実在の販売品を指しません。

※表は横にスクロールできます。

E1へ「検索するコード」、F1へ「参考単価」と入力し、E2にP102を入れます。F2に検索の式を作る予定です。最初から何百件も入力するより、P101なら500、P102なら800、P103なら300という対応を目で追える状態で試す方が、参照ミスを見つけやすくなります。

この表では、検索対象のA2:A4と戻す値のC2:C4が同じ行に対応しています。P102はA3にあり、返したい800はC3にあります。検索列と戻り列を別々に並べ替えたり、一方だけ行を削ったりすると対応関係が崩れます。表を扱うときは、一行に属する情報をまとめて動かすことを意識してください。

列の型と表記を揃え、勝手な正規化をしない

番号が「0012」のように先頭の0を含む場合、その0が識別上必要かを確認します。片方が文字列の0012、もう片方が数値の12では、期待どおり照合できないことがあります。数字に見えるからといって一括して数値へ変換すると、意味のある桁を失う可能性があります。元の管理ルールに従って形式を揃えましょう。

余計な空白や全角・半角の違いも調査対象です。ただし、似た文字列をすべて自動で同じとみなすのは危険です。商品コードの末尾の記号や枝番が、仕様やサイズの違いを表しているかもしれません。元の文字を残した列と、確認済みの照合用列を分ける方法なら、後から何を直したか追いやすくなります。

データを整える作業には、その変更を行ってよいかという判断も含まれます。外部から受け取った台帳を独断で書き換えるのではなく、担当者へ不一致の理由を確認してください。XLOOKUPの式を何度も変える前に、比較する二つの値が本当に同じ意味なのかを確かめる方が、根本的な解決につながる場合があります。

XLOOKUPが使える版を、渡す相手も含めて確認する

自分のExcelで動いていても、相手の環境で同じように計算できるとは限りません。とくに古い買い切り版へファイルを渡す場合は、XLOOKUPへの対応を事前に確認します。式の入力候補に出ない、関数名が認識されないときは、スペルだけでなく利用している製品や版も調べてください。

閲覧だけが必要なのか、相手がコードを変更して再計算する必要があるのかによって、渡し方は変わります。結果だけの資料なら値やPDFで渡す方法がありますが、その後に自動更新できなくなることを伝える必要があります。再計算が必要なら、相手が利用できる関数や形式へ合わせる設計を考えます。

対応版・構文・検索モードは、Microsoft公式のXLOOKUP関数で確認できます。公式ページの適用先一覧だけで判断せず、Excel 2016・2019では利用できないという注記も読んでください。新しい関数があるから古いブックを一括で置き換える、という進め方は避けましょう。

基本の式を組み立てる｜検索値・検索範囲・戻り範囲を分ける

三つの引数で最初の検索を試す

F2へ =XLOOKUP(E2,$A$2:$A$4,$C$2:$C$4) と入力してみます。E2が検索する値、A2:A4が探す範囲、C2:C4が返す値の範囲です。E2へP102を入れた状態なら、F2へ800が表示されることを確認します。式の見た目を一気に覚えるより、三つの役割を順に読んでみてください。

次にE2をP101へ変え、F2が500へ変わることを確かめます。P103なら300です。結果が全部同じなら、検索値がE2ではなく固定した文字列になっていないか、戻り範囲が一つのセルだけになっていないかを調べます。式に入力する参照と、シートの実際の位置が合っているかを一つずつ確認しましょう。

検索範囲と戻り範囲は、対応する行数を揃えます。A2:A4の三行を探すのに、C2:C5の四行を返す指定では、意図した対応になりません。エラーが出た場合、関数名を直す前に範囲の先頭・末尾・高さを見てください。同じ大きさでもA2:A4とC3:C5なら一行ずれているので、寸法だけでなく位置も重要です。

完全一致を明示し、未登録の表示を決める

商品コードの照合では、近い番号を代わりに選ばず、同じコードを探すのが基本です。XLOOKUPの既定は完全一致ですが、練習では条件を分かりやすくするため、=XLOOKUP(E2,$A$2:$A$4,$C$2:$C$4,"未登録",0,1) と指定してみます。未登録の表示、完全一致を示す0、先頭から探す1を明示した形です。

E2をP999へ変えれば、この練習表には存在しないため「未登録」と表示される想定です。ここで0を返すようにすると、本当に単価0の商品なのか、登録されていないのかが区別しにくくなります。エラーを消すことだけを目的にせず、入力した人が次に何を確認すべきか分かる表示を選びましょう。

「未登録」は、コードが世界のどこにも存在しないという意味ではありません。今の式が検索している範囲で見つからなかったという結果です。追加した行が範囲外、参照している一覧が古い、コードの表記が違うなどの場合もあります。表示を見たら登録担当へ依頼する、と決めつける前に、検索対象が正しいかを確かめてください。

※表は横にスクロールできます。

下の行へコピーするときは、動かす参照を決める

E3へP101、E4へP103を入力し、F2の式をF3:F4へコピーしてみます。検索値はE2からE3、E4へ変わり、検索範囲と戻り範囲はA2:A4・C2:C4のままになるのが今回の想定です。ドル記号を付けた参照は固定し、検索するコードのセルは行ごとに動かす使い方になります。

もし検索範囲がA3:A5、A4:A6とずれると、下の行ほど最初の商品が検索対象から外れてしまいます。先頭行では動くのに途中から未登録になる場合、この参照のずれが原因かもしれません。コピー後は最初の式だけでなく、途中と最後の式も選んで参照先を確認してください。

逆に検索値まで $E$2 に固定すると、全行が同じコードを調べます。結果が同じ値で埋まったときは、参照の固定しすぎを疑います。ドル記号は多く付ければ安全というものではありません。どこを動かし、どこを固定する必要があるかを表の設計から決めましょう。

先に手で求めた答えと、返された値を比べる

関数がエラーなしで動いたことと、正しい値を返したことは別です。練習の三件では、商品コード・商品名・参考単価の対応を目で確認できます。コードを順番に入力し、期待値と一致することを記録しましょう。未登録のコードを一つ入れ、未登録表示になることまで確認すると正常と例外の両方を検査できます。

実務で件数が多い場合も、代表的なコードを選び、元の一覧の行を直接調べて結果と比較します。先頭・中間・末尾、新しく追加した商品、単価0の項目など、性質が異なるものを確認すると、範囲の抜けや表示の問題を見つけやすくなります。すべて同じ条件の三件だけを確認して終わりにしないことが大切です。

照合表の目的が転記ミスを減らすことなら、確認したコードと結果を小さな検査表へ残しておきましょう。式を変更したときに同じテストを繰り返せます。作った本人だけが頭の中で覚えている状態より、次の担当者も同じ期待値を確かめられる形が望ましいです。

未登録と重複を見分ける｜見つからない理由を順に調べる

#N/Aは、検索対象を見直す手掛かりになる

見つからない場合の表示を指定していない式では、一致する値がないと#N/Aになることがあります。すぐに式を壊れたものとして扱うのではなく、何を探したか、どの範囲を見たか、元の一覧にその値があるかを確かめます。エラーは、データや範囲の問題に気付くきっかけにもなります。

確認の順番は、検索値のセル、検索範囲の位置、元データの該当行、型や空白です。検索値をコピーし、元表の検索機能で調べる方法もありますが、見つかった行がXLOOKUPの参照範囲に入っているかまで確認してください。元表全体には存在していても、式が末尾まで見ていない場合があります。

Microsoftの#N/Aエラーの確認方法でも、検索した値が参照先に存在するかを確認する考え方が示されています。表示を空欄へ変える前に、不一致の原因を調べる工程を残しましょう。何も表示されない表は見た目がすっきりしても、確認すべき未登録を隠すことがあります。

未入力の検索セルと、未登録コードを分ける

入力欄が空欄の行まで未登録と表示されると、本当に確認が必要な行が埋もれる場合があります。入力前の空欄と、入力したが見つからない状態は分けて扱うと便利です。たとえば =IF(E2="","",XLOOKUP(E2,$A$2:$A$4,$C$2:$C$4,"未登録",0,1)) のように、検索するセルが空なら空表示、値があれば検索する形を考えられます。

ただし、この式でもスペースだけが入ったセルを空欄と同じ扱いにするわけではありません。見た目が空でも、何か入力されていないかを点検します。空欄をなくすためにすべてへ仮コードを入れるような運用は、誤った照合結果を作る原因になりかねません。入力が必要な時点と、まだ入力しない状態を業務上区別してください。

検索対象の一覧に空のコード行がある場合も注意が必要です。まだ未登録の行を予約しているのか、単なる不要行なのか、一覧の管理方針を確認します。検索の式だけで曖昧な元表を補おうとするより、使わない空白行や未確定行をどう扱うか先に決める方が、引き継ぎや検査をしやすくなります。

戻り値の空欄と、本当の0を区別する

コードは見つかったものの、返す列が空欄になっている場合があります。この状態はコード未登録とは異なります。数式や表示形式によって0のように見えることもあるため、結果の0をそのまま「価格が0」と読み取らないようにしてください。元表の該当セルが空欄なのか、数値0なのかを確認します。

空欄を空文字で見せる工夫はできますが、それによって未確定を見逃さないかも考える必要があります。単価が決まっていない商品を扱うなら、単価の列とは別に「価格確認中」のような状態列を設ける方が分かりやすい場合があります。計算用の数値と、確認が必要であるという情報を無理に同じセルへ押し込まないようにしましょう。

練習用コピーでP103の参考単価を空にし、検索結果を観察してみます。その後、明示的に0を入力した場合と比較します。見た目が同じなら、利用する人は区別できません。台帳として何を表示すべきか決め、元の意味を失わせない設計へ整えてから本番へ使ってください。

同じコードが二つある場合は、先に元表を調べる

XLOOKUPは、通常は最初に見つかった一致に対応する値を返します。練習用コピーでP102の行を追加し、単価を900にした場合、どちらの行を採用するかを決めないまま関数へ任せると、意図しない値を使う可能性があります。重複があること自体をエラーとして知らせてくれるとは限りません。

まず、その重複が入力ミスなのか、時期別の価格履歴なのか、地域別の区分なのかを確認します。価格履歴ならコードだけで一意に決められず、適用日など別の条件が必要かもしれません。地域別なら地域とコードの組み合わせが検索の目印になる可能性があります。業務の意味を決めずに検索順だけを変えて解決しないようにしましょう。

一件だけ返したいのか、同じコードの全履歴を確認したいのかでも使う方法は変わります。XLOOKUPで返された一件を見て、該当行は一件しかなかったと判断しないことが重要です。重複の点検を別工程として持ち、想定した一意性を確かめてから照合結果を利用してください。

※表は横にスクロールできます。

返す方向と件数を確認｜左右の列・複数列・検索順を使い分ける

左側の列へ返すこともできるが、目印の一意性は必要

XLOOKUPでは、検索する列より左にある列を戻り範囲にすることもできます。たとえば商品名を検索して商品コードを返すなら、検索範囲をB2:B4、戻り範囲をA2:A4にする考え方です。E2へ収納箱を入れ、=XLOOKUP(E2,$B$2:$B$4,$A$2:$A$4,"該当なし",0,1) を使うと、この練習表ではP102が返る想定です。

ただし、実務では同じ商品名でサイズや色が異なることがあります。左へ返せるから商品名で安全に検索できる、という意味ではありません。商品名が重複しないか、正式名称と略称が混在していないかを確認します。必要ならコードや複数項目で特定する設計へ戻りましょう。

検索方向の自由度は、表を無理に並べ替えずに済む点で役立ちます。一方、列の位置に頼らず式を組めるぶん、どの列を検索し何を返すかを明確に書く習慣が大切です。見出しと参照範囲をセットで読み、隣の似た列を選んでいないかを検査してください。

商品名と単価を同時に返す場合は、出力場所を空ける

検索コードから商品名と参考単価を同時に取り出す例として、戻り範囲をB2:C4へ広げる方法があります。空いている場所で =XLOOKUP(E2,$A$2:$A$4,$B$2:$C$4,"未登録",0,1) を入力すると、対応する一行の複数の値が横へ広がる形で返る場合があります。たとえばF2へ式を入れるなら、F2:G2が使える状態にしておきます。

隣のセルに別の値が入っていると、展開先を確保できずスピル関連のエラーになることがあります。式を何度も直す前に、結果が広がる範囲に既存データや結合セルなどがないかを確認してください。周囲の重要な値を勝手に削除して場所を空けるのではなく、別の出力先を選ぶ方が安全な場合もあります。

動的配列の基本と制限は、Microsoft公式のスピル配列の説明で確認できます。複数の列を返すことは、重複する全行を返すこととは違います。一つの一致行から複数項目を得る例だと区別してください。

逆方向検索を「最新データの判定」と思い込まない

検索モードを-1にすると、末尾側から探す使い方ができます。しかし、最後の行が常に最新で正しいとは限りません。日付順に並んでいない履歴、途中に追加された修正行、古いデータを後から取り込んだ一覧では、末尾を選ぶだけでは最新の判定になりません。

たとえば、P102の価格履歴が上から2026年9月、10月、8月の順に並んでいるとします。最後の一致を返した場合、10月ではなく8月の行が選ばれる可能性があります。検索順を変える操作と、適用日が最大の行を選ぶ処理は同じではありません。いつの値を採用するかを決める必要がある仕事では、履歴の管理方法から確認します。

初心者向けの照合表では、まず一意のコードを持つ現在の一覧を用意し、完全一致で返す使い方に限定すると検査しやすくなります。近似一致や二分検索などのモードは、前提となる並び順や基準を理解してから検討します。処理を速くしたいという理由だけで、意味を理解しない数値へ切り替えないようにしてください。

※表は横にスクロールできます。

複数条件が必要なら、単純な連結にも注意する

コードだけでは一件に決まらない場合、部門や年度などを合わせて検索することを考えます。ただし、二つの文字列を単にくっつけると、別の組み合わせが同じ文字列になる場合があります。たとえばABとC、AとBCは、区切りなしでつなぐとどちらもABCです。複数の条件を一つの目印へ変える場合も、衝突しない設計が必要です。

区切り文字を使う方法でも、その文字が元データに入らないか、空欄をどう扱うかを確認します。複雑な配列式を使えば解決する場面もありますが、後任が読めない式になってしまうなら運用面の課題が残ります。補助列で確認できる形にするなど、検証と維持のしやすさを考えて選びましょう。

この記事では、単一のコードから一件を特定する基本までを練習します。複数条件や価格履歴を扱う本番表へ広げる場合は、「どの条件が揃えば一件とみなせるか」を先に定義してください。検索方法を増やすより、正しい結果の定義が決まっていることが重要です。

使い続けられる照合表へ｜追加・互換性・引き継ぎを整える

商品を追加したら、検索範囲と戻り範囲を一緒に広げる

今回の式はA2:A4とC2:C4を参照しています。A5:C5へ新商品P104を追加しても、範囲が変わっていなければ検索対象には入りません。P104が一覧の画面には見えているのに未登録となる場合は、式の末尾を確認してください。元表に存在することと、検索範囲に含まれることは別です。

範囲を広げるときは、検索側だけでなく戻り側も同じ行数へ合わせます。A2:A5とC2:C5に変更し、追加したコードが期待する値を返すかを試します。元からあるP101やP103も確認し、追加のための変更で既存の参照を壊していないかを確かめましょう。

行が頻繁に増える運用では、Excelテーブルと構造化参照を使う設計も考えられます。ただし、既存の式がテーブルへ自動的に追随すると思い込まず、実際の参照内容と追加テストを確認してください。列名を変更した場合や、別の一覧へ置き換えた場合にも、正しい対象を参照しているかの検査が必要です。

数式のエラーを一括で隠す前に、種類を分ける

見つからない場合の処理と、参照が壊れた場合の処理は区別しましょう。すべてのエラーを空欄へ置き換えると、未登録なのか、式が誤っているのか、参照先が削除されたのかが見えなくなります。台帳を利用する人には分かりやすい表示が必要ですが、管理する人には原因を調べられる情報も必要です。

#NAME?のように関数名や名前が認識されない場合、#REF!のように参照の問題がある場合など、症状ごとに見る場所が異なります。Microsoft公式の数式エラーの確認方法も参考に、表示された内容を記録して切り分けてください。

問い合わせるときは、個人情報や機密データを外へ出さず、必要に応じて架空のコードへ置き換えた最小の例を用意します。使用している版、式、期待値、実際の結果が分かると、原因を説明しやすくなります。画面全体をそのまま共有する前に、見せてよい情報だけになっているか確認しましょう。

渡す相手が再計算するのかを確認する

相手が閲覧だけをする場合と、コードを書き換えて使う場合では、渡す成果物が異なります。結果を値として保存すると、その時点の数字を確認しやすくなりますが、元の一覧を更新しても自動では変わりません。どの時点の値なのかを添え、更新が必要な場合の連絡先や手順を伝えます。

相手のExcelがXLOOKUPに対応していなければ、対応する関数で作り直す方法もあります。VLOOKUPやINDEXとMATCHなどを検討できますが、引数の意味や既定の一致方法が異なるため、関数名だけを置換してはいけません。Microsoft公式の検索関数の使い分けを確認し、正常・未登録・重複のテストを改めて行います。

元の一覧に顧客名や社内情報が含まれる場合、式の結果だけを渡すつもりでも、同じブックに元データが残っていることがあります。非表示シートを秘密情報の安全な保管場所と考えず、共有してよい範囲を確認してください。内部の管理用原本と、相手へ渡す閲覧資料を分けておくと、目的に合う情報だけを渡しやすくなります。

※表は横にスクロールできます。

検査項目と元データの責任者を残す

引き継ぎメモには、検索するコードの意味、元一覧の場所、検索範囲と戻り範囲、未登録時の対応、重複の確認方法を書きます。「XLOOKUPを使っている」という一文だけでは、何を正解として扱っているか分かりません。元の一覧を誰が更新し、いつの状態を正本とするかも明確にしましょう。

検査用の代表コードは、正常な先頭・中間・末尾、存在しない値、空欄、追加した値を含めます。結果が0の場合や、同じコードが複数ある場合をどう扱うかも確認しておくと安心です。式を変更するたびに同じテストを行い、変更した人以外にも結果を説明できる状態に整えます。

業務に合う台帳や書類の項目を探す段階では、bizoceanのテンプレートを仕事に活用する方法も参考になります。ひな形に入っている式や列を、そのまま自分の業務の正解とみなすのではなく、コードの一意性と更新方法を確認して使いましょう。入力・検索・確認の流れが揃って初めて、使い続けやすい照合表になります。

まずはP101・P102・P103の三件で、どの値が返ったかを手で確かめてください。そのうえで未登録、空欄、重複、追加を試すと、関数が得意なことと元データ側で管理すべきことが分かってきます。結果が表示された一瞬を完成とせず、次の更新でも同じ理由で正しく動くところまで整えるのが、仕事に使うためのポイントです。