関数のなかで、もっともメジャーで面白いところが「検索」に関する関数です。関数を記述して、期待する検索値が返ってくるとちょっとした感動を味わうことができます。

そうした関数のなかでもっともメジャーで、なおかつ入門的な関数といえば、

LOOKUP（ルックアップ）関数

ではないかと思います。LOOKUPを和訳するとそのままズバリ「検索」の意味になります。つまり、そのシートの中から該当する値や文字列を検索して、その値を返す関数になります。

しかし、ただ検索するだけでは「ホーム」タブの「検索」機能とかわりません。LOOKUP関数を使うと、例えば「A」という文字を検索する場合、「A」という文字を含む「行」または「列」の他の文字や値 を返すことができます。

つまり、検索する文字や値をトリガーにして、その行や列に含まれる他の値を返してくれるというわけです。どういう用途で使えるのか思いつきにくいかもしれませんが、実は結構用途があります。そのあたりは、例題で取り組んでみましょう。

その前に、もう少しLOOKUP関数の説明をしておきます。上記のとおり「行」または「列」という表現をしましたが、そのとおりLOOKUP関数は「行」と「列」に分類されます。

HLOOKUP（エイチルックアップ）関数・VLOOKUP（ブイルックアップ）関数

の2種類です。この「H」と「V」は、「H=行」、「V=列」の違いになります。つまり、HLOOKUP関数が「行」検索でVLOOKUP関数が「列」検索となります。両者の違いは行方向、つまり「横方向」なのか列方向「縦方向」なのかの違いだけです。実際にはVLOOKUP関数のほうが使いやすく用途も多いでしょう。

次に、引数の入力は下記の表のとおりになります。（引数については前項を参照してください）「検索方法」の両者については、検索値を入力した場合に、それが完全に一致していないと値を返さないという場合と、入力した値が一致していない場合でも近似値を返すという2パターンを設定することができるということです。

その設定方法は、「論理値」という引数で設定します。「並べ替え」については、近似値を含めて値を返すように設定した場合には、「昇順」に並び替えておかないと正しく値をかえすことができないということです。

それでは、例題を使って練習してみましょう。

まずは、「HLOOKUP」のシートを表示させて、HLOOKUP関数から学習していきましょう。下図のように例題の表は、列方向に年数、行方向に個人を並べた成績表であり、簡易的なデータベースになっています。

ここでやりたいことは、任意の年数を指定したときに、該当する個人の成績を表示させることです。例えば「麻酔」選手の2009年の本塁打は何本か、「2009」という数字を指定しただけで検索させて表示させるのです。

例題の表程度の規模であれば、検索は見た方が早いくらいですが、これが数百件にものぼるデータが収集されてくると、こういう検索は役に立ちます。

下図は、「B8」セルにHLOOKUP関数が入力されています。「A8」セルに入力された「2009」に対応する値「28」を「B8」セルに返しているイメージです。

ここで疑問に思われたかもしれませんが、指定した「2009」というのは「列」方向の値です。どうして「行」方向であるHLOOKUP関数なのかというと、表の「何行目」の値を検索するか、という指定をしているからです。

上記の「麻酔」選手であれば、表全体の「2行目」なので、「B8」に記述されているHLOOKUP関数には、行数は「2」と記述されています。つまり、「2009」というユーザーが指定した列を関数が受け取って、「2009」を含む列の「2行目」を返しているというわけです。

したがって、下図のように「年数」ではなく、表の一番左上に入力されている「成績表」という文字を入力すると、その「成績表」という文字が含まれる列の「2行目」になる「麻酔　秀喜」という値が返されます。

それでは、実際にHLOOKUP関数を「B8」セルに記述してみましょう。これまでと同様に「数式バー」横の「fx（関数の挿入）」ボタンから関数を選択します。HLOOKUP関数は、分類メニューの「検索／行列」の中にあります。

HLOOKUP関数の「関数の引数」ウィンドウで、下図のとおり、「検索値」「範囲」「行番号」「検索方法」を記述し、「OK」ボタンで完了です。

上から順に、「検索値」には、関数が検索するトリガーとなる数値もしくはセルを記述します。基本的には、ユーザーが検索値を入力するセルを記述します。つまり、指定したセルに検索値を入力することで、検索を開始させるようにするわけです。ここでは「A8」セルをクリックして、「A8」を記述しましょう。「A8」セルに値を入力すると、検索が開始されるようになります。

次に「範囲」は、表を範囲指定します。「A2:H5」が表の範囲です。HLOOKUP関数では、シートの何行目なのかではなく、この範囲の中で何行目なのかを指定します。

