Excelで複数のシートを横断検索する3つの方法|VLOOKUP関数の実践テクニック

必要な情報がすべて同一のワークシート内に揃っているとは限りません。完全なデータベースを作成するには、Excelの別のシートから必要なデータを取り出す必要があります。こうした手間を解消するために、Microsoft ExcelにはVLOOKUPという汎用性の高い関数が用意されており、複数のシートを横断してデータを検索できます。本記事では、VLOOKUP関数を使って複数のシートを簡単に検索する3つの方法を詳しく解説します。

練習用ワークブックのダウンロード

記事と一緒に配布しているExcelファイルをダウンロードして、実際に操作しながら学習することをおすすめします。

Excelで複数のシートを検索する3つの方法

ここでは、ある書店がオンラインと実店舗の両方で書籍を販売しているケースを想定します。この書店には、「オンライン販売用」と「店頭販売用」の2つの書籍リスト(シート)があります。

 

このチュートリアルでは、これら2つの書籍リストを結合し、1つの完全なリストを作成する方法を、3つの異なる手法でご紹介します。

方法1:IFERROR関数を使って複数シートを検索する

オンラインと店頭の両方で販売中の書籍を含む完全なリストを作成するには、「Store(店頭)」と「Online(オンライン)」の両ワークシートの情報を組み合わせる必要があります。

以下の手順に従って、検索方法をマスターしましょう。

🔗 手順:

❶ まず、数式の結果を表示するセル C5 を選択します。

❷ 次に、以下の数式を入力します。

=IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,2, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 2, FALSE), "Not found"))

❸ Enterキーを押します。

❹ フィルハンドル(セル右下の小さな四角)をドラッグし、「Book Name(書名)」列の最終行までコピーします。

これで完了です。

💡 「Author(著者)」列も同様に入力するには、次の数式をセル D5 に入力し、手順1〜4を繰り返します。

=IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,3, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 3, FALSE), "Not found"))

␥ 数式の解説

📌構文: IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, "Not found"))

• B5 ▶ 検索キーとして機能するIDを参照します。
• Store!$B$5:$D$9 ▶ 「Store」ワークシートの B5〜D9 の範囲内で検索を行います。
• Online!$B$5:$D$9 ▶ 「Online」ワークシートの B5〜D9 の範囲内で検索を行います。
• 2 ▶ 書籍名が格納されている「Book Name」列(2列目)から値を取得することを示します。
• FALSE ▶ 検索時に完全一致を指定する引数です。
• =IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,2, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 2, FALSE), "Not found")) ▶ ID番号96に対応する書籍名を返します。最初のシートで見つからない場合、自動的に次のシートへ検索が切り替わり、どちらにも存在しなければ「Not found」が表示されます。

方法2:INDIRECT関数で複数シートをVLOOKUP検索する

IFERROR関数の代わりに、INDIRECT関数を使用しても複数シートを横断検索できます。ただし、INDIRECT関数は柔軟性が高い反面、構文がやや複雑になる点に注意が必要です。VLOOKUP関数と組み合わせて使う場合は、数式の記述ミスがないか慎重に確認しましょう。それでは、具体的な手順を見ていきましょう。

🔗 手順:

❶ まず、数式の結果を表示するセル C5 を選択します。

❷ 次に、以下の数式を入力します。

=VLOOKUP($B5,INDIRECT("'"&INDEX($F$5:$F$6,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$F$6&"'!$B5:$B9"),$B5)>0,0))&"'!$B$5:$D$9"),2,0)

❸ Enterキーを押します。

❹ フィルハンドルをドラッグし、「Book Name(書名)」列の最終行までコピーします。

これで完了です。

💡 「Author(著者)」列も同様に入力するには、次の数式をセル D5 に入力し、手順1〜4を繰り返します。

=VLOOKUP($B5,INDIRECT("'"&INDEX($F$5:$F$6,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$F$6&"'!$B5:$B9"),$B5)>0,0))&"'!$B$5:$D$9"),3,0)

␥ 数式の解説

