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以降が使える環境であれば、表を一切加工せずに済む
この方法は全バージョンの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で探します。範囲が広いと重くなるので、必要な行に絞って指定します。