商品コードの先頭一致でデータを引き出す｜INDEX・MATCH・LEFT実務テクニック
「商品コードの先頭3文字だけで、別テーブルの地域名を自動取得したい」——こんな場面で詰まったことはありませんか？
VLOOKUPは便利ですが、「コードの一部だけを検索キーにする」という前方一致的な使い方には対応できません。完全一致か、ワイルドカードを使ったあいまい一致しかサポートしていないためです。
この記事を読めば、INDEX・MATCH・LEFTの3関数を組み合わせて、コード体系がバラバラな実務データでも自動抽出できるようになります。エラー対処や応用例も含めて、そのままコピーして使える数式とともに解説します。
INDEX・MATCHの基本をおさらい
INDEX関数
=INDEX(範囲, 行番号, [列番号])
指定した範囲の中から、行番号・列番号で指定したセルの値を返します。
MATCH関数
=MATCH(検索値, 検索範囲, [照合の種類])
検索値が検索範囲の中で何番目にあるかを数値で返します。照合の種類を0にすると完全一致検索になります。
2つを組み合わせるとVLOOKUPを超える
=INDEX(返したい列, MATCH(検索値, 検索する列, 0))
MATCHで「何行目か」を調べ、INDEXでその行の値を取り出す仕組みです。VLOOKUPと違い、検索列の左側にある値も取得できるのが大きな強みです。
なぜLEFTが必要なのか？
MATCHだけでは前方一致ができない理由
たとえば "TKY-001" というコードを持つデータから、先頭3文字の "TKY" だけをキーに別テーブルを検索したいとします。
MATCHの検索値に "TKY-001" をそのまま渡しても、検索範囲に "TKY" しかなければ#N/Aエラーになります。ワイルドカード（"TKY*"）を使う方法もありますが、数式が複雑になり、複数条件との組み合わせが難しくなります。
ここで登場するのがLEFT関数です。
LEFT関数のおさらい
=LEFT(文字列, 文字数)
文字列の左端から指定した文字数だけ取り出します。LEFT("TKY-001", 3) は "TKY" を返します。
「LEFTで検索キーを整形してからMATCHに渡す」——この発想が、前方一致検索を実現する核心です。
実践例：商品コードの先頭から地域名を取得する
実際のデータで試してみましょう。
データ構成
商品マスタ（Sheet1）
| 商品コード | 商品名 |
|---|---|
| TKY-001 | ノートA |
| OSK-002 | ノートB |
| NGY-003 | ノートC |
地域マスタ（Sheet2）
| 地域コード | 地域名 |
|---|---|
| TKY | 東京 |
| OSK | 大阪 |
| NGY | 名古屋 |
使用する数式
Sheet1のC2セルに入力します。
=INDEX(Sheet2!$B$2:$B$4, MATCH(LEFT(A2,3), Sheet2!$A$2:$A$4, 0))
数式の読み方（3ステップ）
LEFT(A2, 3)→"TKY-001"の先頭3文字"TKY"を取り出すMATCH("TKY", Sheet2!$A$2:$A$4, 0)→ 地域マスタのA列で"TKY"が何行目にあるか調べる（→ 1）INDEX(Sheet2!$B$2:$B$4, 1)→ 地域名列の1行目、つまり"東京"を返す
📸 ［完成イメージのスクリーンショット挿入箇所：Sheet1のC列に東京・大阪・名古屋が表示された状態］
応用例：部署コード＋年度で予算を検索する
現場ではさらに複雑なケースも発生します。たとえば、管理システムからエクスポートしたデータに部署コードと余分な文字列が混在していて、年度と組み合わせた複合キーで予算テーブルを引きたい、というシーンです。
データ構成
予算テーブル（A列：キー、B列：予算）
| キー（部署＋年度） | 予算 |
|---|---|
| HR2023 | 500,000 |
| IT2023 | 800,000 |
| HR2024 | 600,000 |
| IT2024 | 900,000 |
検索シート（D列：部署コード入り文字列、E列：年度）
- D2：
"HR-東京"のように部署コードに余分な文字が付いている - E2：
2023
使用する数式
=INDEX($B$2:$B$5, MATCH(LEFT(D2,2)&E2, $A$2:$A$5, 0))
LEFT(D2, 2)→"HR-東京"から"HR"を抽出"HR" & "2023"→"HR2023"という複合キーを生成- MATCHでA列を検索し、INDEXで予算を返す
LEFTで文字列を整形してからキーを組み立てるこの手法は、現場の「きれいでないデータ」に対して非常に効果的です。
よくあるエラーと対処法
#N/Aエラーが出る場合
MATCHで値が見つからないときに発生します。主な原因と対策は以下のとおりです。
| 原因 | 対策 |
|---|---|
| LEFTで切り出す文字数が間違っている | データを目視確認し、文字数を修正する |
| スペースや見えない文字が混入している | TRIMを組み合わせる（後述） |
| 数値と文字列の型が不一致 | TEXTやVALUEで型を統一する |
TRIMを組み合わせる際の注意点
=INDEX(範囲, MATCH(TRIM(LEFT(A2,3)), TRIM(検索範囲), 0))
TRIM(検索範囲) のように範囲全体にTRIMを適用する場合、配列数式になることがあります。
- Excel 2019以前：Ctrl+Shift+Enterで入力する必要があります
- Excel 365 / Excel 2021：動的配列に対応しているため、Enterのみで動作します
お使いのExcelのバージョンに応じて入力方法を変えてください。
IFERRORでエラーを安全に処理する
=IFERROR(INDEX(Sheet2!$B$2:$B$4, MATCH(LEFT(A2,3), Sheet2!$A$2:$A$4, 0)), "該当なし")
エラーが発生した場合に "該当なし" などを表示できます。本番運用のシートでは必ず組み合わせておくことをおすすめします。
まとめ：この組み合わせが活きる場面
| シーン | 効果 |
|---|---|
| コードの先頭N文字で別テーブルを参照したい | 前方一致的な柔軟な検索が実現できる |
| データに余分な文字列が混在している | LEFTとTRIMで検索キーを標準化できる |
| 複数の文字列フィールドを結合して検索したい | &演算子との組み合わせで複合キー検索ができる |
考え方は常にシンプルな3ステップです。
- LEFTで検索キーを整形する
- MATCHで対象の行番号を特定する
- INDEXで目的の値を取り出す
次のステップ：今すぐ手元のデータで試してみよう
理解を深めるために、以下の手順で実際に手を動かしてみてください。
- 自分のExcelファイルを開き、コード列の先頭何文字が意味を持つかを確認する
- 別シートに参照用マスタを作成する
- この記事の数式をコピーして、セル参照だけを自分のデータに合わせて変更する
最初は思うように動かなくても、#N/Aエラーが出たらこの記事のエラー対処セクションに戻ってきてください。原因のほとんどは「文字数のずれ」か「スペースの混入」です。
💡 サンプルデータ入りのExcelファイルは［ダウンロードリンク挿入箇所］からダウンロードできます。数式の動作をそのまま確認できます。
INDEX・MATCH・LEFTの組み合わせをマスターすると、VLOOKUPでは対応できなかった多くの実務課題が解決できるようになります。ぜひ自分のデータで試してみてください。
このシリーズでは、Lookup関連の関数を体系的に解説しています。他の記事もあわせてご覧ください。
