今回はエクセル関数の定番【VLOOKUP】関数を含むLOOKUP系の関数を紹介します。大量のデータベースの中から値を検索して、その値の中の必要なデータを抽出する関数になります。特に【VLOOKUP】関数は中級者ともなれば知ってて当たり前の関数なので日常的に使えるようになるまで使い込みましょう。尚、今回は【LOOKUP】関数についての説明は省き、Microsoft365とOffice2021で新たに導入された【XLOOKUP】関数についても紹介します。
エクセル関数-LOOKUP関連
目次
3つのLOOKUP関数の概要
始めにVLOOKUP・HLOOKUP・XLOOKUPの使い方について概要を表にまとめました。
| 関数名 | 検索用途 | 欠点 |
| VLOOKUP | 縦方向に並んでいるデータの中から 検索値を検索して 該当する行の指定した列番号 （範囲内の？列目）の値を抽出する |
検索値が必ず一番左端にある必要があり、 検索値より左にあるデータは 抽出することができない。 |
| HLOOKUP | 横方向に並んでいるデータの中から 検索値を検索して 該当する列の指定した行番号 （範囲内の？行目）の値を抽出する |
検索値が必ず一番上端にある必要があり、 検索値より上にあるデータは 抽出することができない。 |
| XLOOKUP | VLOOKUPとHLOOKUP両方の機能を持つ 最強の検索関数。 |
自由自在に抽出可能だが、 2021年10月現在では最新のOffice2021か Microsoft365でしか使用できない。 |
VLOOKUP関数の使い方
基本的な使い方
まずはVLOOKUP関数の使い方について実際の表をもとに解説します。下の表は500人が受けた5教科の学力テストの結果を集計した表です。
IDを入力すると右側に各教科の点数が抽出されるように関数を入力する
一番上の赤枠①に受験者IDを入力すると、下の結果表から赤枠①の値を検索して、一致すればその右側にある各教科の点数を抽出するようにVLOOKUP関数を赤枠②に入力していきます。まずは国語の点数が抽出できる関数を作りますので、セルC4を選択して下の数式を入力します。
VLOOKUP関数の作り方図解
国語の抽出セルに入力するVLOOKUP関数
ポイント
VLOOKUP関数の解説を上の図をもとにします。
①検索値・・・検索したい値を入力するセルを指定します。ここでは検索IDを入力するセルB4。
②範囲・・・①検索値が入力されている列を必ず一番左にして、抽出したいデータを全て含む範囲を指定します。ここではセルB7からセルI507。
③列番号・・・②範囲の中で左から何列目のデータを抽出するかを指定する。ここでは国語は②範囲の中で左から2列目なので、2を指定する。
④検索方法・・・完全一致（FALSE）か近似値（TRUE）のどちらかを選択する。ほとんどの場合は完全一致で検索するのでFALSEで問題ない。
①検索値に入力された値を、②範囲の一番左端にある列（受験者ID）内を上から下に検索していき、IDが完全一致した行の中の、③列番号で指定した列にあるデータを抽出する。
もし完全一致する値が存在しなければ、エラー値＃N/Aが返ってきます。下の図のように①で関数を入力して確定すると、②のようにエラー値が返ってきますが、③のように検索IDを入力すると正しい値が返ってきます。
検索IDが空白のままではエラーになるが、IDを入力すると正しいデータが抽出される
これで国語のセルに関数が入りましたので、あとはこの関数をコピーしてその他の教科に貼り付けをします。
※数式内の＄マークはコピーの際に重要となる絶対参照の設定です。分からない方はこちらの絶対参照・相対参照をご覧ください。
ただしこの状態でコピーすると、セルの参照は問題ありませんが、列番号の指定が2のままになり、全て国語の点数が返ってきてしまいます。
列番号だけは値で指定しているので、その場合はどうしても手修正が必要となります。社会は3、理科は4・・・と各セルの数式内の列番号だけは手修正をしてください。
これで完成です。検索IDを打ち換えるとそのIDの各教科と合計と平均の点数が瞬時に表示されます。試してみてください。
VLOOKUPで列番号を自動的に変える方法
関数合わせ技！
VLOOKUPで列番号を手修正しないようにする関数合わせ技
列番号には値だけではなく数式を入れることもでき、入れた数式の結果を列番号とすることが可能です。そこでMATCH関数を併用して使うことで手修正する必要がなくなります。MATCH関数の詳細はここでは省略しますが、検索値が指定範囲の中の何番目にあるかを返す関数です。国語のセルC4に入力した数式の列番号2をMATCHに変更して、その数式をコピーしてその他へ貼り付けすれば手修正する必要はなくなります。大量のセルへの貼り付けが必要な場合はこの方が間違いもなく手間もありません。
セルC4の元々の数式 =VLOOKUP($B$4,$B$7:$I$507,2,FALSE)
MATCHに変更した数式 =VLOOKUP($B$4,$B$7:$I$507,MATCH(C$3,$B$7:$I$7,0),FALSE)
VLOOKUPでエラー値を消す方法
検索IDが空白や②範囲の左端に存在しない場合はエラー値が返ってきます。VLOOKUPの場合、エラー値を消すにはIFやIFERRORなどの関数を組み合わせる必要があります。
関数合わせ技！
VLOOKUPでエラー値を消す関数合わせ技
検索値が空白や範囲内に存在しない場合、VLOOKUP関数はエラー値を返します。エラー値を消すには他の関数を組み合わせる必要があり、ここではIFERROR関数を使用した方法を紹介します。
セルC4のMATCHに変更した数式 =VLOOKUP($B$4,$B$7:$I$507,MATCH(C$3,$B$7:$I$7,0),FALSE)
IFERRORを追加した数式 =IFERROR(VLOOKUP($B$4,$B$7:$I$507,MATCH(C$3,$B$7:$I$7,0),FALSE),"")
IFERRORの詳細はここでは省きますが、赤字の部分は上の式と全く同じで、この赤字の返り値がエラーの場合は、青字の値を返しなさいという関数です。ここでは "" を返すようにしており、"" とは空白のことで、VLOOKUPの返り値がエラーの場合は空白になります。試してください。
HLOOKUP関数の使い方
基本的な使い方はVLOOKUP関数と同じです。違いはVLOOKUP関数は縦方向の検索でしたが、HLOOKUP関数は横方向の検索になります。先程の学力テストの表で行列を入れ替えたものを使って説明します。
行列の入れ替えをした学力テスト表
国語抽出セルのC4に入力する数式
ポイント
国語抽出セルのC4に入力するHLOOKUP関数の解説を上の図をもとにします。
①検索値・・・検索したい値を入力するセルを指定します。ここでは検索IDを入力するセルC3。
②範囲・・・①検索値が入力されている行を必ず一番上にして、抽出したいデータを全て含む範囲を指定します。ここではセルF3からセルSL10。
③行番号・・・②範囲の中で上から何行目のデータを抽出するかを指定する。ここでは国語は②範囲の中で上から2行目なので、2を指定する。
④検索方法・・・完全一致（FALSE）か近似値（TRUE）のどちらかを選択する。ほとんどの場合は完全一致で検索するのでFALSEで問題ない。
①検索値に入力された値を、②範囲の一番上端にある行（受験者ID）内を左から右に検索していき、IDが完全一致した列の中の、③行番号で指定した行にあるデータを抽出する。
もし完全一致する値が存在しなければ、エラー値＃N/Aが返ってきます。
VLOOKUP関数で説明した関数組み合わせ技も同じ考え方でできますので、VLOOKUP関数を参考にしてください。
XLOOKUP関数の使い方
Microsoft365とOffice2021で追加された新関数のXLOOKUP関数は、上の2つの機能を持ち、かつ弱点が改善され非常に使いやすくなった関数です。最新バージョンでしか使えないのが残念ですが、新関数の使い方を紹介します。
XLOOKUPの引数の指定箇所
国語抽出セルのC4に入力する数式
ポイント
国語抽出セルのC4に入力するXLOOKUP関数の解説を上の図をもとにします。
| ①検索値 | 検索したい値を入力するセルを指定します。ここでは検索IDを入力するセルB4。 |
| ②検索範囲 | ①検索値が入力されている列（行）を選択する。 ここではセルB7からセルB507。 ※HLOOKUPのように横方向に検索する場合は行を選択する |
| ③戻り範囲 | 抽出したいデータがある列（行）を選択する。ここではセルC7からセルC507。 ※HLOOKUPのように横方向に検索する場合は行を選択する |
| ④見つからない場合 | 検索値がない場合に返す値を指定できる。 （省略可能、省略した場合はエラー値が返る） |
| ⑤一致モード | 検索値を検索する条件を指定する。 （省略可能、省略した場合は0を指定したことになる） 0 - 完全一致。検索値がない場合はエラー値を返す。 -1 - 完全一致。検索値がない場合はその次に小さい値を返す。 1 - 完全一致。検索値がない場合はその次に大きい値を返す。 2 - 文字列の部分一致。*や?などのワイルドカードを使った文字列の検索を する場合は必須。 |
| ⑥検索モード | 検索値を検索する条件を指定する。 （省略可能、省略した場合は1を指定したことになる） 1 - 一番上端（左端）から順番に検索していく。 -1 - 一番下端（右端）から順番に検索していく。（逆順検索） 2 - 昇順に並び替えされた検索範囲を使用してバイナリ検索をする。 -2 - 降順に並び替えされた検索範囲を使用してバイナリ検索をする。 |
引数が6個に増えたため複雑になったように見えますが、必須は①②③の3つだけです。普通に使う分には⑤と⑥は省略しても問題ないでしょう。④も省略できますが、エラー値が返ってきてほしくない場合はここで指定できます。VLOOKUPではIFERRORを組み合わせてエラー値を空白にしましたが、XLOOKUPは④で指定すればエラー値を消すことが可能なので活用しましょう。VLOOKUPと大きく変わったのは③戻り範囲で、VLOOKUPでは列番号を指定していたが、セル範囲の指定に変更され、さらに検索値がある列より左にある列からもデータの抽出が可能になっています。今までもINDEX関数とMATCH関数を使えば可能でしたが、これも必要なくなり大変便利になっています。
まとめ
LOOKUP関連の3つを紹介しました。エクセル関数の定番とも言うべきVLOOKUPは様々なシーンで使用することがあります。基本的な使い方を覚えて、他の関数と組み合わせることによりいろんな検索ができるようになります。XLOOKUP関数ははっきり言って最強です。今まではINDEXやMATCHを使わないとできなかったことがこれ一つで可能になります。残念なのは最新バージョンのMicrosoft365とOffice2021でしか使用できない点です。最新バージョンを使っている方は、この最強の新関数をぜひお試しください。
おすすめ！！
本サイトでは常に最新機能を兼ね備えるMicrosoft365 PersonalのExcelを使用しております。旧バージョンでは使用できない便利な機能や最新関数が盛りだくさんなのでぜひ下記よりご購入ください。
