VLOOKUPとHLOOKUP
一般にLOOKUP系の関数というと、VLOOKUPを指すケースが多います。
一方で、LOOKUP系の関数にはほかにも、HLOOKUPやXLOOKUPといった関数が存在します。
ここではまとめて使い方と一緒に解説していきます。
VLOOKUPのVはVerticalのV
VLOOKUP、一般にブイ・ルックアップと呼ばれますが、ここでのVとは、Vertical、つまり垂直を意味します。
VLOOKUPの構文は、次の通りですが、検索値を「縦に」探すという機能を持つ関数です。
=VLOOKUP(検索値,検索値が含まれるセル範囲,戻り値を含む範囲内の列番号,検索方法)
例えば前回と同じような、上のような表があるとして、「A株式会社」の取引金額が知りたいとします。
その場合、
=VLOOKUP(“A株式会社”,A2:D6,4,0)
という式を入れると結果が返ってきます。
この時、VLOOKUPがわかりにくい理由は二つあります。それは、「検索値が含まれるセルの範囲」という言い方と検索方法です。
検索値が含まれるセルの範囲
一般に、「検索値が含まれるセルの範囲」というと、そのどこかに検索値が含まれていれば、それでいいような気がします。
しかしながら、VLOOKUPでは、その範囲のうち、一番左側の列しか検索しません。したがって、上の式で、検索値に「東北」などと入れたとしても、「#N/A」と、エラーしか返ってきません。
あくまでVLOOKUPは縦に探すことしかできないので、一番左上のセルから、「縦に移動しながら探す」という機能しかないわけです。
あくまでここでセルの範囲を横に広げているのは、 「戻り値を含む範囲内の列番号」の指定場所を決めるための目的しかありません。
「A2:D6」の指定範囲の中で、取引金額が記載されているのは4列目なので、三つ目の引数に「4」と入れるわけですが、その指定をするための前提でしかない、ということです。
あくまでVLOOKUPの二つ目の引数は、VLOOKUPの処理対象範囲という意味でしかない、と理解しておくといいと思います。
一番左上から縦に検索をかけ、該当したところで、その行の指定列に何が入っているかを返す、というイメージです。
検索方法
もう一つは検索方法です。ここには完全一致を示すFalse（=0）を入れるのが一般的ですが、これを入れないと、自動でTrue（=1）であるとみなされ、近似一致検索がなされます。
Excelの関数の中では、Trueと1は同じ、Falseと0は同じという処理がなされます。
なので、
=VLOOKUP(“A株式会社”,A2:C6,3,0)
=VLOOKUP(“A株式会社”,A2:C6,3,FALSE)
は同じ意味になります。
ここで「近似一致」というと、より近いものを示しているように見えますが、少しややこしいのが、近似一致といっても、一番近いものを強い召しているわけではないということです。
ほかの関数での近似一致もそうですが、ここでの近似一致とは、二分探索というかなり特殊な方法で検索がなされており、データが昇順でソートされているときにはじめて効力を発揮するものとされています。
例えば先ほどの例で、 =VLOOKUP(“A株式会社”,A2:C6,3,1)とした場合は特に結果は変わりませんが、=VLOOKUP(“株式会社E”,A2:D6,4,1)とすると、全く予想しない結果しか出てきません。
詳細な説明は割愛しますが、A列を昇順でソートすると
・A株式会社→B商店→株式会社E→合同会社D→有限会社C
という順番になります。
いまA列は、
・A株式会社→B商店→有限会社C→合同会社D→株式会社E
となっていますが、株式会社Eは、上の昇順の考え方からすると、A株式会社と有限会社Cの間に入っていないとおかしくなります（二分探索は対象がソートされていることが前提です）。
なので、「有限会社C」と入っているセルより下は探さず、その上のどこに当てはめるべきか、という探索しかしません。
なので、予想しない結果が出てきてしまいます。
HLOOKUPのHはHorizontalのH
VLOOKUPのポイントは以上の通りですが、HLOOKUPは、これを横向きにしただけです。
対象範囲の一番左上のセルから、横向きに検索をかけ、該当した列の指定行のセルの値を返します。
なので、2行目に書いてあるA株式会社の取引金額が知りたいとすると、次のような式を入れる、ということになります。
=HLOOKUP(“取引金額”,A1:D6,2,0)
ちなみに、例をわかりやすくするために検索対象範囲を「A1:D6」として、ヘッダーを含めて検索しましたが、やっていることはVLOOKUPを横向きにしただけです。
新関数XLOOKUP
使い勝手を考えたXLOOKUP
このように、VLOOKUPやHLOOKUPには、微妙に使い方がわかりにくいという問題がありました。
そこで新しく作られたのがXLOOKUP関数です。
XLOOKUPは、これはIFSやSWITCHと同様、2019年以降実装された関数で、今までのVLOOKUPなどの使いにくさを改良した関数だとされています。
構文は次のようになっていますが、後半を指定することはほとんどないので、前半三つの引数を覚えておけば十分です。
=VLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
おそらく書いてしまった方がイメージがつきやすいので、式として書いてしまうと、XLOOKUPで「A株式会社」の取引金額を調べる場合は次のように記載します。
=XLOOKUP(“A株式会社”,A2:A6,D2:D6)
この関数のイメージは「A2:A6」の範囲で「A株式会社」という検索値を探して、その範囲内での相対的な場所を、「D2:D6」において返すというイメージです。「A2:A6」の中で、「A株式会社」は一番左上にあるので、「D2:D6」の一番左上にある「D2」に記載されている値を返すといった具合です。
縦横の検索が直感的
VLOOKUPで分かりにくかった「検索値が含まれるセル範囲」と異なり、XLOOKUPの検索範囲は、一列もしくは一行しか指定できません。
設定される検索範囲によって、縦検索か横検索かが自動で決まりますが、その操作は非常に直感的です。
あくまで検索範囲として指定された範囲でのみ検索を行うので、その挙動も非常にわかりやすいです。
また、VLOOUPでは、一番左側の列しか検索できなかったため、戻り値は検索値よりも右側に記載されている必要がありました（同じく、HLOOKUPは一番上の行しか検索できませんでした）。
これに対して、XLOOKUPでは、検索範囲と戻り範囲を別々に設定するので、戻り値がより左やより上に記載されていても、特段問題はありません。
指定しなければ完全一致
VLOOKUPでは指定しなければ近似一致という挙動が予測しずらい一致方法で検索をかけていました。
これに対して、XLOOKUPでは、指定しなければ完全一致で検索がされます。特に検索対象をソートする必要もないですし、「LOOKUP」という言葉の意味にもより合致していると思います。
まとめ
ここまでLOOKUP系関数の解説をしてきました。XLOOKUPは便利な一方で、比較的新しいExcelのソフトでないと開かず、やはりこれも、IFSなどと同様外部向けのファイルには使うべきではありません。
そんなLOOKUP系の関数ですが、「検索」というと、使いどころはそこまで多くないようにも感じます。
しかしながら、少し別な視点で考えると、非常に便利な関数と考えられています。具体的な使いどころについては、次の記事をご覧ください！
コメント