検索列が2列に分かれている表を、1つの検索値で探す方法
💡 やりたいこと
Aシートには 東京001 のように検索値が1セルで入っている。一方、マスタ表の検索列は 東京 と 001 の2列に分かれている。そんな時に、検索列側の2列をつないで、1つの検索値と一致させる 方法です。
たとえばAシートのC2が
東京001 なら、Bシート側の検索列Aと検索列Bを B!A:A&B!B:B でつなぎ、同じ 東京001 という形にして照合します。
まずは完成形:XLOOKUPで書く
AシートのD2など、結果を表示したいセルに次の数式を入れます。
| 部分 | 意味 |
|---|---|
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山田 |
実際のシートは次のような形です。
| C | D | |
| 1 | 検索キー | 結果 |
| 2 | 東京001 | 山田 |
| 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シート側も同じ形で結合します。
検索値と検索範囲のキーの形をそろえるのが大事です。A側が 東京-001 なら、B側も 東京-001 を作ります。
基本は「作業列あり」。慣れてきたら「作業列なし」も使う
結合キー検索を最初に覚えるなら、まずはBシート側に作業列を作る方法が一番わかりやすいです。たとえばBシートのD列に検索用キーを作ります。
そのうえで、Aシート側では作業列Dを検索範囲にします。
BシートのD列に
東京001 が実際に見えるので、「何と何を照合しているのか」が理解しやすく、ミスの原因も追いやすくなります。
一方、この記事で紹介している B!A:A&B!B:B のような書き方は、作業列を追加せずに数式の中だけで結合キーを作る方法です。
| 方式 | メリット | デメリット | 速度面 |
|---|---|---|---|
| 作業列あり | キーが目に見える。初心者でも追いやすい。複数の数式で同じキーを使い回せる。 | 元データに列を追加する必要がある。CSV更新や貼り替え時に、作業列の維持が手間になることがある。 | 速くなりやすい。結合キーを一度作っておけば、検索のたびにA列&B列を再計算しなくて済む。 |
| 作業列なし | 元データを加工しない。表の行や列、特に作業列を増やさずに使える。1つの数式で完結する。 | 数式が少し読みにくい。途中の結合キーが見えないため、慣れるまでは原因調査がしにくい。 | 大量データでは重くなることがある。数式ごとに B!A:A&B!B:B の配列を作るため。 |
数百行~数千行で、数式の本数も少ないなら作業列なしはかなり便利です。数万行以上、または同じ検索式を大量に並べる場合は、作業列あり・Excelテーブル・Power Queryでの結合を検討すると安定します。
INDEX + MATCHで書く場合
作業列なしで旧Excel寄りにも考えるなら、次に候補になるのはINDEX + MATCHです。MATCHで一致する行番号を探し、INDEXでその行のC列を返します。
見つからない場合を空白にするなら、IFERROR を使います。
Excel 2019以前では、このような配列を使う数式は
Ctrl+Shift+Enter で確定が必要になる場合があります。Excel 2021 / 2024 / Microsoft 365 では、通常の Enter で動くケースが多くなっています。
VLOOKUPで書く場合(実務では補足扱い)
VLOOKUPでも一応できます。ただし、このケースであえてVLOOKUPを選ぶことは多くありません。作業列なしでやるには CHOOSE や HSTACK で仮想表を作る必要があり、数式が読みにくくなるためです。
見つからない場合を空白にするなら、IFERROR で包みます。
1列目に
B!A:A&B!B:B、2列目に B!C:C を並べた仮想表を作り、その1列目で検索して2列目を返します。
HSTACKが使える環境(Excel 2024 / Microsoft 365)なら、こうも書ける
考え方は同じです。ただ、HSTACKが使える環境ならXLOOKUPも使えることが多いため、VLOOKUPにこだわる場面は少なめです。
複数列をまとめて持ってきたい場合
BシートのC列からE列まで返したいなら、XLOOKUPは戻り範囲を B!C:E にするだけです。
一致した行のC~E列が、Aシート側に横方向へ展開されます。
INDEX + MATCHで複数列を返す
0 は「該当行の列全体を返す」という指定です。複数列の横展開は、動的配列に対応したExcelで特に扱いやすくなります。
VLOOKUPで複数列を返す場合(補足)
書けますが、かなり読みにくくなります。新しく組むなら、XLOOKUPかINDEX+MATCHを優先する方が実務では扱いやすいです。
実務では全列指定より範囲指定がおすすめ
A:A のような全列指定でも書けますが、データ量が多いブックでは重くなることがあります。行数の上限が読めるなら、範囲を絞ると安定します。
B!A:A&B!B:B は、Excel内部で1,048,576行分の結合配列を作る可能性があります。実データが1万行までなら、上のように 2:10000 などへ絞るだけでもかなり軽くなります。
余分なスペースが混ざる場合
001 のようなコードが数値化される場合
B列の 001 がExcel上で数値の 1 になっていると、結合結果は 東京1 になり、東京001 と一致しません。その場合は文字列として保持するか、TEXT で桁をそろえます。
おすすめ順
| 順位 | 方法 | 数式 | 向いている場面 |
|---|---|---|---|
| 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運用に合わせたい場合。ただし少し読みにくくなります。 |
結合キー検索そのものはVLOOKUPでもINDEX+MATCHでも可能ですが、実務で後から読み返しやすいのは
XLOOKUP です。ただし、大量データで速度を優先するなら作業列ありも有力です。旧Excel対応が必要なら INDEX+MATCH を候補にします。