Excel関数 VLOOKUPとXLOOKUP

VLOOKUPとは?XLOOKUP関数とは?

社会人になって、20年近くたちますが、Excelで最も効果が高いなっと思う関数が、このVLOOKUP関数でした。今日は、このVLOOKUP関数を解説し、その後最近Excelに追加されたXLOOKUP関数について紹介したいと思います。

VLOOKUP関数の基本

まず、VLOOKUP関数の構文です。

=VLOOKUP(検索値, 範囲, 列番号, 検索の型)

VLOOKUP関数には4つの引数(ひきすう)があります。
解説します。

  1. 検索値]どのデータで
  2. 範囲] どこを検索して
  3. 列番号]何番目の列にある値を取り出すか
  4. [検索の型]完全一致(false)  or 部分一致(true)か

VLOOKUPの実践

では、使ってみましょう。

1.表を作成します。
2.別の場所から、表の中にある情報を抜き取ります。
表にあるキーワードを探し、そのキーワードから何列となりにあるか?
を考えながら、VLOOKUP関数を作ります。

では、よくある表を作成します。

ここでは、サンプルとして、社員表にします。

次に、この情報を使いたいケースですが、申込書をサンプルにします。
この社員表と申込書は別々のシートに作っています。同じシートに作っても問題ないですが、
実務では別々に作る場合が多いと思います。

上記申込書ですが、社員番号を入力すると、自動的に部署と氏名が出るようにするためにVLOOKUP関数を使います。

社員番号の隣のセルには、直接手で番号を打ち込みます。

部署(D13)と氏名(D15)にVLOOKUP関数を入力します。

D13に入力する内容
=VLOOKUP(D11,Sheet2!A4:D14,2,FALSE)

D15に入力する内容

=VLOOKUP(D11,Sheet2!A4:D14,4,FALSE)

上記を入力した後のExcel

ちなみに、#N/Aという表示は、ノーアサインという意味で、参照先に値がないという意味です。
#N/Aを表示させないようにする方法はいくつかありますが、下記を入力することでも対応できます。

=IF(D11=””,””,VLOOKUP(D11,Sheet2!A4:D14,2,FALSE))

では、話しをもどして、社員番号を入力します。

VLOOKUP関数に問題がなければ、ただしく表示されます。

いかがでしょうか?VLOOKUPについてご理解いただけましたでしょうか。
VLOOKUPについては、知っているという方はたくさんいらっしゃると思います。

XLOOKUPとは?

XLOOKUP関数は2019年8月にリリースされた新しい関数です。
まだ知らない方もいらっしゃるのではないでしょうか?

VLOOKUP関数とXLOOKUP関数の違いは、横方向の検索ではなく、縦方向の検索を行うことです。
はじめてXLOOKUP関数を知った時、これは使える!と思いました。
VLOOKUPは確かに優秀です。しかし、Excelの表はよく項目が追加されます。
例えば、先程の社員表に入社年月日という項目が追加された場合、列が追加されます。
列が追加されると、VLOOKUP関数が正しく機能しません。引っ張ってきたいデータ列がずれるためです。
XLOOKUPはこのVLOOKUPの弱いところをカバーできます。

XLOOKUP実践

では、表に入社年月日を追加します。

申し込みシートに社員番号1001を入力します。

先程、ただしく氏名が表示されていましたが、列を追加したことで、入社年月日の値を取得しています。
3118という値は、セルの形式が日付型になっていないため、正しく表示されていません。

では、XLOOKUPを使います。

=XLOOKUP(検索する値、検索する配列、返り値の配列)

部署(D13)と氏名(D15)にXLOOKUP関数を入力します。

D13に入力する内容
=XLOOKUP(D11,Sheet2!A4:A14,B4:B14)

D15に入力する内容

=XLOOKUP(D11,Sheet2!A4:A14,E4:E14)

これで列が追加されたとしても、列が追加されたタイミングで同時にXLOOKUPの関数も更新されるのでずれが発生しません。

XLOOKUPですが、現在(2020/01/03)のところ、通常機能としては、導入されていないようです。
office Insiderに参加し、最新機能をインストールすることで使えました。
更新後、自分が作成したExcelでは機能するけど、他の人のPCでは機能しないということが予想されますので、ご使用の際にはご注意ください。

下記office Insiderへ参加し、Excelを更新します。

まとめ

今回、XLOOKUPを知ったことで、古くからあるExcelも着々と進化していることがわかりました。

他にも FILTER、UNIQUE、SORT、SORTBY、SEQUENCE、SINGLE、RANDARRAYなどが新しく追加されているようです。追って紹介できればと思います。

この記事が参考になった方は、twitterのフォローをお願いいたします。

upandup

Web制作の記事を中心に、暮らし、ビジネスに役立つ情報を発信します。 アフィリエイトにも参加しています。よろしくお願いいたします。