次は、HLOOKUP関数のポイントである「行番号」です。行番号は指定した「範囲」の何行目を検索したいのか、その行数を指定するわけですから、「麻酔　秀喜」の2行目、「2」を記述します。

最後に「検索方法」です。検索方法は上表のとおり、「完全に一致する値だけを検索」する場合は、「FALSE」または「0」、「近似値を含めて検索」する場合は、「TRUE」または「0」以外または「空白」にします。

近似値を含めての検索は後述しますので、ここでは、完全に一致する値だけを検索します。したがって、「FALSE」または「0」を記述しましょう。

上図のとおり入力し、最後に「OK」ボタンをクリックすると、「B8」セルに下記の関数式「=HLOOKUP（A8,A2:H5,2,0）が記述されます。これで、「A8」セルに検索値である年数を入力すると、対応する2行目の値を「B8」セルに表示します。

ちなみに、「完全に一致する値だけを検索」するように「0」を指定していますので、一致しない検索値を入力すると、エラーとなって結果を返すことができません。

同様に、「D8」セルに「C8」セルを検索値として、「イヂロー」の「3行目」を検索する関数式、「F8」セルに「E8」セルを検索値として、「松崎　大輔」の「4行目」を検索する関数式を記述してみましょう。

違いは、「検索値」のセルと「行番号」が異なるのみです。「D8」セルの関数式は「=HLOOKUP（D8,A2:H5,3,0）、「E8」セルの関数式は「=HLOOKUP（E8,A2:H5,4,0）となります。

これで、検索値セルに適当な値を入れて関数が正しく動作するか確認してみてください。正しくできたら、次の「近似値を含めた検索」に移ります。

次の例題の「レベル一覧表」は、「国語」「数学」「社会」などの科目の得点が、どの程度のレベルなのかをあらわした表です。例えば「国語」の得点が「60点」だとすれば、「専門学校」レベルというわけです。

この表で、どのように近似値を検索するのかというと、表には「0」～「90」の区切りのよい数値しかありませんが、例えば「国語」の点数が「65点」だった場合に、近似値を検索して、該当する「専門学校」という結果を返すということです。

つまり、「完全に一致する値だけを検索」では、「0、30、50、60、70、80、90」以外の数字ではエラーになってしまいますが、「52」でも「18」でも「0.1」でも、それに該当するレベルを返すようにするのです。

設定方法は、同「関数の引数」ウィンドウで、「検索方法」に「TRUE」または「0以外の数値」または「省略」を記述するだけです。「C19」セルに「B19」セルを検索値とするHLOOKUP関数を記述してみましょう。引数の記述は下図のとおりになります。

同様に「C20」～「C23」にも記述しましょう。「範囲」を「F4」キーで絶対参照してから、下方向にドラッグしてコピーしたのち、「行番号」のみ修正すれば、簡単にそれぞれの科目について記述することができます。（絶対参照については、絶対参照と相対参照 を参照してください）

では、「B19～23」セルに適当な数字を入力してみましょう。どのような数字であっても、該当するレベルが表示されるばずです。例えば、下図のようになります。

ただし、この「近似値を含めた検索」には重要な注意事項があります。例題のとおり、

表の左列から昇順に並べておかなければならない

ということです。例題の表の一列目の「0、30、50、60、70、80、90」を入れ替えたりして昇順を崩すと、結果を返すことができなくなります。（実験してみてください）

このように、LOOKUP関数には2通りの検索方法があります。場合によって使い分けられるように、基礎をしっかり覚えておいてください。

では次に、「VLOOKUP」のシートを表示させて、VLOOKUP関数を学習していきましょう。

VLOOKUP関数も、HLOOKUP関数と考え方は同じです。ただ「タテ」と「ヨコ」が逆になったと考えればよいでしょう。実際にはVLOOKUP関数のほうが使いやすいと思います。

例題の表は、下図のとおり、列方向に項目、行方向にコード番号を並べた商品管理表です。

VLOOKUP関数は、表の「何列目」の値を検索するか、という指定をしますので、「アイテムコード」をトリガーとして、何列目の項目を表示させるか任意に選択します。例えば、下図のように「アイテム名」「製作地」「粗利益率」を表示させてみましょう。

アイテムコードの「A11」セルにコード番号を入力すると、アイテム名の「B11」セルと製作地の「C11」セル、粗利益率の「D11」セルを表示させます。したがって、関数式は、「B11・C11・D11」の3セルに記述します。

まず、「B11」セルで「fx（関数の挿入）」ボタンから関数を選択します。同様に分類メニューの「検索／行列」の中から、VLOOKUP関数を選択し、「関数の引数」ウィンドウを表示させます。

