検索列が2列に分かれている表を、1つの検索値で探す方法

💡 やりたいこと

Aシートには 東京001 のように検索値が1セルで入っている。一方、マスタ表の検索列は 東京 と 001 の2列に分かれている。そんな時に、検索列側の2列をつないで、1つの検索値と一致させる 方法です。

ポイントは「検索列2列を、検索値と同じ形にそろえる」ことです。
たとえばAシートのC2が 東京001 なら、Bシート側の検索列Aと検索列Bを B!A:A&B!B:B でつなぎ、同じ 東京001 という形にして照合します。

まずは完成形:XLOOKUPで書く

AシートのD2など、結果を表示したいセルに次の数式を入れます。

=XLOOKUP(C2,B!A:A&B!B:B,B!C:C,"")
XLOOKUPの引数
部分 意味
C2 Aシート側の検索値。例では 東京001。
B!A:A&B!B:B Bシートの検索列2列をつないだ、仮想の検索列。
B!C:C 一致した行から返したい列。
"" 見つからない場合は空白を返す。
この方法の大きなメリットは、表を増やさずにそのまま使えることです。
マスタ表に作業列を追加したり、列構成を変えたりせず、検索列Aと検索列Bを数式の中だけで一時的につなぎます。CSVから受け取った表、他部署が管理している表、列追加しにくい共有ブックでも使いやすいのが強みです。

図解:検索列2列をつないで、C2の検索値と一致させる

このケースでは、Aシートの C2 にある 東京001 をそのまま探します。ただしBシート側の検索列は2列に分かれているため、B!A2&B!B2 で一時的に 東京001 を作り、C2と一致させます。一致した行の B!C2、つまり 山田 をAシートへ返します。

照合の流れ
1. 探したい検索値 2. 検索列2列をつないだ値 3. 一致したら返す値
C2
東京001
B!A2&B!B2
東京 + 001 = 東京001
B!C2
山田

実際のシートは次のような形です。

Aシート
Aシート
C D
1 検索キー 結果
2 東京001 山田
Bシート
Bシート
A B C
1 地域 コード 担当者
2 東京 001 山田
3 大阪 001 佐藤

よくあるケース別の書き方

基本の考え方は同じです。検索値の形 と 検索列をつないだ形 が一致するように数式を作ります。

ケース別の考え方
ケース 検索値 検索列側で作る形 式の例
2列をそのままつなぐ 東京001 東京 + 001 =XLOOKUP(C2,B!A:A&B!B:B,B!C:C,"")
区切り文字つきで探す 東京-001 東京 + - + 001 =XLOOKUP(C2,B!A:A&"-"&B!B:B,B!C:C,"")
コードの桁数をそろえる 東京001 東京 + TEXT(1,"000") =XLOOKUP(C2,B!A:A&TEXT(B!B:B,"000"),B!C:C,"")
Aシート側も2セルに分かれている A2 + B2 B!A:A + B!B:B =XLOOKUP(A2&B2,B!A:A&B!B:B,B!C:C,"")
検索列が3列ある 東京001A 地域 + コード + 区分 =XLOOKUP(C2,B!A:A&B!B:B&B!C:C,B!D:D,"")
一致行から複数列を返す 東京001 東京 + 001 =XLOOKUP(C2,B!A:A&B!B:B,B!C:E,"")
最初に見る場所:
「自分の検索値がどんな形か」を先に見ます。次に、検索列側の2列または3列をつないだ時に同じ形になるかを考えると、式を組み立てやすくなります。

区切り文字を入れると、キーの取り違えを防げる

東京 と 001 を単純に結合すると 東京001 になります。ただしデータによっては、単純結合では紛らわしいケースがあります。

例:AB + C も、A + BC も、単純結合すると ABC になります。

AシートC列が 東京-001 のように区切り文字付きなら、Bシート側も同じ形で結合します。

=XLOOKUP(C2,B!A:A&"-"&B!B:B,B!C:C,"")

検索値と検索範囲のキーの形をそろえるのが大事です。A側が 東京-001 なら、B側も 東京-001 を作ります。

基本は「作業列あり」。慣れてきたら「作業列なし」も使う

結合キー検索を最初に覚えるなら、まずはBシート側に作業列を作る方法が一番わかりやすいです。たとえばBシートのD列に検索用キーを作ります。

=A2&B2

そのうえで、Aシート側では作業列Dを検索範囲にします。

=XLOOKUP(C2,B!D:D,B!C:C,"")
作業列ありは、仕組みを目で確認できるのが強みです。
BシートのD列に 東京001 が実際に見えるので、「何と何を照合しているのか」が理解しやすく、ミスの原因も追いやすくなります。

一方、この記事で紹介している B!A:A&B!B:B のような書き方は、作業列を追加せずに数式の中だけで結合キーを作る方法です。

=XLOOKUP(C2,B!A:A&B!B:B,B!C:C,"")
作業列あり・なしの比較
方式 メリット デメリット 速度面
作業列あり キーが目に見える。初心者でも追いやすい。複数の数式で同じキーを使い回せる。 元データに列を追加する必要がある。CSV更新や貼り替え時に、作業列の維持が手間になることがある。 速くなりやすい。結合キーを一度作っておけば、検索のたびにA列&B列を再計算しなくて済む。
作業列なし 元データを加工しない。表の行や列、特に作業列を増やさずに使える。1つの数式で完結する。 数式が少し読みにくい。途中の結合キーが見えないため、慣れるまでは原因調査がしにくい。 大量データでは重くなることがある。数式ごとに B!A:A&B!B:B の配列を作るため。
実務の目安:
数百行~数千行で、数式の本数も少ないなら作業列なしはかなり便利です。数万行以上、または同じ検索式を大量に並べる場合は、作業列あり・Excelテーブル・Power Queryでの結合を検討すると安定します。

