VLOOKUP関数の限界

こんにちは。伊川です。今回は、VLOOKUP関数ではできない、左側を検索する方法を紹介します。

VLOOKUP関数の弱点
・右側しか検索できない
・列がずれたら終わり(参照できない)
・範囲で検索するため、Excelが重くなる

上記の3つを一度に解決する方法を紹介します。

今、下図のようなExcelシートがあるとします。このシートを原本とします。


下図が、参照というシートになります。


セルA2に、カデナという馬に対応した騎手である、福永祐一という騎手を表示したいとします。

これは、みんなが大好きな、VLOOKUP関数ではできません。


INDEX関数と、MATCH関数を組み合わせると縦横無尽に検索できるようになります。

やり方は、難しいと思われガチですが、非常に簡単です。

難しい知識を得るよりも、数式は、読めるようになりましょう。

スポンサーリンク

VLOOKUP関数を使用しないで、左側を検索する手順

 INDEX関数を選択する

参照のシート、セルB2に、【=in】と入力します。すると、下図のようになります。


Excelが勝手に候補となる関数を表示してくれますので、【INDEX】を選択します。

そうすると、下図のようになります。下図のブルーの枠に記載されているものは、無視してください。


 表示したい列を選択する

この状態で、表示したい列を選択します。この場合は、原本シートのA列(騎手)になります。

このように、A列をダーァ(A列全体)と選択します。そうすると、数式バーに、

【=INDEX(原本!A:A】と表示されたと思います。


 MATCH関数を選択する

原本シートのA列選択後、下図のように【=INDEX(原本!A:A,m】と入力します。


そうすると、Excelが候補関数を表示しますので、【MATCH】をクリックします

そうすると、下図のようになったと思います。


一致するキーの列を選択する。

カデナという馬名は、原本シートのB列、参照シートのA列にあります。

それでは、下図のように、

参照シートの、A列をダーァと選択します。選択後、必ず、【,】の入力を忘れないでください。


下図のように、原本シートのB列をダーァと選択します。


そして、B列選択後、【,】を打ちます。

そうすると、下図の赤枠のように、数字の選択をExcelが要求してきます。


今回は、完全一致です。【0】を選択します。

そして、カッコの数を合わせます。

完成した数式
=INDEX(原本!A:A,MATCH(参照!A:A,原本!B:B,0))

となります。



列がずれもエラーは自動修正される

下図を見てください。列が増えても【N/A】というエラーは回避されています。

数式バーでは、数式が自動で変更されていまます。


【N/A】のエラーを避けるには。

【N/A】エラーの回避方法はとても簡単です。

=INDEX(原本!A:A,MATCH(参照!A:A,原本!B:B,0))

=IFERROR(INDEX(原本!A:A,MATCH(参照!A:A,原本!D:D,0)),””)

この赤字の部分を付け加えるだけで、【N/A】は回避されますので、

参照したものを、そのまま会議資料にも使えます。

Index関数とMatch関数はHLLOKUPにも対応するのか?

こんにちは。伊川(@naonaoke)です。

今回は、チョイチョイ来る質問に回答します。

私は、HLOOKUP関数は使ったことがありません。

しかし、HLOOKUP関数にも対応しています。

ご視聴者様からの質問

HLOOKUPの代わりにもなりますか?

 

Index,Match関数でHLOOKUP関数を再現 作成手順その1 データベースを確認


前回紹介した内容は、縦列になりますが、今回は横列になります。

やり方は、縦が横になっただけです。

Index,Match関数でHLOOKUP関数を再現 作成手順その2 作成手順

表示したいのはどれ?

それは、馬の名前です。

一致するキー(値)を選択

順番があります。

表示させたい方のキーを最初に選択します。


完全一致させるので0を選択

これで完成です。

数式、VBAのコードを音読できるようになれると、Excelは楽しくなります。


スポンサーリンク

まとめ

INDEX関数とMATCH関数の組み合わせは、VLOOKUP関数の弱点をすべて補える。
サンプルファイルNo2はです
スポンサーリンク

Twitterでフォローしよう

おすすめの記事