Excel関数は組み合わせて使う 検索列の左側を抽出できないVLOOKUP関数の弱点を、INDEX関数、MATCH関数で克服します

Excel関数の中でも、利用価値の高いVLOOKUP関数ですが、弱点もあります。INDEX関数とMATCH関数を組み合わせて使うと、その弱点をクリアすることができるのです。

スポンサーリンク

VLOOKUP関数の弱点

VLOOKUP関数は、
 
①指定したデータを調べて
 
②一致したら、右側に移動して、セルを表示する 
(いくつ右に移動するかは事前に指定する)
 
と言う関数です。

 
図で示すと、

 

スクリーンショット 2015 02 01 12 58 04
(VLOOKUP関数の動きのイメージ)

 
表の上を、赤の矢印に沿って動くイメージです。
(関数はこの「動きのイメージ」が重要)

 
VLOOKUP関数は、表の中から必要なデータを抽出するのに使う、非常に便利な関数ですが、先述の通り弱点があります。

 
それは、
 
「調査するデータが、表の一番左の列にないと機能しないこと」
 
です。

 
VLOOKUP関数では、

 
「一致したセルから、右にいくつ進んだセルを表示せよ」

 
と指示しなければいけません。

 
その結果、表示したいデータが、一致したセルの左側にある場合は、表示させることができないのです。

 

スクリーンショット 2015 02 01 13 16 03

 
もちろん、表を加工すれば、VLOOKUP関数で対応できますが、表に手を加えずに、データを抽出したい場合もあるはず。

 
そのようなときに、VLOOKUP関数の弱点を克服してくれる関数があります。

 
INDEX関数&MATCH関数の組み合わせです。

 

 

INDEX関数とMATCH関数

それぞれの関数について説明します。

 

INDEX関数

INDEX関数は、範囲を指定して、行番号と列番号を指定すると、
指定したセルを表示してくれる関数です。

 

スクリーンショット 2015 02 01 13 28 43

 
=INDEX($L$18:$O$25,5,3)
と入力されています。

 
この関数の要素は、
 
①範囲:$L$18:$O$25
 
②行番号:5
 
③列番号:3

 
の3つです。

 
ポイントは、基準が指定範囲の”左上角のセル”であること。
(上の例では「L18セル」が該当)

 
このセルを基準に「5行下がる(=23行)」「3列分右(N列)」と指示すれば、「N23セル(=吉田類)」が表示されることになります。

 
関数の仕組みはシンプルです。
上の図の「矢印の動き」でイメージできるようになると、関数の理解がさらに進みます。

 

MATCH関数

MATCH関数は、「探したいデータ」と「範囲」を指定すると、
指定した範囲の中で、「何番目のセルにあるか」を表示してくれる関数です。

 

スクリーンショット 2015 02 01 13 40 53

 
=MATCH(“吉田類”,$L$30:$O$30,0)
と入力しています。

 
MATCH関数の要素は、

 
①調査するデータ:吉田類
 
②調査する範囲:$L$30:$O$30
 
③検索の型:0(ピッタリ一致するものだけを表示)

 
の3つです。

 
上の例は、

 
「吉田類」は、「$L$30:$O$30」の範囲で、
 
何番目にあるか、を表示せよ、

 
という意味です。

 
ポイントは、「行」だけでなく、「列」にも使えること。

 

スクリーンショット 2015 02 01 13 55 14

 
「列」の場合は、一番上のセルを基準にして、何番目のセルにあるかを表示します。

 

 

INDEX関数&MATCH関数で、検索列の左にあるデータを表示

VLOOKUP関数の弱点だった、「調査するデータが、一番左の列にないと機能しないこと」を克服します。

 
サンプルの表を見て下さい。

 

スクリーンショット 2015 02 01 14 03 04

 
この表から、「名前」を調査するデータにして、その左側の列にある「職業」を表示させてみましょう。

 
大まかな方針は、INDEX関数を使って、
 
①範囲:表全体

 
②行:調査する「名前」のデータがある行番号

 
③列:「職業」が入力されている列番号

 
を入力することで、指定した「名前」(「倉本康子」さん)の、「職業」を表示させることを目指します。

 

MATCH関数で「行」、「列」を表示

まずは、調査する「名前」のある行を表示させます。

 
今回は「倉本康子」さんを探します。
調査するデータとして、”倉本康子”を入力。

 
名前が入力されている範囲は、「O5セル」から「O10セル」ですから、範囲を「$O$5:$O$10」に指定します。

 

=MATCH(“倉本康子”,$O$5:$O$10,0)

 

すると、

 

スクリーンショット 2015 02 01 14 15 23

 
「4」と表示されました。

 
次に、「職業」を表示させたいので、表の何列目に入力されているかを表示させます。ここも、MATCH関数です。

 
調査するデータは「職業」ですが、表の項目では「Career」になっていますから、 
調査するデータに”Career”を入力。

 
項目が並んでいるのは、表の一番上の行ですから、ここを範囲指定します。「$L$5:$P$5」ですね。

 

=MATCH(“Career”,$L$5:$P$5,0)

 

すると、

 

スクリーンショット 2015 02 01 17 00 50

 
「2」と表示されました。

 
これで、

 
・該当するデータ(今回は「名前」)の行
 
・表示させたいデータ(今回は「Career」)の列

 
をMATCH関数で表示することができました。

 
これを、INDEX関数に組み込めば完成です。

 
=INDEX($L$6:$P$11,MATCH($L$4,$O$6:$O$11,0),MATCH(M$6,$L$6:$P$6,0))

 
最初に、表全体を範囲指定して、MATCH関数で行・列を指定しています。

 
その結果が、こちら。
 

スクリーンショット 2015 02 01 17 12 13

 
正しく、「ファッションモデル」と表示されています。

 
難しく見えますが、大事なのはイメージです。

 
INDEX関数としては、③で示された、「行+列」の矢印の動きが該当します。

 
まずは、ココをおさえる。

 
その上で、INDEX関数に出てくる、「行」、「列」を調査するために、
 
①、②の矢印で表示された、MATCH関数の動き
がある。

 
と、分けてイメージするようにしましょう。

 

 

まとめ

VLOOKUP関数の弱点である、「検索列の左側にあるデータ抽出」も、INDEX関数&MATCH関数の組み合わせで、克服できます。
2つの関数の動きを、イメージできるようになることが、スムーズに使いこなすための鍵です。

 
<おまけ>
『おんな酒場放浪記』は出演者のキャスティングが絶妙です。
倉本康子さんが最大のヒットですが、それ以外にも、
棋士、ハーモニカ奏者、写真家、料理家、と「いったい、どこから探してきたんだ!」と言いたくなるような、渋いお酒好きがブックされています。

【セミナー情報】
実務で使えるExcel入門セミナー 基礎編

タイトルとURLをコピーしました