ルックアップ関数の特徴と相違点（関数LOOKUP編）

関数LOOKUPの基本的な使い方

巷では、「ルックアップ関数を使えるようになればExcelの初心者は卒業」といった話を耳にすることもある。とはいえ、実際の現場では「ルックアップ関数が必須」という職場もあれば、「まったく使わないし、使う必要もない・・・」という職場もあるのが実情ではないだろうか？

ルックアップ系の関数は、SUMやAVERAGEのように必須の関数とはいえないが、覚えておいても損のない関数と考えられる。ただし、LOOKUP、VLOOKUP、HLOOKUP、XLOOKUPといった4つの関数があり、それぞれ使い方や仕様が異なるため、少し紛らわしく感じてしまうかもしれない。そこで、まずは最も古典的な「関数LOOKUP」の使い方と注意点について紹介していこう。

• 

  関数LOOKUPの使い方と注意点

今回は、以下の図に示した会員名簿を使って具体的な使用例を紹介していく。この名簿には、ワークシートの11行目から510行目までに計500件のデータが入力されている。

• 

  会員情報をまとめた表

ここでは「関数LOOKUP」の使用例として、ID番号を入力すると、その会員の氏名、郵便番号、住所を自動取得する機能を作成してみよう。

◆関数LOOKUPの書式
　=LOOKUP(検索値, 検索範囲, 取得範囲)

第1引数には、検索のキーとなる数値（または文字）を指定する。今回の例では、ID番号を入力する「C2セル」を指定すればよい。

続いて、第2引数に「データを検索する範囲」を指定する。今回の例ではA列の11行目～510行目にID番号が入力されているので、そのセル範囲となる「A11:A510」を指定すればよい。

最後に、「取得するデータが入力されている範囲」を第3引数に指定する。たとえば「氏名」のデータを取得するときは、そのセル範囲となる「B11:B510」を指定すればよい。

• 

  「氏名」を取得する関数LOOKUPの入力

以上で、関数LOOKUPの入力は完了。「Enter」キーを押すと該当するデータが自動取得される。今回の例では、ID番号が「12」の会員の「氏名」が自動取得されることになる。

• 

  関数LOOKUPにより取得された「氏名」

同様の手順で「郵便番号」などのデータを取得することも可能だ。この場合は、「郵便番号」が入力されている「E11:E510」を第3引数に指定すればよい。

• 

  「郵便番号」を取得する関数LOOKUPの入力

以降の作業も同様で、「住所1」を取得するときは「F11:F510」、「住所2」を取得するときは「G11:G510」を第3引数に指定すればよい。これで、入力したID番号に対応する氏名、郵便番号、住所を自動取得できるようになる。

• 

  関数LOOKUPにより取得されたデータ

このように関数LOOKUPを使うと、入力した数値（または文字）を検索して、そのデータと同じ行にある「別のデータ」を自動取得することが可能となる。ただし、いくつか注意すべき点があり、その中には致命的な弱点となる制約もある。順番に解説していこう。

取得するデータが「空白セル」の場合

まずは、データの取得先セルが「空白セル」であった場合だ。この場合は、数値の0（ゼロ）が取得される仕様になっている。たとえば、ID番号に「13」と入力すると、「住所2」の取得データには「0」という数値が表示される。

• 

  取得したデータが「空白」の場合

この程度の問題であれば見過ごすことも不可能ではないが、もっと深刻な問題が生じてしまうケースもある。その代表例が「文字」で検索してデータを取得する場合だ。

文字で検索する場合

今度は、ID番号ではなく「氏名カナ」で検索して会員データを取得するケースを考えてみよう。

このケースにおける「関数LOOKUP」の記述は以下の図のようになる。第1引数に「氏名カナ」を入力するC3セル、第2引数に「氏名カナ」のデータが入力されているセル範囲（C11:C510）、第3引数に「取得データ」が入力されているセル範囲（E11:E510）を指定すればよい。

• 

  「氏名カナ」で検索する関数LOOKUPの入力

同様の手順で「住所1」と「住所2」を自動取得する関数LOOKUPを入力すると、以下の図のような結果が得られる。

• 

  関数LOOKUPにより取得されたデータ

上図をよく見ると、間違ったデータが取得されていることに気付くと思う。「イワイ ケイコ」さんの郵便番号、住所を調べようとしているのに、実際に取得されたデータは見当外れの間違ったデータになっている。

このような結果になってしまうのは、関数LOOKUPの使用ルールを無視していることに原因がある。というのも、関数LOOKUPを使用するときは「あらかじめデータを昇順に並べ替えておく」というルールがあるからだ。

最初に示した例では「ID番号」が1、2、3、・・・と「昇順」に並んでいたため、正しいデータを取得することができた。一方、今回の例はそうなっていない。「氏名カナ」の並び順に規則性はなくランダムだ。このような場合、関数LOOKUPは間違ったデータを取得してしまう。

関数LOOKUPを正しく使うには、以下の図のように「検索する列」を基準にデータを並べ替えておく必要がある。今回の例の場合、「氏名カナ」の昇順にデータを並べ替えると、「正しいデータ」を取得できるようになる。

