はじめに
Excel で「SQL の LEFT JOIN 的なことを関数で実現したい」「複数のキーでマッチさせたい」ということは意外とよくあります。
本記事では、複数キーを使って LEFT JOIN を行い、ヒットした行の複数列をまとめて取得するセル関数を紹介します。
サンプルデータ
以下のような「左テーブル」と「右テーブル」を用意します。
左テーブル（H:J）
| クラス | 番号 | 氏名 |
|---|---|---|
| A | 1 | 佐藤 |
| A | 2 | 鈴木 |
| B | 1 | 田中 |
| B | 2 | 伊藤 |
| C | 1 | 山本 |
| C | 2 | 加藤 |
右テーブル（A:F）
| クラス | 番号 | 国語 | 算数 | 理科 | 社会 |
|---|---|---|---|---|---|
| A | 4 | 95 | 93 | 90 | 94 |
| B | 1 | 88 | 90 | 91 | 87 |
| A | 2 | 90 | 85 | 88 | 92 |
| A | 1 | 80 | 78 | 82 | 79 |
| C | 2 | 75 | 80 | 77 | 78 |
| C | 1 | 80 | 77 | 60 | 65 |
セル関数（LEFT JOIN 相当）
以下の式を、K2セルに入力します。
=LET(
範囲, A:F,
キー1, H2:H7,
キー2, I2:I7,
マッチ結果, MATCH(キー1 & CHAR(9) & キー2, INDEX(範囲,,1) & CHAR(9) & INDEX(範囲,,2), 0),
IFERROR(INDEX(範囲, マッチ結果, {3,4,5,6}), "")
)
仕組みの解説
結合キーを作る
2つのキー（クラス × 番号）を結合して「検索キー」を作ります。
キー1 & CHAR(9) & キー2
CHAR(9)「タブ」 を区切り文字に使う理由は、
通常のデータに現れる可能性が低く、衝突しにくい文字だからです。
別に他の文字（例：","）でも値に含まれることがない文字であれば何でも良いです。
MATCH で行番号を探す
右テーブル側の同じ構造の結合キーと比較し、対応する行番号を配列で取得します。
MATCH(検索キー, 結合キー一覧, 0)
一致しないところは #N/A になります。
INDEX で複数列を同時に返す
INDEX(範囲, マッチ結果, {3,4,5,6})
ここが今回のポイントで、
INDEX に 複数行の配列を渡すと、複数行を一度に返せ、
列番号の配列 {3,4,5,6} を渡すと、複数列を一度に返せる というテクニックです。
これは、私が書いた過去の記事の手法を使用しています
IFERROR で LEFT JOIN を実現
右側に存在しないキーの場合はIFERROR関数で空文字 "" を返すようにしています。
まとめ
Excel だけで LEFT JOIN（複数キー） を再現できる
MATCH + INDEX の組み合わせでVLOOKUPの代用
INDEX に 行番号配列と列番号配列 を渡して2次元の表を返すのがポイント
SQL 風の JOIN 処理をシート関数で再現できる
※ Excel 365 / 2021 以降のスピル対応環境を前提としています