VLOOKUP関数で左側が検索できないときは、INDEX MATCH関数を使う

B!

今だに人気のあるVLOOKUP関数ですが、VLOOKUP関数には弱点が多すぎます。
現在は、Excelのバージョンによっては、XLOOKUP関数がありますが、VLOOKUP関数の弱点を補う方法を紹介します。

目次

VLOOKUP関数の限界

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

私の職場では、VLOOK関数を利用しているシートを見かけます。

どの職場でも、VLOOKUP関数最強伝説があります。

しかしVLOOK関数には欠点が多すぎます。

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

このブログはこんな人にお勧め

 

今回のテーマ
  • VLOOKUP関数で、左側が検索できなくて困っている人
  • VLOOKUP関数を利用することで、月末にExcelが重くなっている人
  • VLOOKUP関数を利用して、検索列がずれてイライラしている人

このブロブを読み終わるころには・・・・・

前後左右縦横無尽の検索ができます。理論的には、難しいかもしれませんが、やっていることは、非常にカンタンです。関数は、音読しながら、手を動かすことで理解が早まります。

実務で実際に起こりうる現象

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


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


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

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


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

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

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

スポンサーリンク

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

 INDEX関数を選択する

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


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

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

初心者でも、すぐに、集計、プレゼンができるようになる。書籍だけの勉強は今すぐやめよう!
電子書籍で、AMAZON1位
専門のスタッフが、電話、チャットで完全サポート
大手パソコンスクールよりも、圧倒的な低価格

 表示したい列を選択する

この状態で、表示したい列を選択します。この場合は、原本シートの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は楽しくなります。

ここがポイント

VLOOKUP関数は、IF関数と組み合わせて利用することがあります。しかし、本当にやめてほしい。時間がかかりすぎです。途中でExcelが固まることも良くあります。このIndex関数と、Match関数が、すべての問題を解決してくれます。VBAでもVLOOKUP関数よりカンタンに利用できます。まずは、関数から理解していただければ、Excelは楽しくなります。
動画とブログでわかりやすくExcelとACCESSを紹介しています

まとめ

INDEX関数とMATCH関数の組み合わせは、VLOOKUP関数の弱点をすべて補える。

今回のサンプルファイルは、No2です。

毎日の業務が、3時間短縮できます

わからない事を延々と考えるのは、無駄です。

  • なんで作動かないの?
  • もうやだ!VBAなんか嫌い!
  • ネットで調べても情報がない!

必ず作動するコードが、ここにあります。

最新の記事はこちらから