• 

  「氏名カナ」の昇順にデータを並べ替えた場合

とはいうものの、「いちいち表を並べ替えるのではなく、最初の状態のままデータを自動取得したい」というのが一般的な感想ではないだろうか？　これが「関数LOOKUPは使いづらい」と言われる理由の一つといえる。

検索値が見つからない場合は？

ほかにも、関数LOOKUPにはいくつかの弱点がある。そのひとつが「部分一致」で検索できないことだ。状況がわかりやすくなるように、漢字表記の「氏名」を取得する関数LOOKUPを追加した例で解説していこう。

• 

  「氏名」を自動取得する関数LOOKUP

たとえば、名前の一部が不明のため「イワイ」のキーワードだけで検索した場合を検証してみよう。この場合、「イワイ」（岩井）さんのデータではなく、「イモト ノゾミ」（井本 望美）さんのデータが取得されてしまう。

• 

  「氏名カナ」に最初の数文字だけ入力した場合

Excelに慣れている方なら「ワイルドカード（*）を使えばよいのでは・・・」と考えるかもしれない。

そこで「イワイ*」と入力した場合の例も紹介しておこう。この場合は、問題なく「イワイ ケイコ」（岩井 慶子）さんのデータが取得される。

• 

  ワイルドカードの使用を試みた場合（1）

一見するとワイルドカードが正しく機能しているように見えるが、実はそうではない。この結果は「たまたま上手くいった」というだけの話でしかない。

その証拠として「イワ*」のキーワードで検索した例も紹介しておこう。この場合、「イワ」の文字をまったく含まない「イモト ノゾミ」（井本 望美）さんのデータが取得されてしまう。

• 

  ワイルドカードの使用を試みた場合（2）

このような結果になる理由を探るには、関数LOOKUPが「どのようにデータを検索してるか？」を学んでおく必要がある。少しややこしい話になるが、順番に解説していこう。

検索値に指定したデータが見つからなかった場合は、検索値に「最も近い値」が近似一致としてヒットする仕組みになっている。ここでいう「最も近い値」とは、検索値より小さい範囲で「最も近い値」となる。データを「昇順」に並べ替えているのであれば、「検索値の直前にあるデータ」が近似一致として扱われる。

このことは、「ID番号」で検索する例に話を戻して検証してみると理解しやすい。たとえば、検索値に「9.3」と入力すると、ID番号が「9」のデータが取得される。これは素直に「最も近いデータ」と考えられるだろう。

• 

  検索値「9.3」でデータを取得した場合

続いては、検索値に「9.8」と入力した場合だ。この場合もID番号が「9」のデータが取得される。普通に考えると「9.8」に最も近い整数は「10」になるが、「10」は検索値（9.8）より大きな値になるため、その直前にある「9」が近似一致として扱われる。

• 

  検索値「9.8」でデータを取得した場合

検索値が「文字」の場合は、同様の処理が「文字コード」に対して行われる。先ほど示した「*」についても補足しておこう。

試しに、関連しそうな「氏名カナ」と「検索値」をワークシートに入力して「昇順」に並べ替えると、以下の図のような結果が得られた。なお、背景が黄色い部分は「実在するデータ」を示している。

• 

  関連するキーワードを「昇順」に並べ替えた結果

この結果を見ると、「イワイ*」の文字コードは「イワイ ケイコ」より大きな値になることがわかる。

よって、その直前にある「イワイ ケイコ」のデータが自動取得される。一方、「イワ*」や「イワイ」の文字コードは「イワイ ケイコ」より小さな値になる。よって、その直前にある「イモト ノゾミ」のデータが取得されてしまうことになる。

要するに「*」はワイルドカードとして機能している訳ではなく、単なる文字として扱われていると考えられる。

キーワードに漢字を含める場合は、さらに話が複雑になる。漢字の文字コードは50音順にならないため、まったく見当はずれのデータが取得される可能性もある。

なお、ここまで小難しい話をしてきたが、最終的に覚えおきたいポイントは「一致なしの場合であってもエラーが発生しない」ということだ。つまり、適当なキーワードで検索したり、キーワードに入力ミスがあったりすると、間違ったデータが取得されることになる。そのことに気付かずに作業を進めてしまい、重大なミスに発展する危険性もあるだろう。

このように関数LOOKUPには、実務で使用するときに問題を引き起こしそうな弱点がいくつか見受けられる。まとめると、以下のようなポイントが弱点になると考えられる。

◆関数LOOKUPの弱点
　・あらかじめデータを「昇順」に並べ替えておく必要がある
　・検索値が見つからなかった場合は、近似一致でデータが自動取得される
　　（間違ったデータを取得してしまう危険性がある）
　・ワイルドカードを使った「部分一致」の検索には対応していない

このような理由から「関数LOOKUP」が使用される機会は少なく、ルックアップといえば「関数VLOOKUP」という風潮が強くなっている。ということで、次回は「関数VLOOKUP」と「関数HLOOKUP」の使い方について詳しく紹介していこう。