今回のまとめ
・VLOOKUP関数は、検索して跳ね返す値のある列が必ず右側になければいけない、という制約がある。・レガシーエクセルでVLOOKUP関数の制約を克服する定番の方法は二つある。
①作業列を作る方法
②INDEX+MATCHの組み合わせを使う方法
【第3回】監査や経理の担当者が知っておくべき！『モダンExcel』（全10回）
【第3回】VLOOKUP関数の「最大の弱点」を克服する「レガシーエクセル」の最適解
・レガシーエクセルでVLOOKUP関数の制約を克服する定番の方法は二つある。
前回（第2回）、「VLOOKUP関数」をご紹介しました。ただし、「左側の列を検索できない」という最大の弱点があることも述べました。
今回、VLOOKUP関数の弱点を「レガシーエクセル」で克服する２つの方法をご紹介します。
■「作業列」を作り、VLOOKUP関数を動作させる
前回（第2回）解説のとおり、VLOOKUP関数で縦・垂直（Vertical）方向に検索することができますが、VLOOKUP関数は検索して跳ね返す値のある列が必ず右側になければいけない、言い換えれば「左側の列を検索できない」という制約があります。図1をご覧ください。
図1 A列～C列が「販売データ」。G列～H列が「得意先マスタ」。
「販売データ」には「得意先コード」（B列）はあるが、「得意先名」列がないので表示したい。
だが、「得意先マスタ」は「得意先コード」（H列）の左側に「得意先名」（G列）がある。これではVLOOKUP関数で対処できない。
事例の場合、「販売データ」（A列～C列）の「得意先コード」（B列）と同じ項目列を、「得意先マスタ」（G列、H列）の「得意先コード」（H列）で検索し、「得意先マスタ」の「得意先名」（G列）を跳ね返したいわけですが、「得意先マスタ」の列の並びを見ると検索される「得意先コード」（H列）の左側に跳ね返したい「得意先名」（G列）があるため、このままではVLOOKUP関数では対処できないことになります。
最も初歩的な対処法として、この「得意先コード」の右隣り（I列）にVLOOKUP関数を使うためだけの「作業列」を作成するという方法があります。
図2をご覧ください。「得意先コード」（H列）の右隣りに「得意先名」（I列）をコピーしたり、セル参照したりすれば、検索される列（H列の「得意先コード」）の右側に跳ね返したい列（I列の「得意先名」を示すためだけの「作業列」）があるので、VLOOKUP関数が使えるようになります。セルI4を見ると分かるように、「＝G4」としてここではセル参照をしています。
ただ、この方法には一つ大きな問題があります。「作業列」が一列追加されるため、データ量が多い場合、動作が遅くなるなどの問題が生じてしまいます。
図2 「作業列」（I列）を作りVLOOKUP関数が動作するようにした
■VLOOKUP関数のように使う、「INDEX＋MATCH」の組み合わせ
「作業列」を作ってVLOOKUP関数を動作させる方法は、非常に簡単で便利です。ただし、VLOOKUP関数を動作させるため「だけ」に「作業列」をわざわざ一列作るというのもいかがなものでしょうか。
そこで、エクセルに詳しい人は「INDEX関数＋MATCH関数」の合わせ技でVLOOKUP関数のような効果を得ようとします。この方法であれば、検索される値の列（事例では「得意先マスタ」の「得意先コード」（H列））と跳ね返したい値の列（同「得意先名」（G列））がどんな位置関係にあっても検索できるようになります。
ここで、それぞれの関数を説明します。
（1）INDEX関数
INDEX関数は、テーブルまたはセル範囲にある値、あるいはその値のセル参照を返します。
つまり、INDEX関数を使えば、指定した行と列が交差する位置にあるデータを抽出できるわけです。その書式は、INDEX(配列，行番号，列番号）です。詳しくは、Microsoft公式一次情報も参考にしてください。
INDEX 関数（microsoft.com）
（2）MATCH 関数
MATCH 関数は、範囲のセルの範囲で指定した項目を検索し、その範囲内の項目の相対的な位置を返します。
つまり、指定した検査値が検査範囲の中で何番目のセルにあるかを求める、これがMATCH関数です。詳しくは、Microsoft公式一次情報も参考にしてください。
MATCH 関数（microsoft.com）
（3）INDEX関数＋MATCH関数
これらINDEX関数とMATCH関数を組み合わせて使うことで、VLOOKUP関数と同様の効果を得られます。
事例の場合、VLOOKUP関数では不可能な検索される左側の値を跳ね返す、つまり「得意先マスタ」の検索される「得意先コード」（H列）の左側に存在する「得意先名」（G列）の値を「販売データ」（D列）に表示することが「INDEX+MATCH」で可能となります。
図3 INDEX＋MATCHによる検索の結果（D列）。
INDEX＋MATCHの使い方については、Microsoft公式一次情報も参考にしてください。
VLOOKUP、INDEX、または MATCH を使って値を検索する（microsoft.com）
確かに、INDEX関数とMATCH関数を組み合わせれば、「作業列」を作らずに、VLOOKUP関数と同じような効果を得ることができます。これが「レガシーエクセル」による「値検索」の最適解の一つなのですが、数式が複雑で、一見して理解が及ばず、エクセル初学者にはハードルが高いようにも思います。
次回（第4回）は、前回（第2回）と今回（第3回）でご紹介した「レガシーエクセル」による値検索の方法よりもスマートな「モダンExcel」による対処法をご紹介します。
＊「モダンExcel」の詳細は、拙著『モダンExcel入門』（日経BP）などに委ねます。
モダンExcel入門（日経BP）
https://amzn.to/3wnlzBD
『モダンExcel研究所』
https://excel.value.or.jp/
- 執筆者プロフィール
-
村井 直志（むらい ただし）
公認会計士 / データアナリスト日本公認会計士協会東京会コンピュータ委員会委員長、経営・税務・業務各委員会委員、第三者委員会委員などを歴任。研究テーマ『CAAT（コンピュータ利用監査技法）で不正会計に対処する、エクセルを用いた異常点監査技法』で日本公認会計士協会研究大会に選抜。
- 著書
-
『3000社の決算を分析してきた会計士が教える 経営を強くする 会計7つのルール』（ダイヤモンド社）
『Excelによる不正発見法 CAATで粉飾・横領はこう見抜く』（中央経済社）
『会計ドレッシング10episodes』（東洋経済新報社）
『モダンExcel入門 データ分析＆可視化の新しい教科書』（日経BP）など
