「大量のデータ一覧から、特定の商品の『価格』や『商品番号』を自動で引っ張ってきたい……」
そんなときに思い浮かぶのが「VLOOKUP関数」ですが、「引き出したいデータが検索キーより左側にあるからエラーになる」「横に長い表だと使えない」と、途方に暮れた経験はありませんか？
Googleスプレッドシートには、データの並び方（縦・横）や位置関係に合わせて使い分けるべき3つの検索関数（VLOOKUP、HLOOKUP、XLOOKUP）があります。この記事では、それぞれの基本的な使い方から、「結局どれをいつ使えばいいの？」という用途別の使い分けまで、実例を交えてスッキリ解説します！
HLOOKUP関数
基本的な使い方
構文＝HLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])
水平方向の検索です。範囲の 1 行目で指定したキーを検索し、同じ列内にある指定したセルの値を返します。
以前ご紹介した「VLOOKUP関数」と同様に、「データから指定した値を検索する」関数になります。
VLOOKUP関数との違い
では「VLOOKUP関数」との違いは何でしょうか。機能的にはほぼ同じ関数なのですが、検索する方向が違います。
- VLOOKUP関数は縦方向の検索
- HLOOKUP関数は横方向の検索
参照するデータが縦方向なのか、横方向なのかで使い分けます。
実際の使い方
まずは「VLOOKUP関数」の使い方について振り返ってみましょう。D２のセルに「=VLOOKUP(C2,$D$23:$E$27,2,0)」と入力しています。
②のC列に商品名を入力すると自動的に③の表から商品価格を①に入力します。この時、商品価格の検索は「垂直方向」に行われます。
次に「HLOOKUP関数」についてみてみましょう。D30のセルに、「=HLOOKUP(C30,$C$51:$G$52,2,0)」と入力しています。
②のC列に商品名を入力すると自動的に③の表から商品価格を①に入力します。言葉の説明としては、先程の「VLOOKUP関数」と同じですが、③の表での検索の仕方が違います。
「VLOOKUP関数」は垂直方向に商品価格を検索しましたが、「HLOOKUP関数」は「水平方向」に商品価格を検索しています。
XLOOKUP関数
基本的な使い方
構文＝XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)
検索範囲を検索して見つかった一致の位置に基づき、結果範囲内の値を返します。一致するものが見つからない場合は、最も近い値が返されます。
上記の説明では何が出来るのかよく分からないかもしれませんが、簡単に言うと、「VLOOKUP関数」と「HLOOKUP関数」の代わりに使用することが出来る関数になります。
VLOOKUP関数の代わりに使う
先程の「VLOOKUP関数」を使った表を「XLOOKUP関数」で書き換えてみましょう。
当然、結果は同じで、②のC列に商品名を入力すると自動的に③の表から商品価格を①に入力します。違いは、③の選択範囲です。
「VLOOKUP関数」では、表の中の「何列目（番号）」を使うか指定する必要がありました。
「XLOOKUP関数」では、「検索範囲」、「結果範囲」をそれぞれ個別に指定します。
HLOOKUP関数の代わりに使う
「HLOOKUP関数」の代わりに使う場合もみてみましょう。
こちらも結果は同じになります。「HLOOKUP関数」の代わりに使用しますので、③で水平方向の範囲を選択しています。「商品」が検索範囲になり、「価格」が結果範囲になります。
実用的な使い方
今まで「XLOOKUP関数」を「VLOOKUP関数」、「HLOOKUP関数」の代わりに使用する使い方をみてきました。しかし、「VLOOKUP関数」、「HLOOKUP関数」で出来ることをわざわざ「XLOOKUP関数」を使って計算する意味はありません。では、どのような場合に、「XLOOKUP関数」を使用するべきでしょうか。
実は、「VLOOKUP関数」、「HLOOKUP関数」共に、「検索キー」にある制限がかけられています。
- VLOOKUP関数：検索キーは、データ範囲の一番左の列であること
- HLOOKUP関数：検索キーは一番上にあり、その下のデータを返すこと
このような制限がかけられているために、使用するデータによっては「VLOOKUP関数」、「HLOOKUP関数」を使用することが出来ない場合があります。実際の例で確認してみましょう。
①にあるセルD2に、②の「商品名（検索キー）」から自動で③の「商品番号」を入力したい場合を考えてみましょう。下の表を見てみると、「商品番号」は検索キーの左側にあるために、「VLOOKUP関数」を使用することが出来ません。
このような場合に「XLOOKUP関数」であれば、「検索範囲」と「結果範囲」を個別に指定することが出来るため、問題なく商品番号を自動で入力することが出来るようになります。
計算式は以下になります。
=XLOOKUP(C2,$D$23:$D$27,$C$23:$C$27)
では④に、検索キーから自動で⑤の商品価格を入力したい場合はどうでしょうか。表を見ると商品価格は検索キーの右側にあることが分かります。
このような場合は、「XLOOKUP関数」を使うまでもなく、「VLOOKUP関数」を使用することで自動的に商品価格を入力することが出来ます。計算式は以下になります。
=VLOOKUP(C2,$D$23:$E$27,2,0)
「XLOOKUP関数」と「VLOOKUP関数」を使用することにより、正しい表を作成することが出来ました。
「HLOOKUP関数」の場合も確認しておきましょう。
セルD31に、商品名から自動で商品番号を入力する時は、商品番号が検索キーより上にあるので、「XLOOKUP関数」を使用します。数式は以下になります。
=XLOOKUP(C31,$C$53:$G$53,$C$52:$G$52)
セルE31に、商品名から自動で商品価格を入力する時は、商品価格が検索キーより下にあるので、「HLOOKUP関数」を使用します。数式は以下になります。
=HLOOKUP(C31,$C$53:$G$54,2,0)
絶対参照とIFERROR関数
すでにご存じの方も多いと思いますが、今回ご紹介したような表を作成する時は、「IFERROR関数」と「絶対参照」を記述するのを忘れないようにしましょう。
絶対参照
セルD31に「XLOOKUP関数」を普通に記述すると以下になります。
=XLOOKUP(C31,C53:G53,C52:G52)
D31には正しい計算結果、「102」が返されますが、D31を下の行に貼り付けると「#N/A」とエラーが表示されてしまいます。これは参照するデータの位置も変わってしまうために、「データが見つからない」ということで表示されるエラーになります。
なおGoogleスプレッドシートでは「絶対参照」を記述しなくても、下の行に数式をコピーしようとすると、正しいと思われる数式が候補として表示されます。このような候補が表示された場合は、矢印の先にあるチェックアイコンをクリックしてください。
先程の数式が訂正され、絶対参照を含んだ正しい数式が表示されるようになりました。
=XLOOKUP(C31,$C$53:$G$53,$C$52:$G$52)
またD32以降のセルにも絶対参照を含んだ数式がペーストされ、商品番号が正しく入力されています。
IFERROR関数
先程の表では、「商品名」がすべてのセルに記載されていましたが、実際に利用する時は、日々商品を入力していくと思います。その際は当然空白のセルが存在することになるのですが、XLOOKUP関数では検索キーが見つからないため「#N/A」エラーが表示されてしまいます。
このままでも、商品名を入力すれば、エラーは消えて商品番号や金額が表示されるのですが、見た目的にも良くありませんし、表が分かりにくいものになってしまいます。そこで、商品名が未入力でもエラーを表示させないようにする関数が「IFERROR関数」になります。
使い方はとても簡単で、「XLOOKUP関数」の数式を「IFERROR関数」で囲むだけになります。
=IFERROR(XLOOKUP(C31,$C$53:$G$53,$C$52:$G$52))
あとはこの数式を表の最後まで貼り付ければ、先程まで表示されていた「#N/A」エラーは表示されなくなります。
ここでは単純にエラーが表示されないように「IFERROR関数」を使用しましたが、入力が未入力の場合に特定の数字やメッセージを挿入することも可能です。詳しくは以下の記事でご説明させて頂いていますので、合わせてご参照ください。
XLOOKUPの「エラー回避」内蔵機能
先ほど、XLOOKUP関数の「#N/A」エラー回避策として、IFERROR関数を使う方法をご紹介しました。しかし実はXLOOKUP関数には「第4引数」があり、こちらに空欄を指定するとエラー表示を回避することが出来ます。
数式は以下になります。
=XLOOKUP(C2,$D$23:$D$27,$C$23:$C$27,””)
第4引数に「“”」を追加するだけです。一見単純な作業ですが、この機能があることで、数式のミスを回避することが出来るのです。つまり、XLOOKUP関数はVLOOKUPの弱点を克服しただけじゃなく、数式自体もシンプルにできる非常に優れた関数だということになるのです。
マスターが教える「検索関数を壊さないためのプロの技」
VLOOKUPの「列番号」自動化ワザ: VLOOKUPで『2列目』と数字で直接指定すると、後から間に列を挿入したときにズレてエラーになります。これを防ぐためにXLOOKUPを使うか、あるいはVLOOKUPの中で COLUMN 関数を組み合わせて使うようにするとさらにスマートです。
データ型（数値と文字列）の不一致に注意: 商品番号『101』を検索するとき、片方が『数値の101』、もう片方が『文字としての101』だと、関数はデータを見つけられずに #N/A を返します。見た目が同じなのにエラーが出る時は、表示形式が統一されているか確認しましょう。
まとめ
お疲れ様でした！3つの検索関数について、それぞれの特徴と強みが整理できたでしょうか？
最後に、実務で迷わないための「用途別の使い分けチェックシート」を用意しました。
| 探したいデータの状態 | 使うべき関数 | 特徴 |
| 縦長の表で、検索キーより右側にある | VLOOKUP | 昔ながらの定番。データが軽い。 |
| 横長の表で、検索キーより下側にある | HLOOKUP | 月別データなどに有効。 |
| キーの左側・上側にある、またはエラー対策も1発で済ませたい | XLOOKUP | 迷ったらこれ！ 全ての制限を無くした万能関数。 |
従来のVLOOKUPだけでは「データの並び順を変えなきゃいけない…」と諦めていた作業も、XLOOKUPという新しい武器を手に入れたあなたなら、元のデータを傷つけることなく、一瞬でスマートに解決できるはずです。
ぜひご自身のシートのデータの形に合わせて、最適な魔法の呪文を唱えてみてくださいね！
今回も最後までお読みいただき、ありがとうございました。
コメント