※本記事には広告・アフィリエイトリンクが含まれます。
取引先コードを入力したら、隣に社名も出したい。元の一覧はもうある。でも、社名の列がコードの左にある。
うーん、表を並べ替えてからじゃないと検索できない？
XLOOKUPなら、検索する列と結果を返す列を別々に指定できます。コードがB列、社名がA列でも、同じ行の社名を取り出せるんです。左にあるからと、元の表を作り直すところから始めなくて大丈夫。
ただし、社名が出たら、それだけで合っているとは言い切れません。同じコードが二行あればどちらを出すのか。未登録と出たら、本当に一覧にないのか。この二つまで見て、仕事で使う検索にしていこうね。
この記事では説明用の四行だけで、完全一致の式、範囲の固定、重複と見つからない場合の調べ方を組み立てます。実在の会社や担当者のデータではありません。
操作仕様は2026年10月17日にMicrosoftの公式サポートを確認しています。XLOOKUPはExcel 2016・2019では使用できないという案内があるため、自分だけでなく共有先の版も先に確かめてください。
まず四行の一覧で、コードの左にある社名を探してみよう
XLOOKUPを目次に含むExcel関数の解説書を楽天市場で確認する
大きな仕事の表を開いたまま式を試すと、どこを探して何を出しているのか、見失いやすいよね。最初は同じブックの空いているシートなどに、小さな練習表を用意しよう。
以下はA1からC5へ置く説明用の例です。先頭の見出しが一行目、データは二〜五行目になります。
| A列・社名 | B列・取引先コード | C列・窓口 |
|---|---|---|
| 北町商店 | T101 | 田中 |
| 西野企画 | T102 | 佐藤 |
| 南原工房 | T103 | 山本 |
| 東川書店 | T104 | 鈴木 |
次にE2へ、探したいコードとしてT103を入力します。そしてF2に、次の式を入れる想定です。
=XLOOKUP(E2,$B$2:$B$5,$A$2:$A$5,"未登録",0)
この配置なら、B2:B5でT103を探し、同じ行のA列にある「南原工房」を返す式になります。コードの左側に結果の列があっても構いません。
探す値はE2。探す場所はB列。持ってくるものはA列。
この三つを声に出して分けると、長く見えた式が少しほどけるよ！
途中の「未登録」は、指定したコードが検索範囲で見つからない場合に出す文字です。会社が実際に存在しないという意味ではなく、この範囲で有効な一致を見つけられなかった、という表示として使います。
最後の0は一致モードで、完全一致の指定。XLOOKUPは既定でも完全一致ですが、今回は何を指定したかが読みやすいように0を明示しています。「だいたい近いコードでよい」とはしていません。
窓口も出したいなら、G2へ次のように戻り範囲だけを変える式を置けます。
=XLOOKUP(E2,$B$2:$B$5,$C$2:$C$5,"未登録",0)
同じT103を探し、C列の「山本」を返す組み立てです。これは一つのセルへ一つの結果を返す例。まず社名と窓口を別々に確かめて、検索している行が同じかを見よう。
本番のデータへ置き換えるときは、説明中のA・B・C列を丸ごと信じて入力せず、自分の表の見出しを確認してください。列の位置が違っていれば、検索範囲と戻り範囲もその表に合わせて変えます。
範囲の最初と最後をそろえよう。「$」はコピーするときの固定だよ
一件は出せた。次の行にも式をコピーしたい。
そこで見るのが、式の中のドル記号です。E2には付けず、検索範囲と戻り範囲には$B$2:$B$5、$A$2:$A$5と付けています。
E2は、下へコピーすればE3、E4と探す入力欄を移したいところ。一方、元になる四行の一覧は同じ場所を見てほしいので、列と行を固定する、という分け方です。
ドル記号があるとコードを完全一致で探せる、という意味ではありません。完全一致はさっきの0の役割。範囲の固定と検索方法は、式の中でも別の仕事をしているんだね。
もう一つ気を付けたいのは、探す範囲と返す範囲の行です。今回ならB2:B5とA2:A5で、どちらも二行目から五行目。コードと社名の対応が、同じ位置でそろっています。
たとえば検索をB2:B5、戻りをA3:A6としたらどうだろう。どちらも四つのセルですが、行が一つずれています。範囲の長さが同じというだけでは、元の対応が正しい保証になりません。
元一覧を並べ替える場合も、コードの列だけを動かして、社名や窓口を置き去りにしないでね。一行が一件の情報を表す例なので、各列の対応が崩れると、関数が式どおり動いても別の取引先を返してしまいます。並べ替えたあとも、コードと社名が同じ組になっているかを見よう。
「エラーが出なかったから大丈夫」とする前に、T103の行を元の一覧で見て、返ってきた社名と窓口が本当に同じ行か確かめよう。ここは、画面に何か出たことと正しい検索を分ける確認です。
行を追加するときも、今回の式は五行目までしか指定していません。六行目に新しい取引先を書いたら、自動的にこの固定範囲へ加わったことにはならないので、両方の範囲をそろえて広げる必要があります。
たとえば十行目まで使うなら、検索を$B$2:$B$10、戻りを$A$2:$A$10にする。同じように窓口の式もC10までにします。一方だけ広げて、もう片方を置いたままにしないでね。
表の更新が多いなら、Excelのテーブル機能などを学ぶ選択肢もありますが、この記事では固定セル範囲の例に絞っています。今の段階では、一覧を増やした日にどこを変えるかを分かる状態にするのが先です。
共有用に短いメモを添えるなら、「元一覧はA2:C5、検索コードはE列、社名はF列、窓口はG列」と書けます。式を作った人だけが範囲を知っている状態にしないこと。明日続きを触る人にも、同じ場所が見えるようにしておこう。
同じコードが二行あったら？最初の一致と「最新」は同じじゃない
社名が出た。でも、そのコードは一覧に一件だけだった？
ここ、検索できたときこそ見たいところです。XLOOKUPの既定の検索モードは先頭からの検索。同じ値が複数ある場合には、最初に見つかった一致に対応する結果を返します。
説明用に、さっきのB5だけをT104からT103へ変えたと考えてみてください。四行目にも五行目にもT103があります。A4は南原工房、A5は東川書店のままとします。
この状態で最初の式を使うなら、先に見つかる四行目側の「南原工房」が返る組み立てです。結果が一つ出るからといって、コードの重複が解決されたわけではありません。
二つの社名に同じコードを付けた入力ミスなのか、同じ取引先の古い行と新しい行なのか。そこは数式だけでは判断できないんだね。
末尾から探す検索モードもあります。最後の引数に-1を指定する例は、次の式です。
=XLOOKUP(E2,$B$2:$B$5,$A$2:$A$5,"未登録",0,-1)
ただし、これで自動的に「いちばん新しい取引先情報」を選べる、とは言わないでください。探す向きが末尾からになるだけで、日付の新旧を判定する指示は入っていません。
行が日付順に並んでいない、途中に訂正行を挿入している、担当者ごとに追記場所が違う。そのような表なら、下にあることだけでは新しい情報と決められないよね。
仕事の一覧で一コード一件というルールなら、まず重複した行を調べるのが先です。どちらを正しい記録にするかを担当者と確認し、元のデータの扱いを決める。末尾検索で表示を変えて、入力ミスを見えなくする使い方にはしないでおこう。
実際に重複を確認するときは、元一覧のコード列で対象値を探し、同じコードの行を並べて見ます。社名だけでなく、窓口、更新日があるならその日付、適用期間など、重複する理由を確認するための列も読むと判断しやすいです。
最初に見つかった行がよいのか、最後の行がよいのか、そもそも重複を許さないのか。式を決める前にそこを一つ決めよう。関数の便利さが、一覧のルールを勝手に代わってくれるわけではないんです。
「未登録」が出ても、すぐ社名を手入力しない。型と空白を見てみて
取引先は確かにあるはずなのに、未登録と出る。
急いでいると、結果のセルへ直接社名を打って先に進みたくなるよね。でも、元の一覧を検索できていない理由は、そのまま残ります。まず範囲と入力値を見直そう。
最初は、指定した検索範囲の中に対象コードが入っているか。追加した六行目を式が見ていないなら、コード自体が正しくても一致しません。表全体にあることと、数式が探している場所にあることは別です。
次に、コードの前後へ余分な空白が入っていないかを確かめます。画面ではT103に見えても、コピーしたときの空白が付いている可能性があります。Microsoftのエラー解説も、余分なスペースを確認項目にしています。
ただし、全データから空白を一括で消す前には、その空白がコードの一部として必要でないかを確認してね。どこで入った文字かを見ずに、一覧全体を別の値へ変える修正は急がなくて大丈夫です。
数字だけのコードなら、数値として入っているか、文字列として入っているかも確認したいところ。Microsoftは、検索する値と元データの型が違う場合を、#N/Aなどの原因として挙げています。
ここで注意したいのが、0012のように先頭のゼロを持つコード。何でも数値へ変換すると12になり、管理上必要な桁を失うおそれがあります。コードを数として扱うか、文字列として扱うかは、元の一覧のルールに合わせてそろえよう。
今回は「見つからない場合」の引数に未登録を入れていますが、表示名を変えたことは、データが登録されていないと調査できた証拠ではありません。範囲の外、空白、型の不一致も、順に見てから判断します。
また、この引数は何でもエラーを消すための指定ではありません。参照しているセルに別のエラーがある、指定する範囲がおかしいなどの問題まで、未登録の文字を入れれば全部直る、と考えないでね。
一つのコードで直ったら、一覧にある別のコードでも元の行と照合する。存在しない説明用コードでは、未登録の表示になるかを見る。件数の多い本番データへ広げる前に、正しい一致と見つからない場合の両方を確認する順番です。
共有前に相手のExcelも確認しよう。関数本の対応版は一つずつ読む
今すぐ使えるかんたんExcel関数の版とISBNを楽天市場で確認する
自分の画面では出せた。さあ同僚へ送ろう、の前に、相手が使うExcelの版も見てね。
Microsoftの公式案内は、XLOOKUPはExcel 2016と2019では使用できないとしています。ファイル名が同じExcelブックだから、どの版でも同じ関数を使えるというわけではありません。
共有先が対応していないなら、今の式を渡すだけで解決したことにはしないでください。相手が更新して使う必要があるのか、結果の確認だけなのかを整理し、使える関数や渡し方を検討することになります。元の計算用ファイルを失うような上書きは避けよう。
練習を続ける本の候補は、日花弘子さん著『今すぐ使えるかんたん Excel関数［Office 2024/2021/2019/Microsoft 365対応版］』。技術評論社の書誌では2025年9月3日発売、320ページ、紙のISBNは9784297150426です。
出版社の公開目次には、相対参照・絶対参照、エラー、TRIM、XLOOKUPなどの項目があります。今回つまずいた場所を、自分のペースで戻って調べたい人向けの参照候補です。本の全編を実読した使用感や、ここに示した取引先の例がそのまま収録されているという紹介ではありません。
題名に2019対応とあっても、本の中のすべての関数がExcel 2019で使える、という意味にはしないでね。必要な関数ごとの対応状況を確認することは、書籍を選ぶときにも残ります。
まずは四行の一覧で、一つのコードから社名を出す。次に、結果が元の同じ行かを見る。それができたら、コピーする範囲と共有先へ。
表の左側に社名があるからと、全部を組み直さなくて大丈夫。どこを探して、何を返す式なのかが分かる小さな一件から、仕事で読める検索を作っていこう！
参照：Microsoft・XLOOKUP関数、Microsoft・#N/Aの原因確認、技術評論社の書誌・目次。式と結果は説明用の配置に対する例で、個別環境での実機動作確認報告ではありません。
