INDEX + MATCH + 結合キー列:Excel 2016以降対応の「旧来型・多条件検索」

💡 やりたいこと

「店舗」と「商品名」など、複数の条件の組み合わせで検索したいが、XLOOKUPが使えない旧バージョンのExcel環境で確実に実装したい。

Excel 2016以降対応の王道テクニック

VLOOKUPや通常のMATCH関数は「1つの列」しか検索できません。そこで長年使われてきたのが、検索用の「結合キー列(合体させた列)」を新しく作ってしまい、それを検索対象にするという物理的なアプローチです。

データテーブル
A (新規追加) B C D
1 結合キー 店舗 商品 売上
2 東京PC 東京 PC 150,000
3 大阪PC 大阪 PC 145,000
マスタのA列に =B2&C2 という数式を入れて、無理やり「1つの検索キー」にしてしまう方法。
そして、検索側でも =INDEX(D:D, MATCH("東京"&"PC", A:A, 0)) のように検索値を結合して当てに行きます。
⚠️ 「元データを汚す」という最大のデメリット
この方法は全バージョンのExcelで確実に動く反面、「関数を使うために元のマスタ表の構造を変えなければならない」「他システムからCSVでデータを落としてくるたびに列を追加する手間(マクロ不可環境での手作業)が発生する」という運用上の大きな弱点があります。

そのため、Excel365や2021以降が使える環境であれば、表を一切加工せずに済む XLOOKUP(A2&B2, D:D&E:E, ...) などの配列検索への移行が強く推奨されます。

実務での注意点

結合するときは区切り文字を入れる

そのまま結合すると、「1」と「23」も「12」と「3」も同じ「123」になり、別のデータを拾うおそれがあります。データに出てこない記号をはさむと防げます。

=B2&"|"&C2
=INDEX($D:$D, MATCH(F2&"|"&G2, $A:$A, 0))

F2に店舗、G2に商品を入力する例です。検索する値も、同じ形で結合します。

作業列を追加できない場合

表の形を変えられないときは、条件を掛け算する書き方があります。Excel 2016でも、配列数式(Ctrl+Shift+Enter)にせず使えます。

=INDEX(D2:D100, MATCH(1, INDEX((B2:B100=F2)*(C2:C100=G2),0), 0))

両方の条件に合う行だけが 1 になり、その最初の位置をMATCHで探します。範囲が広いと重くなるので、必要な行に絞って指定します。

🔗 関連記事