広告
- VLOOKUPのエラーを非表示にしたい
- VLOOKUPで複数範囲を検索したい
これらは VLOOKUP に IFERROR を組み合わせればOKです。
この記事では次のことを解説します。
※お急ぎの方は、ボタンから該当箇所へ移動してください。
エラーが想定内なら表示させない方が、見た目もスッキリし、作業に集中できるようになります。
複数の範囲を検索できれば、例えば社員名簿の検索で、部署ごとに分かれていても1つの数式ですべて検索できて便利です。
作業の効率が上がり、きっと時短につながることでしょう。
本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルもダウンロード可能です。
≫ PDFファイル参照ページはこちら
VLOOKUP を基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ VLOOKUPの使い方｜基礎から完璧マスター＆便利技20選を超図解！
1. VLOOKUPのエラーをIFERRORで非表示にする方法
この章では、VLOOKUPのエラーをIFERRORで非表示にする方法を解説します。
エラーが想定内であったとしても、表示されたままだと気になってしまいます。表示させないようにできれば、見た目もスッキリし、作業に集中できることでしょう。
1-1. エラーを非表示にする数式
VLOOKUPのエラーをIFERRORで非表示にする数式は次のとおりです。
=IFERROR(VLOOKUP(D2,A:B,2,FALSE),””)
| 項目 | 内容 |
|---|---|
| D2 | 出席番号を入力するセル |
| A:B | テスト結果一覧表の範囲 |
| 2 | 2列目のデータを返せ |
| FALSE | 一致する場合のみ返せ |
| “” | エラーの場合は「””」（空白）にせよ |
VLOOKUPとIFERRORの基礎知識を確認したい方はこちら
≫ 3. VLOOKUPとIFERRORの基礎知識
1-2. エラーを非表示にする仕組み
VLOOKUPにIFERRORを組み合わせることで、エラーを表示させないようにできます。なぜならIFERRORは、エラーの場合に指定した値を返すようにできるからです。
例えば上図では、テスト結果から集計表へ、出席番号を頼りに点数を転記しています。出席番号が見つからない場合は「””」を返すので、セルには何も表示されません。
IFERRORと組み合わせることで、エラーが表示されずに見た目がスッキリします。
具体的な処理の流れは次のとおりです。
(1) VLOOKUPがエラーかどうか判定｜正常ならそのまま実行
IFERRORの中にある❶のVLOOKUPが、出席番号を頼りにテスト結果を検索します。
探している出席番号が見つかれば、点数を集計表へと転記します。
(2) エラーの場合のみ指定された値を返す
出席番号が見つからなければエラーとなります。
エラーの場合はIFERRORの2つ目の項目❷「””」が返されます。
「””」は文字数ゼロの文字列のため、セルには何も表示されません。
エラーの場合に表示させる値は、自由に設定することもできます。「”入力中”」や「”要問合せ”」などにすれば、エラーが出たときだけのメッセージが伝えられて便利です。
セルの番地を指定すれば、そのセルの値を表示することもできます。数式を組み込めば、追加処理ができるので、応用の幅が広いです。
VLOOKUPの単独処理の場合
VLOOKUPの単独処理の場合、検索値が見つからないと#N/Aエラーになります。#N/AがSUM（合計）やAVERAGE（平均）などの演算もエラーになるので、注意しましょう。
#N/Aエラーについて詳しく知りたい方はこちら
≫ 【VLOOKUP】#N/Aエラーの最短解決方法＆非表示や文字列への置き換え
エラーは必ずしも非表示にして良いとは限りません。数式の不備やデータの更新ミスなど、対応が必要なエラーもあります。すべて非表示にしてよいかどうかを検討した上で導入しましょう。
1-3. エラーを非表示にする数式の作り方
VLOOKUPにIFERRORを組み合わせた数式の具体的な作り方を解説します。
=IFERROR(VLOOKUP(D2,A:B,2,FALSE),””)
テスト結果から集計表へ自動転記する数式です。存在しない出席番号を入力しても、エラーにならず空欄になります。
数式の作り方は次のとおりです。
(1) 先にVLOOKUPの数式を完成させておく
VLOOKUPだけの数式を先に作り、正常な値が転記されることを確認します。
まとめて作成すると、うまくいかないときにどこに問題があるのかわかりにくくなります。
VLOOKUPの数式の作り方については、下記を参照ください。
≫ エクセルの商品マスタ作成を自動転記で入力効率化＆検索100万件でも楽々｜2-2.基本的な数式の作り方
(2) VLOOKUPの数式部分だけコピーする
VLOOKUPの数式部分だけコピーします。「=」はコピーしないでください。「=」が混じっていると数式が正しく動きません。
(3) 数式を削除してから関数ダイアログを開く
数式を削除してから、「fx」をクリックします。
数式を削除せずに開いてしまうと
数式を削除せずに「fx」をクリックすると、セルに残っているVLOOKUPの設定画面が開いてしまいます。
(4) IFERROR関数を検索して呼び出す
「関数の挿入」ダイアログが開くので、「関数の検索」の欄に「IFERROR」と入力し、検索して呼び出します。
(5) 数式を貼り付ける
❽「値」の欄に先ほどコピーしたVLOOKUPの数式を貼り付けます。このとき「=」が無いことを確認しましょう。
❾「エラーの場合の値」の欄には、「””」を入力します。
完成したら下のセルにコピーしてください。
値の欄に数式を貼り付ける際、「=」を含めてしまうと、貼り付けた数式がただの文字列として扱われます。勝手に「””」で挟まれ、計算結果もおかしくなります。
2. VLOOKUPにIFERRORを組み合わせて複数の範囲を検索する方法
この章では、VLOOKUPにIFERRORを組み合わせて、複数の範囲を検索する方法を解説します。
例えば社員名簿を検索する数式をVLOOKUP単独で作る場合、部署ごとに名簿が分かれているとそれぞれ別々に作る必要がありました。それがIFERRORと組み合わせると、1つの数式ですべて検索できるようになります。
2-1. 複数の範囲を検索する数式
VLOOKUPにIFERRORを組み合わせて、複数の範囲を検索する数式は次のとおりです。
=IFERROR(VLOOKUP(G2,A:B,2,FALSE),IFERROR(VLOOKUP(G2,D:E,2,FALSE),”該当なし“))
| 項目 | 内容 |
|---|---|
| G2 | 抽出リスト内の社員番号を入力するセル |
| A:B | 1つ目の社員名簿の範囲 |
| D:E | 2つ目の社員名簿の範囲 |
| 2 | 2列目のデータを返せ |
| FALSE | 一致する場合のみ返せ（※1） |
| “該当なし” | 社員番号が見つからなかった場合に表示(※2) |
(※1) エラーになることを利用して検索範囲を切り替えるため、必ず「FALSE」を選びましょう。「TRUE」にすると近似値検索になり、一致する社員番号が無くてもエラーにならず、近い値を返してしまうからです。
(※2) 他の文字へ置き換え可能です。数式でも構いません。
VLOOKUPとIFERRORの基礎知識を確認したい方はこちら
≫ 3. VLOOKUPとIFERRORの基礎知識
2-2. 複数の範囲を検索する仕組み
VLOOKUPにIFERRORを組み合わせることで、複数の範囲を検索できるようになります。検索値が見つからなければエラーになることを利用して、次のVLOOKUPを実行できるからです。
例えば上図は、社員名簿から抽出リストへ、社員番号を頼りに名前を転記しています。1つ目の社員名簿で見つからない場合はエラーとなり、IFERRORによって2つ目の社員名簿を検索します。
IFERRORと組み合わせることで、複数の範囲を検索できるようになるわけです。
具体的な処理の流れは次のとおりです。
(1) 1つ目のVLOOKUPで1つ目の社員名簿を検索
前半にある❶のVLOOKUPで「A:B」の検索範囲から検索値を探します。
見つかれば検索結果を返して完了です。
(2) 見つからなければ次のIFERRORへ移行
見つからなければエラーとなるため、IFERRORの後半の❷を実行します。
(3) 2つ目のVLOOKUPで2つ目の社員名簿を検索
❸のVLOOKUPで「D:E」の検索範囲から検索値を探します。
見つかれば検索結果を返して完了です。
(4) 見つからなければ「該当なし」を表示
見つからなければエラーとなるため、IFERRORの後半の❹「”該当なし”」を返します。
「””」や「”要確認”」などの他の値でも構いません。
(5) 同じ形式で繰り返し可能
”該当なし”のところに新たにVLOOKUPを組み込めば、繰り返し検索範囲を増やすことができます。
検索範囲が複数に渡っても、検索値は重複してはいけません。重複していた場合、最初に見つけた検索値の結果を返して検索完了となります。
2-3. 複数の範囲を検索する数式の作り方
VLOOKUPにIFERRORを組み合わせた数式の具体的な作り方を解説します。
=IFERROR(VLOOKUP(G2,A:B,2,FALSE),IFERROR(VLOOKUP(G2,D:E,2,FALSE),”該当なし“))
複数の社員名語から抽出リストへ名前を転記する数式です。1つ目の社員名簿で見つからなければ、2つ目の社員名簿を検索します。
数式の作り方は次のとおりです。
(1) 先に2つのVLOOKUPの数式を完成させておく
それぞれの社員名簿を検索するVLOOKUPの数式を先に作ります。
任意の空いているセルに作成しますが、結果を表示させたいセルと同じ数式にしてください。今回の例でいうと「H2」セルです。
数式ができたら、正常に動作することを確認します。この時点で動作を確認しておかないと、組み合わせてから問題が発生した場合に原因追及が難しくなります。
VLOOKUPの数式の作り方については、下記を参照ください。
≫ エクセルの商品マスタ作成を自動転記で入力効率化＆検索100万件でも楽々｜2-2.基本的な数式の作り方
(2) VLOOKUPの数式部分だけコピーする
できた数式のVLOOKUPの部分だけをそれぞれコピーし、メモ帳やWordなどに貼り付けておきます。
「=」はコピーしないでください。正しく機能しなくなります。
(3) 関数ダイアログを開く
数式を作成するセルを選択し、「fx」をクリックします。
(4) IFERROR関数を検索して呼び出す
「関数の挿入」ダイアログが開くので、❹「関数の検索」の欄に「IFERROR」と入力し、検索して呼び出します。
(5) 1つ目の数式を貼り付ける
❽「値」の欄に先ほど作成したVLOOKUPの1つ目の数式を貼り付けます。
❾「エラーの場合の値」の欄をクリックします（※何も入力しません）。
(6) IFERROR関数を呼び出す
❿エクセル画面左上の「ファイル」の下にある「▼」をクリックします。使用した関数の履歴が出てくるので「IFERROR」を選んでください。
(7) 2つ目の数式を貼り付ける
新しい関数の入力画面が開きます。
⓬「値」の欄にVLOOKUPの2つ目の数式を貼り付けます。
⓭「エラーの場合の値」の欄には、「”該当なし”」を入力します。
完成したら下のセルにコピーしてください。
コピー履歴から連続して貼り付けする方法【Windowsのみ】
数式を貼り付ける際、コピーの履歴から連続して貼り付けることができます。
「Windowsキー+V」を同時に押すと、コピー履歴が開くので選んでクリックするだけです。
複数の数式をまとめてコピー＆貼付するのに便利です。
※ Macの場合は、クリップボード（コピー履歴）を管理できるアプリを入れる必要があります。
VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ VLOOKUPの使い方｜基礎から応用まで完璧マスター＆便利技20選を超図解！
3. VLOOKUPとIFERRORの基礎知識
ここではVLOOKUPとIFERRORの基礎知識を解説します。
3-1. VLOOKUPの基礎知識
VLOOKUPとは、指定した範囲から特定の値を探し出し、紐づくデータを返す関数です。数式は次のとおりです。
=VLOOKUP( 検索値 , 検索範囲 , 列番号 , 検索方法 )
| 項目 | 内容 | 例 |
|---|---|---|
| 検索値 | データを探し出す手掛かりとなる値 | A1 |
| 検索範囲 | 探したいデータが入っている表 | A:D （列全体で指定） A1:D4（長方形で指定） |
| 列番号 | 左から何列目を返すか | 2 |
| 検索方法 | 一致した場合のみ返すか、近似値でも返すか | FALSE（完全一致） TRUE（近似値） |
各項目の基礎知識についてさらに詳しく知りたい方はこちら
≫ VLOOKUPの使い方｜3.VLOOKUPはどうやって使うの？｜各項目の解説
マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft｜Officeのサポート ～ VLOOKUP関数
3-2. IFERRORの基礎知識
IFERRORはエラーを判定し、エラーの場合は指定の値を返す関数です。
IFERRORの数式
IFERRORの数式は次のとおりです
=IFERROR( 値 , エラーの場合の値 ）
| 項目 | 内容 | 例 |
|---|---|---|
| 値 | 判定したい計算式などを入れる （正常ならここの計算結果がそのまま表示される） | VLOOKUPの数式 |
| エラーの場合の値 | エラーの場合に表示したい値を入れる | 「””」 |
IFERRORの処理の流れ
IFERRORは次の流れに従って処理します。
❶に入っている値がエラーかどうかを判定します。正常ならその結果をそのまま返します。
計算結果がエラーの場合、❷の「エラーの場合の値」を返します。
数式を入れれば、その計算結果を表示できます。
マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft｜Officeのサポート ～ IFERROR関数
4. まとめ（IFERRORでエラーを非表示｜複数の範囲を検索）
この記事では次のことを解説しました。
VLOOKUP に IFERROR を組み合わせて
- エラーを非表示にする仕組みと数式の作り方
- 複数の範囲を検索する仕組みと数式の作り方
エラーが想定内なら表示させない方が、見た目もスッキリし、作業に集中できるようになります。
複数の範囲を検索できれば、例えば社員名簿の検索で、部署ごとに分かれていても1つの数式ですべて検索できて便利です。
本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルもダウンロード可能です。
≫ PDFファイル参照ページはこちら
VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事も参照ください。
≫ VLOOKUPの使い方｜基礎から応用まで完璧マスター＆便利技20選を超図解！
エクセルでお困りのことがありましたら、『エクセルの窓口』で検索してください。
ブックマークやお気に入りに登録していただけたら幸いです。