VLOOKUP関数を使う際、複数シートから検索してほしい時ありますよね。
ですがVLOOKUP関数単体では検索先を複数指定する事はできません。
しかし、一手間かければできちゃいますので、やり方をご紹介。
今回はEXCEL2007から登場したIFERROR関数を使っていきますので、どうしても古い2007より前の古いエクセルを使いたいという方は、頑張ってIFとISERRORで再現してみてください。
IFERROR関数について
まず、今回使用するIFERROR関数について簡単に説明。
式：=IFERROR(値,エラー時の値)
最初に指定した値がエラーだった場合、別の結果を返すという関数です。
例えば、=IFERROR(VLOOKUP(A1,B:C,2,FALSE),"見つからない")という式にすると、A1セルの内容をB列から探して、見つかれば2列目（C列）の値を返す。見つからなければ#N/Aではなく、"見つからない"という文字を返すという事できます。
IFERROR関数については下記記事を参考に使って遊んでみてください。
実際にやってみよう！
ここから実践です。
大きく分けて、1つの式に全部まとめるパターンと、作業用セルを用意するパターンに分けて紹介していきます。
1つの式に全部まとめる
指定した氏名を名簿から検索し、生年月日を表示するという例で実験していきます。
ただし、名簿は男性と女性という、2つのシートに分かれている事とする。
どういう事かというと、図のとおり。
このように、どちらかのシートに検索対象が存在した場合、生年月日が表示されるような式を作っていきます。
そんな事ができてしまう式がコチラ。
式：=IFERROR(VLOOKUPその１,VLOOKUPその２)
やっている事は、VLOOKUPその１でエラーになったら、VLOOKUPその２を使うという内容。
今回の例題で実際に使っている式は、下記のようになっています。
上段（C4セル）の式：=IFERROR(VLOOKUP(B4,男性!A:C,3,FALSE),VLOOKUP(B4,女性!A:C,3,FALSE))
下段（C5セル）の式：=IFERROR(VLOOKUP(B5,男性!A:C,3,FALSE),VLOOKUP(B5,女性!A:C,3,FALSE))
まずは男性シートを検索し、見つからなければ女性シートを検索するという式です。
もし同姓同名がいた場合は、前半のVLOOKUP式から動くので、必要ならば順番を入れ替えてください。今回の場合は男性シートの内容が優先されます。
今回は検索先が2シートですが、3シート以上またいで検索したい場合は、IFERROR関数をもう一段追加しましょう。
式はこのようになります。
式：=IFERROR(IFERROR(VLOOKUPその１,VLOOKUPその２),VLOOKUPその３)
例題で実際に使っている式はこんな感じに。
式：=IFERROR(IFERROR(VLOOKUP(B6,男性!A:C,3,FALSE),VLOOKUP(B6,女性!A:C,3,FALSE)),VLOOKUP(B6,超越者!A:C,3,FALSE))
マーカー部分が追加された感じですね。
このようにIFERROR関数を追加していく事で、もっと多くのシート数にも対応したい場合でも、1セル内で完結する事ができるというのが、このやり方のメリットです。
反面、式が長くなりすぎて混乱してきますし、あとから検索対象のシートを増やす場合、式のメンテナンスが面倒くさいことになるというデメリットを含んでいます。
作業用セルを使う
続いて、１つの式で完結させずに、作業用セルを用意する方法。
まず各シートから検索し、見つからなければ空欄という式を用意します。
先頭行の式：
=IFERROR(VLOOKUP($B4,男性!$A:$C,3,FALSE),"")
=IFERROR(VLOOKUP($B4,女性!$A:$C,3,FALSE),"")
=IFERROR(VLOOKUP($B4,超越者!$A:$C,3,FALSE),"")
そして最後に、SUM関数や、＆でセルを結合して、検索結果を表示するためのセルを用意します。（今回は生年月日なのでSUM関数を使ってますが、太古だけ文字列なので表示が上手くいってません。でもセイウチの神による影響なので仕方ないね。）
これを最終行までオートフィルやコピーで範囲を広げます。
作業スペースを要するので、1つの式にまとめた場合に比べ、見た目的には散らかった印象がありますね。
ですが、検索先のシート数が増えても式の長さは変わらない、作業場所を増やせばすぐに対応できるといったメリットがあります。
さらに言えば、シート名を指定する部分にINDIRECT関数を使えば、どれだけシートが後から増えようと、メンテナンスも簡単になります。
今回ですと、こんな式になります。
式：=IFERROR(VLOOKUP($B4,INDIRECT(C$3 & "!$A:$C"),3,FALSE),"")
これをC4セルに用意してしまえば、あとは検索先のシート数が増えたとしても、作業場所の列を増やして、シート名と連動してるタイトル部分を入力して、C4セルに作った式をオートフィルやらコピーやらで持ってきて、最後の検索結果用の式さえ調整すれば、メンテナンス完了になります。
このINDIRECT関数とVLOOKUP関数の組み合わせについて気になる方は、下記記事も併せてご覧ください。
まとめ
2通りのやり方をご紹介してきました。
使い分けとしては、検索先が少ない場合は1つの式に、多かったり増減する場合は作業場所を作る方法をオススメします。
あと、今回は複数シートを検索するという話で進めてきましたが、同シート内の別表を検索する場合も、同じような考え方で作れます。
こんな感じの式で。
式：=IFERROR(VLOOKUP(A1,B:C,2,FALSE),VLOOKUP(A1,E:F,2,FALSE))
同シート内の、B～C列の表、E～F列の表を順に検索する式です。
以上、そんなに頻繁に使う事はないでしょうが、知っておいて損はないタイプの応用でした。
コメント