📌構文: VLOOKUP(lookup_value, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(TRUE, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!lookup_range"), lookup_value)>0), 0)) & "'!table_array"), col_index_num, FALSE)

• Lookup_value ▶ $B5 ▶ 検索の基準となるキーワード(ID)です。
• Lookup_sheets ▶ $F$5:$F$6 ▶ 検索対象となるシート名のリストが格納されたセル範囲です。
• Lookup_range ▶ $B5:$B9 ▶ 検索値が存在する範囲です。
• Table_array ▶ $B$5:$D$9 ▶ データテーブル全体の範囲です。
• Column_index_number ▶ 2 ▶ 取得したいデータがある列の番号です。

この方法のメリットは、シート名のリスト($F$5:$F$6)を編集するだけで検索対象のシートを追加・変更できる点です。シート数が多い場合に特に便利です。

方法3:ネストしたIF関数で複数シートを検索する

複数シートを横断検索するもう一つの方法として、ISNA関数とVLOOKUP関数を組み合わせたネストIF(入れ子のIF)関数を使うやり方があります。

ただしこの方法は、データを取得する対象シートが少数の場合にのみ使いやすい手法です。シートの数が増えるほど数式が複雑になり、管理が難しくなるため、多数のシートを扱う場合にはおすすめできません。

それでは、以下の手順で数式の動作を確認してみましょう。

🔗 手順:

❶ まず、数式の結果を表示するセル C5 を選択します。

❷ 次に、以下の数式を入力します。

=IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,2,0)),VLOOKUP($B5,Online!$B$5:$D$9,2,0),IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,2,0)),VLOOKUP($B5,Store!$B$5:$D$9,2,0)))

❸ Enterキーを押します。

❹ フィルハンドルをドラッグし、「Book Name(書名)」列の最終行までコピーします。

これで完了です。

💡 「Author(著者)」列も同様に入力するには、次の数式をセル D5 に入力し、手順1〜4を繰り返します。

=IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0),IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0)))

␥ 数式の解説

📌 構文: IF(ISNA(VLOOKUP(lookup_value,table_array,col_index_number,0)),value_if_true,value_if_false)

• Lookup_value ▶ $B5 ▶ 検索の基準となるキーワード(ID)です。
• Table_array ▶ $B$5:$D$9 ▶ データテーブル全体の範囲です。
• Column_index_number ▶ 2 ▶ 取得したいデータがある列の番号です。
• ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)) ▶ $B5が示すIDが範囲 $B$5:$D$9 内に存在するかどうか(エラーになるかどうか)を判定します。
• IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0) ▶ Storeシートで見つからなかった場合(TRUE)、Onlineシートから対応する書籍名を取得します。
• 上記の条件がFALSEの場合は、IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0)) へ処理が移ります。
• IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0)) ▶ Onlineシートで見つからなかった場合は、Storeシートから書籍名を取得します。

注意ポイント

📌 検索値は、テーブル配列(範囲)の必ず左端(先頭)の列に配置してください。

📌 配列数式を確定するときは、Ctrl + Shift + Enter を同時に押しましょう。

📌 関数の構文ミスがないか、常に注意してください。

📌 数式に挿入するデータ範囲は、正確に指定しましょう。

まとめ

本記事では、Excelで複数のシートを横断して検索する3つの方法(IFERROR関数・INDIRECT関数・ネストIF関数)を解説しました。それぞれの方法には特徴があり、シンプルさを重視するならIFERROR、柔軟性を重視するならINDIRECT、というように状況に応じて使い分けるのがおすすめです。ぜひ記事添付の練習用ワークブックをダウンロードして、すべての方法を実際に試してみてください。ご不明な点があれば、下のコメント欄からお気軽にお尋ねください。できるだけ早く回答いたします。

関連記事

• Excelで補助列なしのVLOOKUPを複数条件で使う5つの方法
• INDEX MATCHを日付範囲の複数条件で使う方法
• INDEX-MATCHで部分一致テキストを複数条件検索する2つの方法
• XLOOKUPを複数条件で使う4つの簡単な方法