VLOOKUPのエラーをIFERRORで非表示&複数範囲を一発検索

広告

VLOOKUPのエラーをIFERRORで非表示&複数範囲を一発検索
  • VLOOKUPのエラーを非表示にしたい
  • VLOOKUPで複数範囲を検索したい

これらは VLOOKUP に IFERROR を組み合わせればOKです。

この記事では次のことを解説します。
※お急ぎの方は、ボタンから該当箇所へ移動してください。

解説内容詳細へ
エラーを非表示にする数式の作り方詳細へ
複数範囲を検索する数式の作り方詳細へ

エラーが想定内なら表示させない方が、見た目もスッキリし、作業に集中できるようになります。

複数の範囲を検索できれば、例えば社員名簿の検索で、部署ごとに分かれていても1つの数式ですべて検索できて便利です。

作業の効率が上がり、きっと時短につながることでしょう。

本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルもダウンロード可能です。
≫ PDFファイル参照ページはこちら

VLOOKUP を基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ VLOOKUPの使い方|基礎から完璧マスター&便利技20選を超図解!

1. VLOOKUPのエラーをIFERRORで非表示にする方法

エクセルVLOOKUPの使い方【応用編】IFERRORでエラーを非表示にする方法
VLOOKUPのエラーをIFERRORと組み合わせて非表示にする(画像はクリックで拡大)

≫ 解説図のPDFファイル

この章では、VLOOKUPのエラーをIFERRORで非表示にする方法を解説します。

エラーが想定内であったとしても、表示されたままだと気になってしまいます。表示させないようにできれば、見た目もスッキリし、作業に集中できることでしょう。

1-1. エラーを非表示にする数式

VLOOKUPのエラーをIFERRORで非表示にする数式は次のとおりです。

=IFERROR(VLOOKUP(D2,A:B,2,FALSE),””)

項目内容
D2出席番号を入力するセル
A:Bテスト結果一覧表の範囲
22列目のデータを返せ
FALSE一致する場合のみ返せ
“”エラーの場合は「””」(空白)にせよ

VLOOKUPとIFERRORの基礎知識を確認したい方はこちら
≫ 3. VLOOKUPとIFERRORの基礎知識

1-2. エラーを非表示にする仕組み

エラー非表示の仕組み
IFERRORと組み合わせてエラーを非表示にした場合の例

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の使い方【応用編】VLOOKUPのエラーを非表示にする数式の作り方
VLOOKUPのエラー非表示にする数式の作り方(画像はクリックで拡大)

≫ 解説図のPDFファイル

VLOOKUPにIFERRORを組み合わせた数式の具体的な作り方を解説します。

数式の作り方

=IFERROR(VLOOKUP(D2,A:B,2,FALSE),””)

テスト結果から集計表へ自動転記する数式です。存在しない出席番号を入力しても、エラーにならず空欄になります。

数式の作り方は次のとおりです。

(1) 先にVLOOKUPの数式を完成させておく

数式の作り方

VLOOKUPだけの数式を先に作り、正常な値が転記されることを確認します。
まとめて作成すると、うまくいかないときにどこに問題があるのかわかりにくくなります。

VLOOKUPの数式の作り方については、下記を参照ください。
≫ エクセルの商品マスタ作成を自動転記で入力効率化&検索100万件でも楽々|2-2.基本的な数式の作り方

(2) VLOOKUPの数式部分だけコピーする

数式の作り方

VLOOKUPの数式部分だけコピーします。「=」はコピーしないでください。「=」が混じっていると数式が正しく動きません。

(3) 数式を削除してから関数ダイアログを開く

数式の作り方

数式を削除してから、「fx」をクリックします。

数式を削除せずに開いてしまうと
VLOOKUPが開く

数式を削除せずに「fx」をクリックすると、セルに残っているVLOOKUPの設定画面が開いてしまいます

(4) IFERROR関数を検索して呼び出す

数式の作り方

「関数の挿入」ダイアログが開くので、「関数の検索」の欄に「IFERROR」と入力し、検索して呼び出します。

(5) 数式を貼り付ける

「値」の欄に先ほどコピーしたVLOOKUPの数式を貼り付けます。このとき「=」が無いことを確認しましょう。
「エラーの場合の値」の欄には、「””」を入力します。

完成したら下のセルにコピーしてください。