INDEX + MATCHで書く場合

作業列なしで旧Excel寄りにも考えるなら、次に候補になるのはINDEX + MATCHです。MATCHで一致する行番号を探し、INDEXでその行のC列を返します。

=INDEX(B!C:C,MATCH(C2,B!A:A&B!B:B,0))

見つからない場合を空白にするなら、IFERROR を使います。

=IFERROR(INDEX(B!C:C,MATCH(C2,B!A:A&B!B:B,0)),"")
旧バージョンExcelでは注意。
Excel 2019以前では、このような配列を使う数式は Ctrl+Shift+Enter で確定が必要になる場合があります。Excel 2021 / 2024 / Microsoft 365 では、通常の Enter で動くケースが多くなっています。

VLOOKUPで書く場合(実務では補足扱い)

VLOOKUPでも一応できます。ただし、このケースであえてVLOOKUPを選ぶことは多くありません。作業列なしでやるには CHOOSE や HSTACK で仮想表を作る必要があり、数式が読みにくくなるためです。

=VLOOKUP(C2,CHOOSE({1,2},B!A:A&B!B:B,B!C:C),2,FALSE)

見つからない場合を空白にするなら、IFERROR で包みます。

=IFERROR(VLOOKUP(C2,CHOOSE({1,2},B!A:A&B!B:B,B!C:C),2,FALSE),"")
CHOOSE({1,2}, ... ) のイメージ
1列目に B!A:A&B!B:B、2列目に B!C:C を並べた仮想表を作り、その1列目で検索して2列目を返します。

HSTACKが使える環境(Excel 2024 / Microsoft 365)なら、こうも書ける

=VLOOKUP(C2,HSTACK(B!A:A&B!B:B,B!C:C),2,FALSE)

考え方は同じです。ただ、HSTACKが使える環境ならXLOOKUPも使えることが多いため、VLOOKUPにこだわる場面は少なめです。

複数列をまとめて持ってきたい場合

BシートのC列からE列まで返したいなら、XLOOKUPは戻り範囲を B!C:E にするだけです。

=XLOOKUP(C2,B!A:A&B!B:B,B!C:E,"")

一致した行のC~E列が、Aシート側に横方向へ展開されます。

INDEX + MATCHで複数列を返す

=INDEX(B!C:E,MATCH(C2,B!A:A&B!B:B,0),0)

0 は「該当行の列全体を返す」という指定です。複数列の横展開は、動的配列に対応したExcelで特に扱いやすくなります。

VLOOKUPで複数列を返す場合(補足)

=VLOOKUP(C2,CHOOSE({1,2,3,4},B!A:A&B!B:B,B!C:C,B!D:D,B!E:E),{2,3,4},FALSE)

書けますが、かなり読みにくくなります。新しく組むなら、XLOOKUPかINDEX+MATCHを優先する方が実務では扱いやすいです。

実務では全列指定より範囲指定がおすすめ

A:A のような全列指定でも書けますが、データ量が多いブックでは重くなることがあります。行数の上限が読めるなら、範囲を絞ると安定します。

=XLOOKUP(C2,B!A$2:A$10000&B!B$2:B$10000,B!C$2:C$10000,"")
速度を気にするなら、全列指定 + 作業列なしは避けるのが無難です。
B!A:A&B!B:B は、Excel内部で1,048,576行分の結合配列を作る可能性があります。実データが1万行までなら、上のように 2:10000 などへ絞るだけでもかなり軽くなります。

余分なスペースが混ざる場合

=XLOOKUP(TRIM(C2),TRIM(B!A$2:A$10000)&TRIM(B!B$2:B$10000),B!C$2:C$10000,"")

001 のようなコードが数値化される場合

B列の 001 がExcel上で数値の 1 になっていると、結合結果は 東京1 になり、東京001 と一致しません。その場合は文字列として保持するか、TEXT で桁をそろえます。

=XLOOKUP(C2,B!A$2:A$10000&TEXT(B!B$2:B$10000,"000"),B!C$2:C$10000,"")

おすすめ順

使い分け
順位 方法 数式 向いている場面
1位 XLOOKUP =XLOOKUP(C2,B!A:A&B!B:B,B!C:C,"") 一番読みやすく、エラー時の空白返しも中で完結します。
2位 INDEX + MATCH =IFERROR(INDEX(B!C:C,MATCH(C2,B!A:A&B!B:B,0)),"") XLOOKUPが使えない環境でも応用しやすいです。
3位 VLOOKUP =IFERROR(VLOOKUP(C2,CHOOSE({1,2},B!A:A&B!B:B,B!C:C),2,FALSE),"") 既存のVLOOKUP運用に合わせたい場合。ただし少し読みにくくなります。
迷ったらXLOOKUPでOKです。
結合キー検索そのものはVLOOKUPでもINDEX+MATCHでも可能ですが、実務で後から読み返しやすいのは XLOOKUP です。ただし、大量データで速度を優先するなら作業列ありも有力です。旧Excel対応が必要なら INDEX+MATCH を候補にします。