VLOOKUP関数で左側が検索できないときは、INDEX MATCH関数を使う
今だに人気のあるVLOOKUP関数ですが、VLOOKUP関数には弱点が多すぎます。
現在は、Excelのバージョンによっては、XLOOKUP関数がありますが、VLOOKUP関数の弱点を補う方法を紹介します。
目次
VLOOKUP関数の限界
こんにちは。伊川(@naonaoke)です。
私の職場では、VLOOK関数を利用しているシートを見かけます。
どの職場でも、VLOOKUP関数最強伝説があります。
しかしVLOOK関数には欠点が多すぎます。
上記の3つを一度に解決する方法を紹介します。
このブログはこんな人にお勧め
- VLOOKUP関数で、左側が検索できなくて困っている人
- VLOOKUP関数を利用することで、月末にExcelが重くなっている人
- VLOOKUP関数を利用して、検索列がずれてイライラしている人
このブロブを読み終わるころには・・・・・
実務で実際に起こりうる現象
下図が、参照というシートになります。
セル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】を選択します。
そして、カッコの数を合わせます。
となります。
列がずれもエラーは自動修正される
下図を見てください。列が増えても【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関数にも対応しています。
ご視聴者様からの質問
Index,Match関数でHLOOKUP関数を再現 作成手順その1 データベースを確認
前回紹介した内容は、縦列になりますが、今回は横列になります。
やり方は、縦が横になっただけです。
Index,Match関数でHLOOKUP関数を再現 作成手順その2 作成手順
表示したいのはどれ？
それは、馬の名前です。
一致するキー（値）を選択
順番があります。
表示させたい方のキーを最初に選択します。
完全一致させるので０を選択
これで完成です。
数式、VBAのコードを音読できるようになれると、Excelは楽しくなります。
VLOOKUP関数は、IF関数と組み合わせて利用することがあります。しかし、本当にやめてほしい。時間がかかりすぎです。途中でExcelが固まることも良くあります。このIndex関数と、Match関数が、すべての問題を解決してくれます。VBAでもVLOOKUP関数よりカンタンに利用できます。まずは、関数から理解していただければ、Excelは楽しくなります。
まとめ
今回のサンプルファイルは、No2です。
わからない事を延々と考えるのは、無駄です。
- なんで作動かないの？
- もうやだ！VBAなんか嫌い！
- ネットで調べても情報がない！
必ず作動するコードが、ここにあります。
