Excel

【Excelデータ取得の最強奥義】INDEX関数とINDIRECT関数さらにMATCH関数を組み合わせる探索術

本記事ではエクセルの検索を使って欲しい情報を得る方法を解説します。

検索といえばVLOOKUP関数、HLOOKUP関数が思いつくかもしれませんが(私もよく使います)、VLOOKUPもHLOOKUPも左端・上端の行列からしか検索できないという欠点があります。

一方でこれから紹介するINDEX関数とINDIRECT関数さらにMATCH関数を組み合わせると好きなセル、さらには好きなシートから検索し、値を取得することができます。

今回は給与明細を月毎にシートに保存していて、まとめのシートで各月の「支給額」などの情報を引っ張ってくる例題を説明していきます。

まず、「test2001」というシートに下記情報があったとします。

(「test2001」は20年01月を想定しています。testのところは給与とかボーナスとか2種類以上ある場合に使えるので何かしらの文字を入れる想定です)

↓シート名:test2001

次に実際にデータを入れておきたいシートです。(シート名は何でも構いません)

セルC1に「支給金合計」セルC2には

=INDEX(INDIRECT(“‘”&”test”&$A2&$B2&”‘!$B$1:B40”),MATCH(C$1,INDIRECT(“‘”&”test”&$A2&$B2&”‘!$A$1:A40”),0),1)

と入力します。

すると、

C2に「test2001」のシートから支給額合計の右隣の値「¥500,000」が引っ張ってこれます。

 

セルD2には

=INDEX(INDIRECT(“‘”&”test”&$A2&$B2&”‘!$D$1:D40”),MATCH(D$1,INDIRECT(“‘”&”test”&$A2&$B2&”‘!$C$1:C40”),0),1)

と入力します。

すると、

D2に「test2001」のシートから差引支給金の右隣の値「¥300,000」が引っ張ってこれます。

このようにINDEX関数とINDIRECT関数さらにMATCH関数を組み合わせるとどんな状況でも値を引っ張ってくることができます。(関数が多く、関数内の見た目がややこしくなりますが、使っているうちに慣れてくるので大丈夫です)

または、VBAを使うことで今回の事例が簡単にできるので興味のある方は勉強してみてください。

【Excelで作業の自動化!マクロ・VBAを始める方へ】例文が豊富でオススメな本ランキング Excel マクロ・VBA に関する本で私がお勧めする3冊を紹介します。 初心者でもすぐに使え、上級者にも例題集として1...
ABOUT ME
Mickey@コーヒー好きエンジニア
【コーヒー×科学×AI×ものづくり】趣味は①家庭用ロースターで自家焙煎コーヒー作り②アプリ開発です。 Wordpressブログ、note、Instagramやっています♪ ブログでは自家焙煎の実例や実践的なプログラミングの例文紹介、noteではコーヒーの海外論文(主に焙煎理論)やAIに関する情報の発信をしています。