値の欄に数式を貼り付ける際、「=」を含めてしまうと、貼り付けた数式がただの文字列として扱われます。勝手に「””」で挟まれ、計算結果もおかしくなります。

2. VLOOKUPにIFERRORを組み合わせて複数の範囲を検索する方法

エクセルVLOOKUPの使い方【応用編】IFERRORと組み合わせて複数シートを検索する方法
VLOOKUPにIFERRORを組み合わせて複数シートを検索する(画像はクリックで拡大)

≫ 解説図のPDFファイル

この章では、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:B1つ目の社員名簿の範囲
D:E2つ目の社員名簿の範囲
22列目のデータを返せ
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と組み合わせて複数シートを検索する数式の作り方
VLOOKUPにIFERRORを組み合わせて複数の範囲を検索する数式の作り方(画像はクリックで拡大)

≫ 解説図のPDFファイル

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のみ】
エクセルVLOOKUPの使い方【応用編】コピー履歴から連続貼付する方法

数式を貼り付ける際、コピーの履歴から連続して貼り付けることができます。
「Windowsキー+V」を同時に押すと、コピー履歴が開くので選んでクリックするだけです。

複数の数式をまとめてコピー&貼付するのに便利です。

※ Macの場合は、クリップボード(コピー履歴)を管理できるアプリを入れる必要があります。

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ VLOOKUPの使い方|基礎から応用まで完璧マスター&便利技20選を超図解!

3. VLOOKUPとIFERRORの基礎知識

ここではVLOOKUPとIFERRORの基礎知識を解説します。

3-1. VLOOKUPの基礎知識

エクセルVLOOKUPの使い方【応用編】VLOOKUPの処理のイメージ
VLOOKUPの処理のイメージ(画像はクリックで拡大)

≫ 解説図のPDFファイル

VLOOKUPとは、指定した範囲から特定の値を探し出し、紐づくデータを返す関数です。数式は次のとおりです。

=VLOOKUP( 検索値 , 検索範囲 , 列番号 , 検索方法 )

項目内容
検索値データを探し出す手掛かりとなる値A1
検索範囲探したいデータが入っている表A:D (列全体で指定)
A1:D4(長方形で指定)
列番号左から何列目を返すか2
検索方法一致した場合のみ返すか、近似値でも返すかFALSE(完全一致)
TRUE(近似値)

各項目の基礎知識についてさらに詳しく知りたい方はこちら
≫ VLOOKUPの使い方|3.VLOOKUPはどうやって使うの?|各項目の解説

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート ~ VLOOKUP関数

3-2. IFERRORの基礎知識

IFERRORの説明

IFERRORはエラーを判定し、エラーの場合は指定の値を返す関数です。

IFERRORの数式

IFERRORの数式は次のとおりです

=IFERROR(  , エラーの場合の値 )

項目内容
判定したい計算式などを入れる
(正常ならここの計算結果がそのまま表示される)
VLOOKUPの数式
エラーの場合の値エラーの場合に表示したい値を入れる「””」

IFERRORの処理の流れ

IFERRORは次の流れに従って処理します。

IFERRORの説明

に入っている値がエラーかどうかを判定します。正常ならその結果をそのまま返します。

IFERRORの説明

計算結果がエラーの場合、の「エラーの場合の値」を返します。
数式を入れれば、その計算結果を表示できます。

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート ~ IFERROR関数

4. まとめ(IFERRORでエラーを非表示|複数の範囲を検索)

この記事では次のことを解説しました。

VLOOKUP に IFERROR を組み合わせて

  • エラーを非表示にする仕組みと数式の作り方
  • 複数の範囲を検索する仕組みと数式の作り方

エラーが想定内なら表示させない方が、見た目もスッキリし、作業に集中できるようになります。

複数の範囲を検索できれば、例えば社員名簿の検索で、部署ごとに分かれていても1つの数式ですべて検索できて便利です。

本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルもダウンロード可能です。
≫ PDFファイル参照ページはこちら

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事も参照ください。
≫ VLOOKUPの使い方|基礎から応用まで完璧マスター&便利技20選を超図解!

エクセルでお困りのことがありましたら、『エクセルの窓口』で検索してください。

エクセルの窓口検索

ブックマークお気に入りに登録していただけたら幸いです。