HLOOKUP関数と異なるのは「行番号」が「列番号」に変わっているだけです。「検索値」はアイテムコードの「A11」セルを指定し、範囲も同様に表部分「A2:H8」を選択します。

次に「列番号」は「アイテム名」を表示させる、つまり、「左から2列目」を表示させるわけですから、「2」を入力します。

最後に「検索方法」は、完全に一致する値だけを検索するように「0」を入力して完了です。引数の記述は下図のとおりになります。考え方としては、VLOOKUP関数のほうがわかりやすいのではないでしょうか。

では、この関数式「=VLOOKUP（A11,A2:H8,2,0）」を「C11」と「D11」にも記述しましょう。同様に絶対参照してから右方向にドラッグしてコピーしますが、この場合は、「範囲」だけではなく「検索値」も絶対参照しなければ検索値がズレてしまいますので、注意して複写します。その後「列番号」のみ修正しましょう。

列番号は、製作地の「C11」セルが「4」、粗利益率の「D11」セルが「6」になります。絶対参照して複写すると、「C11」は「=VLOOKUP（$A$11,$A$2:$H$8,4,0）」、「D11」が「=VLOOKUP（$A$11,$A$2:$H$8,6,0）」となります。

それでは、「A11」セルに適当なアイテムコードを入力してみましょう。下図のように表示されていれば問題ありません。（「D11」セルはパーセンテージ表示に表示形式を変更しています。表示形式については、セルの書式設定と表示形式 を参照してください）

次に、VLOOKUP関数の「近似値を含めた検索」です。例題の表は、下図のとおり距離数に応じた料金表になっています。

距離数を任意に指定すると、それに対応する料金が自動計算されるようにします。例えば、「83」kmと入力するだけで、「キロ当たり単価」が「95」円で「加算料金」が「2,100」円、「83×95＋2100=9,985」を算出させるわけです。

つまり、距離（km）の「A23」セルを検索値とするVLOOKUP関数を、キロ当たり単価の「C23」セル、加算料金の「E23」セルに記述します。そして、合計金額の「G23」セルに計算式を記述して数式は完成です。

ここで「B列」の「0、30、50、70、100」という数字は関数が使用する数値で、あえて表示させています。「0～29km」では「文字列」になってしまい、関数が「数値」として認識できないからです。見栄えが気になる場合は、列を選択して右クリックメニューより「非表示」にするとすっきりとした表になります。

ではまず、「C23」セルにキロ当たり単価を返すVLOOKUP関数を記述してみましょう。同様に「関数の引数」ウィンドウを表示させて、検索値に「A23」を指定します。

次に「範囲」ですが、ここでは注意が必要です。A列の文字列を範囲に含んでしまうとエラーになってしまいます。ですので、「B16:D20」を範囲に指定します。

次の「列番号」は範囲がB列からなので、左から「2列目」となり、「検索方法」は近似値を含めて検索する「TRUE」または「0以外の数値」または「省略」を記述します。引数の記述は下図のとおりになります。

次に、「E23」セルに加算料金を返すVLOOKUP関数を記述しましょう。列番号が「3」に変わるのみで、関数式は「=VLOOKUP（C23,B16:D20,3,1）」となります。

最後に、合計金額の「G23」セルに計算式「=A23*C23+E23」を記述して完成です。これで「A23」セルに正の数であればどんば数値を入力しても該当する金額が計算されて表示されます。

ただし、この「近似値を含めた検索」にもHLOOKUP関数同様に重要な注意事項があります。

表の上行から昇順に並べておかなければならない

ということです。例題の表の一行目の「0、30、50、70、100」を入れ替えたりして昇順を崩すと、結果を返すことができなくなります。（実験してみてください）

以上でLOOKUPの基礎は終了です。もう一度、下記の練習課題で復習してみましょう。課題7は、例題8に関数を記述した回答編のようなものです。数値を変えたり表をつくり変えたりして、LOOKUP関数の仕組みを復習してください。

本項の練習課題についても特に解説はありませんので、十分学習できたら次項へすすみましょう。

更新履歴

2011年9月17日

ページを公開。

2017年12月29日

ページをSSL化によりHTTPSに対応。

参考文献・ウェブサイト

当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。

文献

なし

ウェブサイト

なし

• 次ページ：「簡単な関数の挿入（3）～ IF関数 ～」へ進む
• 前ページ：「簡単な関数の挿入（1）～ ROUND関数 ～」へ戻る
• 基本操作：「メニューページ」へ戻る
• ホームへ戻る

管理人のつぶやき

Twitterのフォローお願いします！