複数シートをまたぐVLOOKUPとは？
月別シートを作成する場合
複数シートをまたぐVLOOKUPとは、例えば、月別シートから情報を引っ張ってくるような場合を指します。
各月ごとにデータを作成しており、その中のいずれかに該当する場合に、その行に書かれた値を引っ張ってくる場合を想定すると分かりやすいと思います。
管理上はシートを分けたいけれども、横断して検索をかけて値を引っ張ってきたい場合などが、これにあたります。
今回扱う例
そこで今回は、Sheet2とSheet3にそれぞれ、次のような情報が入っている場合を想定して、処理を行います。
これらのシートが仮に一つに該当していれば、例えば、
=VLOOKUP(“key2-1”, A;B, 2, 0)
と入れれば、「item2-1」が返ってくることが想定されます。
横断したVLOOKUP
IFERRORを用いる場合
具体的にこれらを横断してVLOOKUPを行う方法ですが、まず考えられるのはIFERRORを用いる場合です。
例えば、
=VLOOKUP(“key2-1”, Sheet2!A;B, 2, 0)
と入れると、Sheet2には、key2-1がないので、「#N/A」が返却されることになります。
これを活用して、IFERRORを用い、ERRORが返却される場合にSheet3を検索するという式を入れるという方法です。
具体的には、次のような式を入れることを想定しています。
=IFERROR(VLOOKUP(“key2-1”, Sheet2!A;B, 2, 0),VLOOKUP(“key2-1”, Sheet3!A;B, 2, 0))
これでitem2-1が返却されます。
VSTACKの出番！
ただ、この方法だと3シート目を入れるときに、次のように表す必要があり、どんどん長くなっていきます。
=IFERROR(IFERROR(VLOOKUP(“key2-1”, Sheet2!A;B, 2, 0),VLOOKUP(“key2-1”, Sheet3!A;B, 2, 0)),VLOOKUP(“key2-1”, Sheet4!A;B, 2, 0))
また、検索する値を何度も入れなければならず、かなり面倒です。
そこで、そもそも検索する配列をまとめてしまうということが考えられます。そこで、VSTACK関数を用います。
VSTACK関数を用いると、配列の列数が同じ配列を、縦に結合することができます。
試しに、「=VSTACK(Sheet2!A1:B14,Sheet3!A1:B18)」と入れると、Sheet2とSheet3の配列が縦につながった状態のものが返却され、スピルします。
これを応用して、次のように入力すると、思い通りのitem2-1が返却されます。
=VLOOKUP(“key2-1”,VSTACK(Sheet2!A1:B14,Sheet3!A1:B18),2,0)
OFFSETで応用
ちなみに、このやり方だと、配列の一番下の位置を指定してあげる必要が出てきます。
通常のVLOOKUPのように、検索する対象の配列を、「A:B」といった具合に縦の列名だけで指定することはできません。
こういう場合は、OFFSETを用いると、自動で配列のサイズも変えることができるため、便利です。
=VLOOKUP(“key2-1”,
VSTACK(
OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),2),
OFFSET(Sheet3!A1,0,0,COUNTA(Sheet3!A:A),2)
),2,0)
わかりやすくインデントを入れていますが、こうすることで、シートごとに入っている内容を考慮して、配列のサイズを自動で変えることができます。
ちなみに、A列の途中に空白のセルがあるとうまくいかないので、その点は注意です。
コメント