【超図解】VLOOKUPの使い方|基礎から応用まで完璧マスター&スゴ技20選

【超図解】VLOOKUPの使い方|基礎から応用まで完璧マスター&スゴ技20選
  • VLOOKUPの使い方を基礎から学びたい
  • すぐ使える応用技を知りたい
  •  エラーを解決したい

VLOOKUPは、膨大なデータから簡単に必要な情報を取り出せる関数です。オフィスや学校など、様々なシーンで活躍しています。

エクセルの中でも、VLOOKUPは仕事の効率化・時短につながる最も強力な関数といえるでしょう。その反面、注意点が多く、失敗しやすい関数でもあります。

そこで、この記事では次のことを解説します。

この記事でわかること

  1. VLOOKUPで何ができるか
  2. VLOOKUPの基本的な使い方
  3. エラー解決方法
  4. 便利技・応用技20選
  5. 困った時にチェックすべき項目

たくさんの図解でわかりやすく解説していますので、初心者の方も具体的なイメージがわき、スムーズに理解できます

他では見ない便利技・応用技も多数紹介していますので、習熟されている方も、新しい発見が得られることでしょう

幅広く網羅的に解説していますので、この記事を通してVLOOKUPをぜひマスターしてください。

20年間毎日10時間以上エクセルを使い続けてきた筆者が、学んだこと、失敗したこと、指導してきたことをもとに、読者の不安や悩みを解決します。

エクセルに関するお困りごとの、解決の一助になれば幸いです。

1. VLOOKUP(ブイルックアップ)とは

VLOOKUPのイメージ図

VLOOKUP(ブイルックアップ)とは、指定した範囲から特定の値を探し出し、それに紐づくデータを返す関数です。

最低限の理解のために、基本用語として「検索値」と「検索範囲」の2つをおさえておきましょう。

データを探す手掛かりとなる値が「検索値」です。重複せず一つに特定できなければなりません。
例えば、商品コード出席番号などが使われます。

データを探しに行く場所が「検索範囲」です。の形をしていて、一番左の列に検索値が縦に並んでいます。
例えば、商品一覧表生徒名簿などがあてはまります。

つまりVLOOKUPは、「検索値」を手掛かりに、「検索範囲」の一番左の列を縦に探していき、一致するものを見つけたら、それに紐づくデータを返してくれるわけです。

VLOOKUPは、次のことを得意としています。

VLOOKUPの得意なこと

  • 検索範囲が何万行あっても、簡単に検索できる(max100万行!)
  • 検索範囲の中身を更新したら、検索結果もリアルタイムに更新される
  • 検索範囲は、同じパソコン内だけでなく、ネットワーク上の共有ファイルでも可能
  • 完全一致だけでなく、部分一致や近似値も検索できる

一方、次のような苦手な一面もあります。

VLOOKUPの苦手なこと

  • 検索結果は一つしか返せない・・・複数見つけても1つ返したらおしまい
  • 集計ができない・・・検索後の演算・集計は他の関数に助けてもらう必要あり
  • 表を横方向へは検索できない・・・検索範囲となる表には様々な制限あり

これ以外にも不得意な部分はありますが、他の関数と組み合わせることで解決できます。
詳しくは、「6. VLOOKUPをもっと使いこなしたい!|便利技・応用技20選」を参照ください。

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート~VLOOKUP関数

では、次章より詳しく解説していきます。

2. VLOOKUPで何ができるの?|具体例5選

この章では、VLOOKUPで何ができるかをイメージしてもらえるよう、具体例を5つご紹介します。あなたがVLOOKUPでやりたいことがあれば、ぜひ参考にしてください。

VLOOKUPが活躍できる具体例5選

  •  商品一覧表から単価を返す
  •  取引先一覧表から担当者と企業URLを返す
  •  生徒名簿・点数表から名前と科目別点数を返す
  •  社員名簿から連絡先とアドレスを返す
  •  テストの点数に応じた判定結果を返す

いずれもオフィスや学校にて、VLOOKUPがすぐに活躍できるシチュエーションです。

2-1. 商品一覧表から単価を返す

VLOOKUP 商品一覧表から単価を返す

VLOOKUPは、多数の商品を管理する表において、特定の商品情報を調べるのに便利です。

例えば上の図では、商品一覧表から、商品コードを手掛かりに該当する商品を見つけ出し、それに紐づく単価を返しています。

項目内容具体例
検索商品コード1005
検索範囲商品一覧表A~D列の範囲
欲しい情報4列目にある単価1,500

手掛かりとなるのは商品コードの「1005」です。商品一覧表からその商品を見つけたら、その4列目にある単価「1,500」を返しています。

商品コードさえ特定できれば、たとえ何万点の商品があっても欲しいデータを簡単に取り出すことができます。

エクセルで商品マスタを作成する方法はこちら
≫ エクセルの商品マスタ作成を自動転記で入力効率化&検索100万件でも楽々

2-2. 取引先一覧表から担当者と企業URLを返す

VLOOKUP 取引先一覧表から担当者と企業URLを返す

VLOOKUPは、取引先を管理する表において、特定の取引先に関する様々情報を調べるのに便利です。

例えば上の図では、取引先一覧表から、取引先コードを手掛かりに該当する取引先を見つけ出し、それに紐づく担当者企業URLを返しています。

項目内容具体例
検索値取引先コード1003
検索範囲取引先一覧表A~D列での範囲
欲しい情報13列目にある担当者D氏
欲しい情報24列目にある企業URLhttps://D-sample.com/

手掛かりとなるのは取引先コードの「1003」です。取引先一覧表からその取引先を見つけたら、その3列目にある担当者「D氏」と、4列目の企業URL「https://D-sample.com/」を返しています。

取引先コードさえ特定できれば、企業名が複雑だったり(株)が前でも後ろでも係なく簡単に欲しいデータを取り出すことができます。

2-3. 生徒名簿・点数表から名前と点数を返す

VLOOKUP 生徒名簿から名前と点数を返す

VLOOKUPは、学校や塾などで、個々の生徒に関する様々情報を管理するのに便利です。

例えば上の図では、生徒名簿・点数表から、出席番号を手掛かりに該当する生徒を見つけ出し、生徒名各科目の点数を返しています。

項目内容具体例
検索値出席番号6
検索範囲生徒名簿・点数表セルA2~F27で囲まれた範囲
欲しい情報12列目にある生徒名F君
欲しい情報23~6列目にある国語、算数、理科、社会の点数65、100、90、80

出席番号「6」を頼りに、生徒名簿・点数表からその生徒を探し出し、生徒名「F君」と国語、算数、理科、社会の点数「65、10、90、80」点を返しています。

出席番号がはっきりすれば、名前の漢字が読みにくかったりテストの科目がたくさんあったりしても、間違えることなく点数を確認できます。

エクセルで成績管理をしたい方はこちら
≫ エクセルで成績管理|点数を自動で転記・集計・判定まで~VLOOKUP

2-4. 社員名簿から連絡先とアドレスを返す

VLOOKUP 社員名簿から連絡先とアドレスを返す

VLOOKUPは、職場連絡網を作りたいときにも便利です。

例えば上の図では、社員名簿から、社員コードを手掛かりに該当する社員見つけ出し、それに紐づく連絡先メールアドレスを返しています。

項目内容具体例
検索値社員コード2005
検索範囲社員名簿A~D列で囲まれた範囲
欲しい情報13列目にある連絡先080-1234-0005
欲しい情報24列目にあるメールアドレスe@sample.ne.jp

社員コード「2005」を頼りに、社員名簿からその社員を探し出し、その社員の連絡先電話番号「080-1234-0005」とメールアドレス「e@sample.ne.jp」を返しています。

メールアドレスなどは転記ミスが起きやすいところですが、VLOOKUPであれば確実に必要な情報を取り出すことができます。

エクセルで社員名簿を作りたい方はこちら
≫ エクセルの社員名簿作成を自動入力で効率化|検索&転記もVLOOKUPで一発処理

2-5. テストの点数に応じた判定結果を返す

VLOOKUP 判定基準表から判定結果を返す

VLOOKUPは、近似値検索もできるので、一定の範囲ごとにランク付けするのも簡単です。

例えば上の図では、テストの点数に応じて、判定基準表に照らし合わせた判定結果を返しています。

項目内容具体例
検索値点数78
検索範囲判定基準表セルA2~B6で囲まれた範囲
欲しい情報2列目にある判定結果

「78」点を判定基準表に照らし合わせ、「78」点を超えない最も近い値である「70」点を探し出し、その判定結果である「良」を返しています。

このようにVLOOKUPは、検索値が完全一致する場合だけでなく、近似値を見つけてそれに紐づくデータを返すこともできます。

もしこれを、IFを使って同じ処理をしようとすると、とても長い式になります。
詳しくは「TRUE検索が有効なケース:IFと比較すれば便利さ実感」を参照ください。

点数の自動判定について詳しくはこちら
≫ エクセルで成績管理|点数を自動判定する方法

3. VLOOKUPはどうやって使うの?|各項目の解説

VLOOKUPの各項目の説明

この章では、VLOOKUPの各項目を解説します。数式は、次の4つの項目で構成されます。

=VLOOKUP(● , ■:■ , ▲ , ◆)

●:検索値・・・検索したい値(検索値)がある場所を指定する
■:■:範囲・・・検索範囲を指定する
▲:列番号・・・検索範囲の左から何列目を返すかを指定する
◆:検索方法・・・完全一致か近似値検索かを指定する

各項目を正しく理解することが、VLOOKUPのマスターへの第一歩です。では、各項目を一つずつ解説します。

3-1. =VLOOKUP(●,■:■,▲,◆) ●:検索値がある場所を指定

VLOOKUPの1つ目の項目の説明

1つ目の項目では、検索値がある場所を指定します。例えば、商品コード出席番号などを入力する場所です。

具体的には、「A1」などのセルの番地(アドレス)を指定します。数式で指定することもできます。

指定する場所は、必ずしも同じシートでなくても構いません。別シート別ファイル(別ブック)、あるいは社内ネットワーク上の共有ファイルにあるセルも指定可能です。

3-2. =VLOOKUP(●,■:■,▲,◆) ■:■:検索範囲を指定

VLOOKUPの2つ目の項目の説明

2つ目の項目では、検索したいデータがある検索範囲の場所を指定します。
例えば、商品一覧表生徒名簿などがある場所です。

検索範囲の場所は同じシートでなくても構いません。別シート別ファイル(別ブック)、あるいは社内ネットワーク上の共有ファイルにある表も指定可能です。

別シートや別ファイルを指定する場合は記号に注意

別シートや別ファイルを指定する場合、下記の記号を用いて表現します。これらは非常に見づらく、見落としやすいので注意しましょう。

別シートを指定する場合は、シート名の後に「」を付けます。

(例)【別シートの「商品管理シート」にある検索範囲を指定する場合】

=VLOOKUP(A1,商品管理シート!A:E,2,FALSE)

別ファイルにあるシートを指定する場合は、ファイル名を「 [ ] 」で囲みます。

(例)【別ファイルの「商品管理ファイル」にある検索範囲を指定する場合】

=VLOOKUP(A1,[商品管理ファイル.xlsx]商品一覧表!A:E,2,FALSE)

社内ネットワーク上の別ファイルのシートを指定する場合は、フォルダやドライブ名を「」で囲みます。

(例)【社内ネットワーク上の「購買部」フォルダにある「商品管理ファイル」の検索範囲を指定する場合】

=VLOOKUP(A1,‘X:\購買部\[商品管理ファイル.xlsx]商品一覧表’!A:E,2,FALSE)

社内ネットワーク上のファイルを指定する場合の注意点

社内ネットワーク上の別ファイルを指定している場合、そのファイルを開いている間はドライブ名が表示されません。閉じたときにはじめてドライブ名が表示されます。

具体的には、ファイルを閉じたときに、下記のように式が勝手に変化します。

【ファイルを開いているとき】
=VLOOKUP(A1,[商品管理ファイル.xlsx]商品一覧表!A:E,2,FALSE)

【ファイルを閉じたとき】
=VLOOKUP(A1,‘X:\購買部\[商品管理ファイル.xlsx]商品一覧表’!A:E,2,FALSE)

数式を見直す際は、両方の状態でチェックしましょう。

検索範囲の指定方法には「セル指定」と「列指定」がある

VLOOKUPのセル指定と列指定の違い

検索範囲の指定方法には、セルを指定する方法と、列を指定する方法があります。

セルで指定する場合は、「A3:D6」のように左上の番地(アドレス)右下の番地(アドレス)を「」(コロン)で挟みます。
「A3」と「D6」の2つのセルを対角線とする長方形の部分が指定範囲となります。

一方、列で指定する場合は、「A:D」のように列番地だけを「」で挟みます。
指定された列の最下行まで範囲に含まれます。

セル指定と列指定の違いと使い分け方

それぞれ特徴がありますので、目的に合わせて使い分けましょう。

【セル指定(A3:D6)の特徴】
・検索範囲の上や下を自由に使える
・行数が少なければ(概ね100以下)、全体を把握しやすく、使い勝手が良い

【注意点】
・行を追加したときに数式の範囲指定も同時にメンテしないと、検索から漏れてしまう

【セル指定が適しているケース】
・検索範囲の行数が少なく(100行程度まで)、行追加がほとんどないケース
・検索範囲と数式を同じシート内に配置して一覧したいケース

【列指定(A:D)の特徴】
・検索範囲に行を追加しても数式のメンテが不要
・列を指定するだけなので、セル指定より数式がシンプル

【注意点】
・範囲指定した列は、他の用途には使えない
・パソコンに負荷がかかる(最下行まで検索するため)

【列指定が適しているケース】
・検索範囲の行数が多いケース(目安は100行以上)
・検索範囲の行を頻繁に追加するケース

(推奨)範囲指定は「絶対参照(「$」をつける)」にしたほうが良い

VLOOKUPの絶対参照と相対参照の違い

検索範囲の指定は、絶対参照にした方が良いです。

なぜなら、相対参照の場合、数式をコピーする際に指定した範囲もずれていくからです。

例えば、数式を1つ右下に移動/コピーすると、範囲指定が下記のようにずれてしまいます。

【相対参照(「$」がない)の場合の移動/コピーの結果】

前:=VLOOKUP(●, A1:E6 ,▲,◆)

後:=VLOOKUP(●, B2:F7 ,▲,◆)

そこで、番地(アドレス)の手前に「$」をつけて「絶対参照」にします。

【絶対参照(「$」がある)の場合の移動/コピーの結果】

前:=VLOOKUP(●,$A$1:$E$6,▲,◆)

後:=VLOOKUP(●,$A$1:$E$6,▲,◆)

こうすれば数式を移動/コピーしても範囲指定はずれません。

特に注意すべきなのは、縦方向だけずれた場合です。なぜなら、検索値のある列は変化がないので、エラーにならず事態に気付きにくいからです。

この点、横方向にずれると、すべてエラーになって事態にすぐに気付くことができます。

3-3. =VLOOKUP(●,■:■,▲,◆) ▲:何列目を返すかを指定

VLOOKUPの3つ目の項目の説明

3つ目の項目では、検索範囲の何列目を返すかを指定します。数え方は、検索値のある列も含めて、左から右に向けてカウントします。

1以上数字を指定します。1より小さい数や、マイナスはエラーになります。列の幅より大きい数もエラーになります。

VLOOKUPの列番号の指定の注意点

列番号は数値を直接入力する以外に、値の入っているセルを指定することもできます。
枠外にあるセルを指定する方法を詳しく知りたい方はこちら

計算式での指定もOKです。さらに応用技として、MATCHやCOLUMNと組み合わせると便利です。
MATCHで列番号を指定する方法について詳しく知りたい方はこちら
COLUMNで列番号を指定する方法について詳しく知りたい方はこちら

3-4. =VLOOKUP(●,■:■,▲,◆) ◆:検索方法を指定

VLOOKUPの4つ目の項目の注意点

FALSE

  • 完全に一致すればデータを返す
  • 無ければ「#N/A」エラーを表示する
  • FALSEの代わりに「0」と記述してもよい

TRUE

  • 一致するものが無ければ、それを超えない最も近い値(近似値)を探す
  • TRUEの代わりに「1」と記述してもよい
  • 検索方法の指定を省略した場合、TRUEを指定したことになる

4つ目の項目では、検索方法を指定します。検索方法は、次の2種類です。

VLOOKUPのFALSEとTRUEの違い

(推奨)どちらにするか迷ったら、ひとまず「FALSE」を選ぶ

「FALSE」「TRUE」のどちらを選べばよいかわからない場合、ひとまず「FALSE」を選んで先に進めましょう。

ひとまず「FALSE」を選ぶ理由

  • 「FALSE」は完全一致の場合だけ値を返すので、エラーになりやすく、計算式の間違いをチェックしやすい
  • 「TRUE」は近似値検索なので、意図しない結果であってもエラーになりにくく、計算式のミスに気付きにくい

他の項目の設定が完了し、数式に間違いないことが確認できてから、「FALSE」か「TRUE」かを選ぶ方が、ミスを減らすことができます。

(推奨)「FALSE」「TRUE」は、英字ではっきり記述する

「FALSE」「TRUE」の指定は、「0」「1」や省略ではなく、英字ではっきり記述しましょう。なぜなら、数字で指定したり省略したりすると、誤入力したときに気付きにくいからです。

確かに「0」「1」で書く方が簡単です。「TRUE」なら省略もできます。式も短くなるので、選びたくなることでしょう。

しかし、両者は混同しやすく、一見しただけでは間違いに気付きにくいです。はっきりと「FALSE」「TRUE」と記述することをおすすめします。

筆者は20年間、VLOOKUPを使い続けてきましたが、いまだに「0」と「1」で、どっちがどっちだったか、わからなくなるときがあります。

まして、省略した場合などとうてい覚えきれません

あいまいな記憶や思い込みは、事故のもとです。

いつも「FALSE」「TRUE」とはっきり記述する方が安心なのです。

TRUE検索が有効なケース:IFと比較すれば便利さ実感

IFとVLOOKUPのTRUE検索との比較

TRUE検索は、IFと比較すればその便利さが実感できます。

例えば上図のように、点数ごとに判定基準が設けられているとします。IFでは、点数の分けが増えるほど式が長くなってしまいます。一方、VLOOKUPのTRUE検索では、検索範囲のメンテだけで対応できます。

「近似値」での検索は、大小関係にも注意が必要です。

例えば、検索したい値が78の場合、検索範囲に70と80があれば近似値は70となります。一見すると80の方が近いですが、78を超えてしまっているため、近似値とはなりません。

近似値検索は、この意味合いを理解した上で使いましょう。

4. 検索範囲となる表にもルールが必要|失敗しない表の作り方

この章では、検索範囲となる表の作り方を解説します。最初に方針を決めてから丁寧に作成すれば、後々のエラーをグッと減らすことができます。

検索範囲となる表の作成手順は次の通りです。

検索範囲となる表の作成手順

STEP.1 作成場所を決める

STEP.2 検索値を決める

STEP.3 ヘッダーを決める

STEP.4 書式を決める

STEP.5 データを作成する

4-1. 検索範囲となる表の作成上の基本ルール

検索範囲となる表には、守るべきルールがいくつかあります。

検索範囲となる表の作成上の基本ルール

  • 一行ごとに一組のデータであること
  • 検索値は一番左の列にあること
  • 検索値は重複しないこと
  • 検索値は昇順に並べること
  • 結合セルは使用しないこと

検索値を昇順に並べることについては、FALSE検索の場合は必須条件ではありません。
しかし、昇順に並べておく方が、検索値の重複に気付きやすく、事故を減らすことができます。

4-2. 【STEP1】作成場所を決める

検索範囲となる表を作る場所は、必ずしもVLOOKUPの数式と同じシートにする必要はありません。

検索範囲となる表を作ることが可能な場所

  1. VLOOKUPの数式のあるセルと同じシート
  2. 同じファイル内の別シート
  3. 同じパソコンにある別ファイル
  4. 社内のネットワークにある共有ファイル

それぞれにメリット、デメリットがあります。

(1)検索範囲となる表を数式と同じシートに作成する|シンプルでチェックがしやすい

検索範囲となる表を数式と同じシートにすると、両方を同時に見ることができます。そのため、一覧性が高くあり、計算結果がチェックしやすいです。

VLOOKUPに慣れてない方や、1枚のシートで小さくまとめたい方におすすめです。

ただし、下記の注意点があります。

検索範囲となる表を数式と同じシートにする場合の注意点

  • 検索範囲以外の部分をメンテしたときに、表を崩してしまう(空白行ができたり、行を消してしまったりする)
  • 検索範囲以外の部分をメンテしたことで、検索範囲の番地(アドレス)が変わってしまい、VLOOKUPの数式に影響を与える(絶対参照にしていても安心できない)
  • 検索範囲の行数を増やすときに、他の部分のレイアウトにまで影響する

(2) 検索範囲となる表を同じファイル内の別シートに作成する|取り扱いがしやすい

検索範囲となる表を同じファイル内の別シートに分けることができます。検索範囲専用のシートなので、そのことだけを考えて自由にレイアウトできます。

一つのファイルで完結するので、管理がしやすいです。

(3) 検索範囲となる表を同じパソコン内の別ファイルに作成する|兼用しやすい

検索範囲となる表を同じパソコン内にある別ファイルにすることができます。様々なファイルで表を兼用できるので便利です。

もし同じ内容の表をファイルごとに作成した場合、同期をとるのが大変です。
データに変更があった場合に、それぞれの表をメンテする必要があるからです。

これを一つのファイルで兼用すれば、その表をメンテするだけで済みます。

ファイルを「読み取り専用」で開いていても、その時点でのデータを参照することができます。

ただし、下記のような失敗事例があるので注意しましょう。

(失敗事例1) ファイルの移動/コピーでリンクが切れてしまう
数式のあるファイルと検索範囲のあるファイルを別々に移動/コピーすると、リンクが切れてしまうおそれがあります。
動かすときは、両方セットで処理しましょう。
やむを得ず別々に処理する場合は、事後に数式を確認しましょう。
(失敗事例2) 「名前を付けて保存」すると、リンク先まで切り替わってしまう
VLOOKUPの数式のあるファイルを開いたまま、検索範囲となる表をバックアップするために「名前を付けて保存」すると、VLOOKUPの参照先がバックアップの方に切り替わります。
それに気づかずに、いつものショートカットから本来の表をメンテしても、リンクが切れているので検索結果に反映されません。
ファイルを「名前を付けて保存」する場合は、VLOOKUPのあるファイルを閉じてからおこないましょう。
(失敗事例3) 「book1」状態で数式を組んだあと保存し忘れる
ファイルを新規作成して検索範囲となる表をつくる場合、「book1」状態のままで数式を組むのはやめましょう。(※「book1」は、エクセルファイルを新規で開いた際の仮ファイルです)
確かに、「book1」のままVLOOKUPの検索範囲として選んでも、式は作れます。
検索結果も問題なく表示されます。
しかし、保存せずにうっかり閉じてしまうと、次回再現できなくなります
ここでやっかいなのは、閉じた直後は検索結果が消えないため、事態にすぐに気づかないことです。次回そのファイルを開く際に「リンク先が見つかりません」というアラームが出て、初めて事態に気づきます。
でも、時すでに遅しです。。。
筆者は、この失敗で何度も涙を飲んでいます。
ファイルは小まめに保存しましょう。

(4) 検索範囲となる表を社内ネットワーク上に作成する|情報共有・作業効率化がしやすい

検索範囲となる表を、社内のネットワーク上に作ることができます。

ネットワーク上に検索範囲となる表があるので、他のパソコンから参照でき、情報共有することができます。別々のパソコンでメンテできるので、作業を分担でき、効率化をはかることができます。

ただし、次の点に注意しましょう。

ネットワーク上のファイルを検索範囲とする場合の注意点

検索範囲のあるファイルを開かずに数式のあるファイルだけ開くと、前回保存したときの検索結果が表示されてしまいます。

しかし、それは最新の検索結果ではありません。ですが、エラーが出ていないと、そのまま信じてしまうおそれがあります。

ネットワークを介したファイル共有は、こういったリスクも理解しておきましょう。

4-3. 【STEP2】検索値を決める

次に「検索値」を決めます。

検索値は、データを探し出す手がかりとなるものですから、できるだけ重複しないものを選びましょう。商品コードや出席番号など、これさえわかれば商品や生徒が特定できるものが適しています。

文字、数字、記号などが使用可能です。
ただし、全角と半角は区別されるので、どちらにするか決めておきましょう。
見た目では全角と半角の区別がつかない場合もあるので、注意が必要です。

日本語の漢字や仮名を検索値に使う場合の注意点

日本語の漢字や仮名を検索値に使う場合は、注意が必要です。

特に「漢字」は、幾通りも読み方があり、似た字があるので気をつけましょう。

読み方 漢字
わたべ、わたなべ 渡辺、渡部、渡邊、渡邉
いけうち、いけのうち 池内、池ノ内、池之内、池の内

入力変換の際に思い込みで選んでしまうと、不一致を起こしてしまいます。

4-4. 【STEP3】ヘッダー(見出し)を決める

VLOOKUP 検索範囲の列の区切り方の違い

次にヘッダー(検索範囲となる表の1行目の見出し)を決めます。

1番左の列は、STEP2で決めた検索値を配置します。これは必須です。

2列目以降は、自由です。項目をどの程度区切るかは、用途に応じて決めましょう。

【例】住所を管理する表を作る場合のヘッダーの区切り方

封筒に印刷するだけであれば、1つにまとめると便利です。VLOOKUPの式が1つで済むからです。

検索結果を様々な用途に使うのであれば、細かく区切りましょう。都道府県別や市町村別など様々な分析ができるからです。

このように、検索範囲となる表のヘッダーは、検索結果をどのように利用するかで決めましょう。

4-5. 【STEP4】書式を決める

検索範囲となる表の形が決まったら、各列のデータの書式を決めます。検索値の書式だけでなく、それ以外の書式も可能な限り決めておきましょう。

そして項目ごとに、書式は統一しておきましょう。なぜなら、見た目が同じでも書式が異なれば別物になるからです。

例えば数値の「123」と、文字列の「123」は別物です。
VLOOKUPで探しても、不一致になり見つけられません。
近似値検索でもひっかかりません。

検索値に数字を用いる場合は、「数値」か「文字列」かどちらかに統一しておきましょう。

そして具体的にデータを作成する前に、先に書式設定を済ませておきましょう。

書式設定を後回しにした場合の注意点

書式設定を後回しにした場合に注意すべきことがあります。

エクセルには入力した文字に合わせて書式を勝手に判断する「おせっかい機能」があるからです。

例えば、書式設定が異なるセルに「0123」を入力すると、下記になります。

書式設定 「0123」の入力結果 コメント
未設定 123 数値と判断されてしまい、先頭の0は無視される
「数値」に設定 123 数値として扱うので、先頭の0は無視
「文字列」に設定 0123 入力通りに表示

書式設定せずに入力した場合、すべて数字だと勝手に「数値」と判断され、先頭の「0」は無視され、「123」と表示されてしまいます。

さらに面倒なことに、後から書式を「文字列」に変更しても、「0」は復活しません。「0123」にするには、あらためて手入力する必要があります。

書式設定のおせっかい機能

エクセルの「おせっかい機能」は、他にもあります。

2分の1」のつもりで「1/2」を入力した場合の表示結果が下記です。

書式設定 「1/2」の入力結果 コメント
未設定 1月2日 日付と判断されてしまう
「数値」に設定 1 「2分の1」と判断され、小数点以下の表示設定をしていないため四捨五入されてしまう
「文字列」に設定 1/2 入力通りに表示

他にも、引き算章の見出しのつもりで「1-2」を入力した場合も下記のようになります。

書式設定 「1-2」の入力結果 コメント
未設定 1月2日 日付と判断されてしまう
「数値」に設定 44198 日付と判断された上に、表示設定をしていないので、2021/1/2のシリアル値が表示されてしまう
「文字列」に設定 1-2 入力通りに表示

4-6. 【STEP5】データを作成する

検索範囲となる表のフォーマットが完成したら、具体的なデータを作成していきます。
他のファイルからコピーする場合は、そちらの書式設定を引きずらないよう、「値貼り付け」にするのが無難です。

複数のエクセルファイルをつなぎ合わせる場合も、注意が必要です。
なぜなら、それぞれの書式が異なることも十分にあり得るからです。

(失敗事例) 他人が作った表の扱い
他人が作った表を、書式を意識せずにつないでいった場合、部分的に書式が異なる表ができあがります。その全体をチェックせずに数式を組んでしまうのは、ケガのもとです。
なぜなら、チェックした部分だけは正しく検索できてしまうため、間違いに気付きにくいからです。
一つの表で「書式が部分的に異なっている」という状態が、エラーの原因追及に大変手間のかかる事態の一つなのです。

どういったデータを使うにせよ、現状の書式設定にとらわれない方が良いです。最終的にどのような書式にするのが理想的かを考え、それにあわせてすべてを一から整えていく方が、時間がかかるようで実は最も早道です。

慌てず正確さを優先するよう心がけましょう。

アクセス(※Access)のデータを貼り付ける場合の注意点

アクセス(※Access)のデータを貼り付ける場合も、要注意です。(※マイクロソフトのデータベース用ソフトです。)
アクセス内の独自の書式設定を引っ張ってくる場合があるからです。

アクセスの画面から直接コピー&ペーストした場合でも、データをエクスポートして貼り付けた場合でも、同様のリスクがあります。貼り付け先の書式設定に関係なく、貼り付け後の文字の先頭に「’」があると、それは文字列扱いになっています。

貼り付けた後は必ず書式を確認しましょう。

検索範囲となる表には結合セルを使わないようにする

VLOOKUP 検索範囲に結合セルがある場合

セルが結合していると、一番左上だけにデータがあるとみなされます。そのため、検索に失敗するおそれがあります。

検索範囲となる表には結合セルを使わないようにしましょう。

5. VLOOKUPでエラー発生!|エラー別解決法

VLOOKUPエラーマップアイキャッチ画像

この章では、VLOOKUPで発生するエラーの種類と、その解決方法を解説します。

エラーには、次の4種類があります。

エラーの種類内容
#N/A探している検索値が見つからない
#NAME?スペル(綴り)に間違いがある
#REF!参照先に不備がある
#VALUE!列番号の指定に間違いがある

それぞれ原因が異なります。エラーの種類を理解した上で原因追及する方が、より早く解決できます。エラーが出ているからといってやみくもに数式をいじってしまうと、正しい部分まで変えてしまい、解決から余計に遠ざかってしまうおそれがあります。

「0」になる場合も、エラーではありませんが、思い通りの結果にならないという点では同じです。その原因と解決方法もあわせて解説します。

表示結果内容
「0」対象データのあるセルが空欄

エラー解決について体系的に学びたい方はこちら
≫ #N/A|#NAME|#REF|#VALUE|0|エラーの原因と対策~VLOOKUP

5-1. 「#N/A」|探している検索値が見つからない

VLOOKUP NAエラーOGP

#N/A」は、探している検索値が見つからない場合に表示されます。

気をつけるべきことは、検索値が見つからないだけであって、式の作り方は間違っていないケースも多いということです。

数式をいじる前に、次の項目をチェックしましょう。

#N/A の場合の主なチェック項目

  1. 検索値の場所は正しく指定できているか
  2. 式と検索範囲の検索値の書式は一致しているか
  3. 式と検索範囲の検索値は、全角or半角で一致しているか
  4. 検索範囲にダミーデータを追加し、正しく見つけ出すことができるか
  5. TRUE検索の場合、検索範囲の検索値が昇順か

VLOOKUPでの空白の有無の区別について

VLOOKUPは、空白の有無を区別します。

検索値に空白が含まれていると、別物になります。全角と半角も区別されます。

そのため、下記はすべて別物です。

表示内容 コメント
「山田太郎」 空白なし
「山田 太郎」 苗字と名前の間に半角の空白
「山田 太郎」 苗字と名前の間に全角の空白
「 山田太郎」 先頭に半角の空白
「 山田太郎」 先頭に全角の空白
「山田太郎 」 末尾に半角の空白
「山田太郎 」 末尾に全角の空白

先頭や末尾に空白があっても、セルのセンタリングをしていると気付きにくいので注意しましょう。

他からデータをコピーする場合も、空白を含めてコピーしまわないよう注意しましょう。

#N/Aエラーについて詳しくはこちら
≫ #N/A エラーを瞬殺!解決法3選と非表示&他文字へ置き換え~VLOOKUP

5-2. 「#NAME?」|スペル(綴り)が間違っている

#NAME?エラーアイキャッチ画像

#NAME?」は、数式のスペル(綴り)に間違いがある場合に表示されます。単純に文字入力ミスの場合もあれば、項目の指定ミスの場合もあります。

まずは、次の項目をチェックしましょう。

#NAME? の場合の主なチェック項目

  1. スペル(綴り)はあっているか
  2. 範囲指定の方法は間違っていないか(「:」で挟んでいるか )
  3. シート名の後ろに「!」がついているか
  4. ファイル名は「[]」で挟んでいるか

エクセル画面で縮小して表示している場合や、スマートフォンの小さな画面では特に見分けがつきにくいので注意しましょう。

見分けがつきにくい文字

参考に英字や数字で見分けのつきにくい文字を、下記にご紹介します。

【見分けがつきにくい文字】

O 大文字のオー
0 数字のゼロ
1 数字のイチ
l 小文字のエル
I 大文字のアイ
読点
, カンマ
. ピリオド
: コロン
; セミコロン
ダブルクォーテーション
アポストロフィ
ハイフン
_ アンダーバー
(アンダースコア)
ヤユヨ カタカナ
ャュョ 捨て仮名

もし、文字が判読しにくい時は、コピーしてWordなどに貼り付けてみましょう。文字サイズを大きくして、フォントを明朝体などに変更すれば、違いが分かりやすくなります。

ただし、ゴシック体や丸い書体にすると、かえって解らなくなります。試しに、イリーガルIllegal」(非合法の)という単語で見比べてみてください。

#NAME? エラーについて詳しくはこちら
≫ #NAME? エラーは数式の綴りを確認|誤り事例付~VLOOKUP

5-3. 「#REF!」|参照先に不備がある

#refエラーOGP

#REF!」は、参照先に不備がある場合に表示されます。

#REF! の場合の主なチェック項目

  1. 検索範囲の幅は足りているか
  2. 検索範囲の一部、あるいは全部が消失していないか
  3. 検索値のある場所が存在するか

① 検索範囲の幅は足りているか

検索範囲の幅(列数)が足りているか確認しましょう。VLOOKUPの3つ目の項目(何列目を返すか)が、検索範囲の幅よりも大きいと、このエラーになります。

列番号指定の注意点について詳しく知りたい方はこちら

② 検索範囲の一部、あるいは全部が消失していないか

下記の原因で、検索範囲の一部、あるいは全部が消失してしまう場合があります。

検索範囲の一部、あるいは全部が消失してしまう原因

  • 式と検索範囲が同じシートにあり、行や列を削除したときに、検索範囲まで消してしまった。
  • 検索範囲の上に別のものを貼り付けてしまった
  • 検索範囲のあるシートやファイル、フォルダの名称を変更してしまった
  • 検索範囲のあるシートやファイル、フォルダを丸ごと削除してしまった

もし、なにかメンテしたあとにこのエラーが出た場合は、いったんメンテ前の状態に戻してみましょう。もとに戻す方法は、「CTRL+Z」か、あるいは「戻る」をクリックします。)

それで解消した場合、メンテした内容を見直しましょう。

③ 検索値のある場所は、正しく存在するか

検索値のある場所が正しく存在するか確認しましょう。なぜなら、検索値のある場所は、数式で指定することが多いため、場所を正しく指定できていないケースがよくあるからです。

わかりにくい時は、計算式の入っているセルをダブルクリックするか、「F2」ボタンを押してみましょう。計算式が指定しているセルが表示されます。検索値や検索範囲が正しく指定できているかが確認できます。

数式の参照先をチェックする方法はこちら

#REF! エラーについて詳しくはこちら
≫ #REF! エラーは無効な範囲を参照している~VLOOKUP

5-4. 「#VALUE!」|列番号の指定に間違いがある

VALUEエラーアイキャッチ画像

#VALUE!」は、列番号の指定に間違いがある場合に表示されます。

列番号は、必ず1以上の数字でなければなりません。これより小さいとエラーになります。
マイナスもNGです。正しく指定できているか確認しましょう。

列番号の指定について基礎知識を確認したい方はこちら

列番号を数式で指定している場合のチェック方法

列番号を数式で指定している場合、見ただけでは判断できません。
その場合、その部分だけをコピーして他のセルに貼り付けてみます。

ただし、セルごとコピーしてしまうと、参照部分が相対的に動いてしまうため正確なチェックができません。

そこで、画面上部の窓に表示されている式の中から該当部分だけを切り取って、先頭に「=」をつけて空白のセルに貼り付けてみましょう。

#VALUE! エラーについて詳しくはこちら
≫ #VALUE! エラーは列番号の指定に誤りあり~VLOOKUP

5-5. 「0」|検索値は見つかったがセルの中身が空欄

VLOOKUP 0 アイキャッチ画像
エクセルVLOOKUPの使い方【エラー解決編】検索結果が0になるケース

検索結果が「0」の場合、エラーとはいわないまでも、好ましくないケースがあります。なぜなら「0」は、探している検索値を見つけることはできたが、欲しい情報が入っているセルの中が空っぽの場合に表示されるからです。

計算式は正しい場合が多いので、計算式をいじる前に、先に検索範囲に空欄がないか確認しましょう。

空白セルを一発で見つける方法はこちら

なお、「0」になることは了承済みで、とりあえず非表示にしたい方は下記を参照ください。
(参考)
「&””」を末尾につけて「0」を非表示にする方法はこちら
オプション設定で「0」を非表示にする方法はこちら

表示されている「0」は、書式設定に関係なく数値として扱われます。そのことについて、下記の注意点があるので参考にしてください。

テストの点数を管理する表では、VLOOKUPでの「0」は要注意!

テストの点数を管理している表では、検索結果の「0」は要注意です。なぜなら平均点がくるうおそれがあるからです。

もし、テストを受けていないために空欄にしている場合でも、VLOOKUPは「0」という数値を返します。これは数値なので、本当の「0点」と区別ができません。

そのためテストを受けたのと同じ扱いになり、平均点を下げてしまうのです。

VLOOKUPでの「0」について詳しくはこちら
 VLOOKUPの「0」の原因|非表示や他文字へ置換&空白セルの探し方

6. VLOOKUPをもっと使いこなしたい!|便利技・応用技20選

ここまでお読みいただいたら、ひとまずVLOOKUPを使って正しい検索結果が取り出せるようになったことと思います。でも、せっかくなのでもう一工夫して、便利に使いこなしたいところでしょう。

またひょっとして、人の作った数式を見て、「なぜこの関数と組み合わせているのだろう?」「私も、もっと高度な技を身につけたい!」と思われているかもしれません。

そこでこの章では、VLOOKUPがより便利で快適になる方法や、他の関数と組み合わせて高度に使いこなす応用技をご紹介します。

ぜひマスターして、仕事の効率化をはかってください。

【この章で紹介する便利技・応用技一覧】

見出しできること・したいこと技・テクニック
6-1.「0」を非表示「&””」を末尾につける
6-2.「0」を非表示オプションで設定
6-3.書式設定を一括変更区切り位置
6-4.重複を一括チェック条件付き書式
6-5.昇順を一括チェック条件付き書式
6-6.空白セルを一括チェック条件付き書式
6-7.検索範囲となる表の作成の便利技VLOOKUPを組み込む
6-8.検索範囲となる表の作成の便利技名前を定義
6-9.検索範囲となる表の作成の便利技テーブル化
6-10.検索範囲となる表の作成の便利技ピボットテーブルで作成
6-11.複数条件で検索検索値を「&」で連結
6-12.あいまい検索(一部一致、部分一致)「*」ワイルドカード
6-13.数式を縦・横にコピー「$」絶対参照の行・列の使い分け
6-14.エラー対応IFERRORと組み合わせ
6-15.エラー対応IF・ISERRORと組み合わせ
6-16.複数の検索範囲から検索IFで場合分け
6-17.複数の検索範囲から検索INDIRECTと名前の定義
6-18.列番号指定のテクニック枠外で指定
6-19.列番号指定のテクニックMATCHと組み合わせ
6-20.列番号指定のテクニックCOLUMNと組み合わせ

6-1. 「&””」を末尾につけて「0」を非表示

VLOOKUP 0を非表示にする

VLOOKUPは、該当データのセルが空白の場合は「0」を返します。
これを非表示するには、次の方法があります。

【「&””」を末尾につけて「0」を非表示】

=VLOOKUP(●,■:■,▲,◆) &””

これにより、該当データのセルが空白の場合、検索結果のセルも空白になります。

「&””」をつけると、計算結果はすべて「文字列」になります。

VLOOKUPが「数字」を返し、セルの書式設定が「数値」であっても、最終結果は「文字列」になります。

SUMやAVERAGEなどの関数からは、無視されます。(ただし、+-などの四則演算はできます。)

6-2. オプション設定で「0」を非表示

VLOOKUP オプションで0を非表示

エクセルのオプション設定で、「0」を非表示にすることができます。

「ファイル」→「オプション」→「詳細設定」で「ゼロ値のセルにゼロを表示する」のチェックを外します

オプション設定は、シート全体に適用されます。

テストの「0点」など、意味のある「0」も非表示になります。空白との見分けがつかなくなります。

そのため、計算式が完成していない間は、この設定はしない方が良いでしょう。

6-3. 書式設定を「区切り位置」で一括変更

VLOOKUP 書式設定を区切り位置で一括変更

区切り位置」を使うと、既存のデータの書式設定を一括変更できます。

この機能には、そのセルにあらためて新規入力したのと同じ効果があるためです。

あらかじめセルの書式設定をしておきましょう。その後この処理を実行すると、そのセルに設定された書式に、新規で入力したのと同じ結果が得られます。

6-4. 重複を「条件付き書式」で一括チェック

VLOOKUP 重複を条件付き書式で一括チェック

条件付き書式」を使うと、検索値の重複を一括でチェックできます。重複セルの色が変わり、一発で発見できるのです。

なぜこのチェックが重要かというと、VLOOKUPでは、検索値が重複していると一番上のものしか選ばないからです。それ以降に同一の検索値があっても無視されるので、検索漏れが発生するのです。

この方法で、検索値の重複をチェックすることをおすすめします。

6-5. 昇順を「条件付き書式」で一括チェック

VLOOKUP 昇順を条件付き書式で一括チェック

条件付き書式」を使うと、検索値が昇順になっているかを一括チェックできます。

なぜこのチェックが重要かというと、TRUE検索の場合、昇順に並んでいないと近似値を探し出せないからです。

6-6. 空白セルを「条件付き書式」で一括チェック

VLOOKUP 空白セルを条件付き書式で一括チェック

条件付き書式」を使うと、空白セルを簡単に見つけることができます。

上図の設定では、セルの値が空白の場合に、色で塗りつぶすことができます。塗りつぶす色を目立つ色にし、表示の縮尺を小さくすれば一目瞭然です。

空白セルがあると検索結果で「0」を返してしまうので、できれば解消しておきたいものです。

空白セルを見つける簡便な方法

CTRL」+「矢印キー(↑↓←→)」で簡単に空白セルを見つけることができます。

次の空白セルの手前までジャンプします。空白セルが無ければ、検索範囲の終端までジャンプします。

ちなみに、そこからもう一回押すと、100万行の果てまでジャンプします。

縦横無尽とはいきませんが、特定の列を調べたい場合には大変便利です。

6-7. 検索範囲となる表にもVLOOKUPを組み込む

一覧表にもVLOOKUPを使う

検索範囲となる表の中にも、VLOOKUPを組み込むと便利です。同じ値が繰り返し出てくる場合など、VLOOKUPで表示させれば簡単で、ミスも減らせます。

上の図では、商品一覧表を作成する際に、商品のメーカー名や寸法をVLOOKUPで自動入力させています。メーカー名を入力する手間が省けますし、メーカーの情報元に変更があった場合も自動で更新されます。

6-8. 検索範囲に「名前を定義」する

VLOOKUP 名前の定義で検索範囲に名前を付ける

検索範囲に「名前を定義」することで、数式をシンプルにできます。定義した名前をそのまま数式に記述できるので、番地(アドレス)を入れるより、直観的でわかりやすくなります。

【定義した名前を数式に組み込む】

=VLOOKUP(●, * ,▲,◆)

「*」には定義した名前をそのまま入力

名前は自由に付けることができます。日本語もOKです。ただし、数式に組み込むので、できるだけ簡潔でわかりやすい名前にしましょう。

検索範囲の最下端に行を追加した場合、定義した範囲に含まれていないおそれがあります。
検索範囲をメンテした際は、「名前の管理」から参照範囲に漏れがないか確認するようにしましょう。

名前を定義すると、その内容は「名前の管理」からしか確認できません。
そのため他人からは、名前を定義していることが気付きにくいものです。
もし、複数人でエクセルをメンテする場合は、名前を定義していることを情報共有しておきましょう。

6-9. 検索範囲を「テーブル化」する

VLOOKUP 一覧表をテーブル化する

検索範囲をテーブル化して、そのままVLOOKUPに使うことができます。

テーブル名をそのまま数式に記述できるので、番地(アドレス)を入れるよりも、直観的でわかりやすくなります。

【テーブル名を数式に組み込む】

=VLOOKUP(●, * ,▲,◆)

「*」にはテーブル名をそのまま入力

テーブル名は自由に付けることができます。日本語もOKです。ただし、数式に組み込むので、できるだけ簡潔でわかりやすいテーブル名にしましょう。

テーブル化は、エクセルシートを本格的なデータベースにできる方法です。
便利であると同時に、ルールも厳しくなります。
システマチックに管理したい方向けの方法とお考えください。

6-10. ピボットテーブルで検索範囲となる表を作成

VLOOKUP ピボットテーブルで検索範囲の表を作る

ピボットテーブルを使って、検索範囲となる表を作ることができます。(ピボットテーブルとは、エクセルで表を作成する機能の一つです。)

不規則なデータからでも、一発でフォーマットが整った表をつくれます。列の入れ替えなども簡単です。

ピボットテーブルで検索範囲をつくるメリット

  • 検索値にしたい項目がどこにあっても、一番左に移動できる
  • 不規則なデータからでも、関係なくフォーマットを整えることができる
  • 列の入れ替えなど、検索範囲の組み換えが簡単にできる

ピボットテーブルを使いこなすには、ある程度の知識が必要です。ただ、VLOOKUPのためだけに使うのであれば、必要最小限のルールで簡単に作ることができます。

筆者はこの技を、VLOOKUPの設計段階でよく使います。検索範囲のレイアウトをどうするか、試行錯誤するのに便利です。

フォーマットが簡単に整う上に、項目の取捨選択や並べ替えがドラッグするだけでできるので、非常に効率よくVLOOKUPを組むことができます。

皆さんもぜひ活用してください。

6-11. 「&」で検索値を連結して複数条件で検索

VLOOKUP 複数条件で検索する

複数の条件で一致するものを検索したいなら、検索値を「&」で連結する方法が簡単です。

【「&」で検索値を連結】

=VLOOKUP( ●&● ,■:■,▲,◆)

検索範囲の方にも、あらかじめ左に1列追加し、検索値を連結した複合検索値を作成し、最下行までコピーしておきます。

上図では「学年」「組」「出席番号」を連結しています。連結数に制限はありません。

「&」で連結すると、数字もすべて文字列として扱われます。

6-12. 「*」(ワイルドカード)であいまい検索(一部一致、部分一致)

VLOOKUP ワイルドカードであいまい検索(部分一致、一部一致)

あいまい検索(部分一致・一部一致)するには、「」(ワイルドカード)を使います。

【「*」(ワイルドカード)であいまい検索(一部一致、部分一致)】

=VLOOKUP(●&”*”,■:■,▲,◆)・・・左側は一致、右側は任意

=VLOOKUP(”*”&●&”*”,■:■,▲,◆)・・・中央は一致、左右は任意

=VLOOKUP(”*”&●,■:■,▲,◆)・・・左側は任意、右側だけ一致

使い方は、検索値以外の部分に「*」を配置します。その際、「*」を「””」で挟み、検索値のある番地(アドレス)と「&」でつなぎます。

そうすると「*」の部分には何がきても構わないという意味になります。

「*」(ワイルドカード)を組み合わせると、数字であってもすべて文字列として扱われます。

検索範囲の方の検索値の書式設定も、「文字列」に設定しなければなりません。

6-13. 「$」(絶対参照)を行・列で使い分けて数式を縦・横にコピー

VLOOKUP 絶対参照を行・列で使い分ける

数式を縦・横にコピーするには、「$(絶対参照)を行・列で使い分けます。

【「$」(絶対参照)の使い分け方】

=VLOOKUP($行列,$■:$■,▲,◆)・・・行アドレスは固定、列アドレスは可変

=VLOOKUP(行$列,$■:$■,▲,◆)・・・行アドレスは可変、列アドレスは固定

=VLOOKUP($行列&行$列,$■:$■,▲,◆)・・・縦・横にコピー可能

縦・横(マトリックス)にコピーする際に、うまく使い分ければ同じ数式をすべてにコピーできます。やみくもにすべて絶対参照にするのではなく、必要に応じて「$」をつけるべきか見極めるのが肝要です。

数式はできるだけ統一しておく方が、メンテナンスが格段にしやすくなり、ミスも減ります。

数式をチェックする方法

数式のチェック方法を3つご紹介します。
メンテナンスの際にお役立てください。

EXCEL 数式のチェック方法

 

6-14. IFERRORでエラーを非表示|代わりに空白や”要確認”などを表示

VLOOKUP IFERRORと組み合わせてエラーを非表示

IFERRORと組み合わせることで、エラーを非表示にできます。

【IFERRORでエラーを非表示にする】

=IFERROR(VLOOKUP(●,■:■,▲,◆),””)

計算結果がエラーになる場合のみ、指定した文字列を返します。上の式では「””」としているので、空白(つまり非表示)になります。

「0」にしたり、「”要確認”」や「”未入力”」にしたりするなど、自由に設定できます。文字だけではなく、関数を組み込むこともできます。

特に有効なケースを下記にご紹介します。

IFERRORを組み合わせると有効なケース

  • エラーが出ること自体は問題ではなく、見栄えが悪いので非表示にしたい
  • エラーが1つでも存在すると、そのセルを含む他の計算(Σ(合計)等)ができなくなるので置き換えたい
  • エラーの場合のみ、追加の処理をしたい
  • 検索範囲は今後メンテしていくが、とりあえずエラーが出ないようにしたい

IFERRORについて詳しくはこちら
≫ IFERRORでエラーを非表示&複数範囲を一発検索~VLOOKUP

6-15. IF・ISERRORでエラーを判定し処理内容を切り替える

VLOOKUP IF・ISERRORと組み合わせてエラー判定

ISERRORと組み合わせることで、検索結果がエラーかどうかを判定できます。

【ISERRORでエラーを判定する】

=ISERROR(VLOOKUP(●,■:■,▲,◆))

[表示結果]
TRUE : エラーあり

FALSE : 正常

エラーがあれば「TRUE」、正常なら「FALSE」が出力されます。

【IF・ISERRORで判定結果に応じて処理を切り替える】

=IF(ISERROR(VLOOKUP(●,■:■,▲,◆)),★,☆)

[処理内容]
★: エラーの場合の処理

☆ : 正常な場合の処理

さらに、IFと組み合わせることで、判定結果に応じて処理内容を選択することができます。

ISERRORは、エラーの場合に「TRUE(本当の、真実の)」を返します。
逆に、正常な場合は「FALSE(嘘の、偽りの)」を返します。

感覚的には逆のような気もしますが、この関数はエラーであることが「TRUE(真実)」なのです。

ISERRORは、単独では「TRUE」か「FALSE」を返すだけなので、あまり使い道はありません。IFと組み合わせることで、効果を発揮します。

逆にIFと組み合わせると、正常な場合も、それに対する処理を実行できます。
この点については、エラーの場合しか対応できないIFERRORより優れています。

ISERRORについて詳しくはこちら
≫ VLOOKUPのエラーをISERRORで非表示|エクセル2003以前にも対応

6-16. IFで場合分けして複数の検索範囲から検索

VLOOKUP IFで場合分けして検索範囲を切り替える

複数の検索範囲から検索するには、検索値をIFで場合分けすることで対応できます。検索値が、数字の大小などで区切りやすい場合に有効です。

切り替える方法には、次の2種類があります。

【IFで検索範囲だけを切り替える】

=VLOOKUP(●,IF( [条件式] ,■:■,■:■),▲,◆)

[条件式] : 検索範囲を切り替える条件
■:■ : 条件に合致する場合の検索範囲
■:■ : それ以外の場合の検索範囲

2つの検索範囲のフォーマットが同じであれば、こちらの方が数式をシンプルにできます。

【IFでVLOOKUPの数式を丸ごと切り替える】

=IF( [条件式] ,( VLOOKUP(●,■:■,▲,◆),VLOOKUP(●,■:■,▲,◆))

[条件式] : 処理を切り替える条件
VLOOKUP : 条件合致の場合に実行
VLOOKUP : それ以外の場合に実行

この方法であれば、検索範囲のフォーマットや列番号などが異っていても、切り替え可能です。

6-17. INDIRECTと「名前の定義」で検索範囲を可変にする

VLOOKUP INDIRECTと「名前の定義」で一覧表を切り替え

検索範囲に名前を定義し、それを切り替えることで、複数シートから検索できるようになります。

ただし、定義した名前があるセルの番地(アドレス)をそのまま数式に入力しても、検索範囲を指定したことにはなりません。

そこで、INDIRECTと組み合わせて使用します。

【INDIRECTと「名前の定義」で検索範囲を切り替える】

=VLOOKUP(●, INDIRECT(■) , ▲ , ◆ ) 

■ : 定義した名前があるセルの番地(アドレス)
INDIRECT(■)  : ■にある名前を数式に直接入力したのと同じ意味になる

INDIRECTを使うことで、数式に直接入力したのと同じ意味になります。

6-18. 列番号を枠外に列記して指定

VLOOKUP 列番号を枠外で指定

列番号の指定は、枠外に列記してそれを指定する方法も可能です。

数字がそのまま表示されているので、一目でわかりミスも少なくできます。

ただし、印刷時に誤って出てしまわないよう注意が必要です。

また、絶対参照と相対参照とを使い分ければ、数式を縦・横にコピーできます。

絶対参照と相対参照の使い分け方について詳しく知りたい方はこちら

6-19. MATCHで列番号を指定

VLOOKUP MATCHで列番号を指定

列番号を計算式で指定する方法として、MATCHを使う方法があります。

MATCHは、指定した文字列が、指定した範囲中で、何番目にあるかを調べる関数です。

【MATCHで検索したい文字列が何番目にあるかを算出】

=MATCH (●, ■:■ , ◆)

●  : 検索したい値を指定
■:■: 検索する範囲を指定
◆  : 検索方法を指定(完全一致 or 近似値)

これをVLOOKUPに組み込むことにより、列番号を指定することができます。

【MATCHでVLOOKUPの列番号を指定する】

=VLOOKUP(●,■:■, MATCH( □□□ ) ,◆) 

□□□ : MATCHに必要な情報をすべて入力(上段のMATCHの解説BOX参照)
MATCH( □□□ )  : 指定した文字列が何番目にあるかを返す

MATCHの参照先を検索範囲となる表のヘッダー(1行目)にしておけば、ヘッダーに連動して列番号を指定することができます。

6-20. COLUMNで列番号を指定

VLOOKUP COLUMNで列番号を指定

列番号を計算式で指定する方法として、COLUMNを使う方法があります。

COLUMNは、セルの番地(アドレス)のうち、列の部分を取得する関数です。

【COLUMNでセルの列の番地(アドレス)を取り出す】

=COLUMN ()

() : カッコ内を空欄にすることで、そのセルのある列の番地を取り出せる

これをVLOOKUPに組み込むことにより、列番号を指定することができます。

【COLUMNでVLOOKUPの列番号を指定する】

=VLOOKUP(●,■:■, COLUMN()+/-★ ,◆) 

COLUMN() : そのセルのある列の番地を取り出す
COLUMN()+/-★ : プラスマイナスして欲しい列番号を作り出す

COLUMNで得た結果をプラスマイナスすることにより、欲しい列番号を作り出します

これをVLOOKUPの列番号として使うと、列の番地(アドレス)に連動して指定することができます。

VLOOKUPの数式の移動/コピーにあわせて、列番号も同じ間隔で変化させたい場合に有効です。

7. VLOOKUPで困った!|チェックしたい5つの項目

VLOOKUPで数式を組んでいると、なぜかうまくいかず原因不明で困り果てる時があります。

そんな時は、やみくもに数式をいじらずに、原因が潜んでいる可能性の高い項目から一つずつ確認していくのが得策です。

そこで、間違いやすいポイントを項目ごとにまとめました。
1つずつチェックしていくことで、きっと原因が見つかることでしょう。

解決方法や予防策もあわせてご紹介しますので、参考にしてください。

潜んでいる原因は1つとは限らない

筆者の経験から、VLOOKUPは複合的な原因が潜んでいるケースが多いです。
1つの問題が解決したからといって、油断してはいけません。
大きなエラーが解消すると安心してしまい、小さな間違いを見落としてしまいがちです。

VLOOKUPは、数式が完成したら、圧倒的に速い計算結果が得られます。
それゆえ、作成にも十分時間をかけましょう。

計算結果に少しでも違和感があれば、一度深呼吸して、多面的にチェックすることをおすすめします。

7-1. VLOOKUPでのチェック項目一覧(ダウンロード可能)

チェックにあたっては、VLOOKUPの各項目にそって、下記を一つずつ確実に進めていきましょう。

VLOOKUPのチェック項目一覧

  1. 検索値に関するチェック項目
  2. 検索範囲の表に関するチェック項目
  3. 検索範囲の場所指定に関するチェック項目
  4. 列番号指定に関するチェック項目
  5. 検索方法に関するチェック項目

チェックリストの詳細は、下記からダウンロードできます。

ダウンロードボタン

7-2. ①検索値に関するチェック項目

検索値の書式設定は、数式と検索範囲で一致しているか
LOOKUPでは、書式が異なれば、見た目が同じでも別物になります。数値なのか文字列なのか、思い込みはやめてきちんと書式設定を確認しましょう。
複数の表を結合した場合は、それぞれの書式を確認しましょう。異なる部分を見つけたら、必ず列ごとに書式を統一しましょう。

【参照先】
書式を一発で整えたい場合の「区切り位置」の使い方はこちら
検索値は全角か半角か、数式と検索範囲で一致しているか
LOOKUPでは、全角/半角も区別されます。
見た目では全角と半角の区別がつかない場合もあるので、注意しましょう。
検索値のスペル(綴り)は正しいか
検索値のスペル(綴り)は本当に正しいか、再度確認しましょう。フォントによっては、見た目ではわかりにくい場合があるので、思い込みに注意してください。
ゼロとオーの違いや、大文字アイと小文字のエルと数字の1は、フォントによっては見分けがつかないことがあります。

【参照先】
見分けにくい文字についての解説はこちら
検索値のあるセルの指定は正しいか
セルの番地(アドレス)を直接指定している場合は、正しく指定できているか確認しましょう。
当たり前ですが、番地(アドレス)が正しいことが大前提です。このチェックを怠ってはいけません。
【参照先】
セルの指定先を確認する方法はこちら
指定場所が相対参照でずれていってないか
相対参照($がない)にしていると、数式を移動/コピーした際にずれていくおそれがあります。
元の数式が正しいからといって安心していると、事故のもとです。

【参照先】
範囲指定についての絶対参照と相対参照の違いはこちら
計算式で指定している場合、そのセルは実在するか
セルの指定を計算式で行う場合、正しく指定できているか計算式を見ただけでは判断できません
その場合は、計算式の中のセルを指定している部分だけをコピーして、空いているセルに貼り付けてみましょう。
先頭に「=」をつけるのをお忘れなく。

7-3. ②検索範囲の表に関するチェック項目

検索値のある列に重複はないか
検索値のある列に重複があると、上にある検索値が優先されます。
重複していてもエラーにならず、気付きにくいので注意しましょう。
【参照先】
検索値の重複を一括チェックする方法はこちら
TRUE検索の場合、検索値は昇順になっているか
TRUE(近似値)で検索する場合、検索範囲の検索値は昇順にしておく必要があります。
検索結果だけでは気付きにくいので、検索範囲をくまなくチェックしましょう。
【参照先】
検索値が昇順かを一括チェックする方法はこちら
結合セルが含まれていないか
セルが結合していると、検索にあたって不都合が生じます。
結合している左上の番地にのみ、データが存在することになってしまうからです。
VLOOKUPにとって、検索ミスの原因となりますので、セルの結合は避けましょう。
【参照先】
結合セルについての注意点はこちら
行や列が非表示になっていないか
設定で非表示になっている場合もあれば、グループ化によって隠れている場合もあります。
列が非表示なっていると、列番号を数え間違えてしまうおそれもあります。
一度、非表示の設定をすべて解除してチェックしましょう。
不規則な部分はないか
行数が多いと途中でずれていたりしても、気付きにくいものです。表示の縮尺を下げて、目を細めて全体を見渡してみるとよいでしょう。
また最下行まで移動して、フォーマットが崩れてないかチェックしましょう。
「CTRL」+「矢印キー(↑↓←→)」で、表の端までジャンプできます。途中に空白セルがあるとそこで止まるので、再度ジャンプしてみましょう。

7-4. ③検索範囲の場所指定に関するチェック項目

場所の指定は「:」で挟んで指定しているか
範囲指定は「:」で挟む必要があります。
今一度、範囲指定の方法を確認しましょう。
【参照先】
検索範囲の指定方法について基礎知識を確認したい方はこちら
シートやファイル名の指定の仕方は正しいか(「! 」「[ ]」「‘」は、正しく使えているか)
別シートを指定する場合、数式内のシート名の末尾に「」が必要です。
別ファイルを指定する場合、ファイル名を「[ ]」で挟みます。
社内ネットワーク上の別ファイルを指定する場合、フォルダやドライブ名を「」で挟みます。
フォントによっては非常に細くなり、見落としやすいので注意しましょう。
【参照先】
別シートや別ファイルの指定の仕方|記号に注意
定義した名前やテーブル名の指定は正しいか
検索範囲に名前を定義したり、テーブル化したりした場合、その名称を正しく指定しているか確認しましょう。
数式を見ただけでは確認できないため、見落としがちになるので注意しましょう。
【参照先】
「名前の定義」について詳しく知りたい方はこちら
「テーブル化」について詳しく知りたい方はこちら
範囲指定が相対参照でずれていってないか
指定場所を、相対参照($がない)にしていると、数式を移動/コピーした際にずれていくおそれがあります。
すべてがエラーとなるわけではありませんので、かえって注意が必要です。
【参照先】
範囲指定についての絶対参照と相対参照の違いを確認したい方はこちら
列の数(表の幅)は足りているか
検索範囲の端までカバーしているか確認しましょう。列番号の指定より、列の幅の方が小さいとエラーになります。
【参照先】
列番号の指定について基礎知識を確認したい方はこちら
検索範囲の行数を増やした時、最下端までカバーしているか
検索範囲の行を増やした場合、VLOOKUPの数式側もメンテが必要な場合があります。名前を定義している場合や、テーブル化で管理している場合でも、念のため確認しましょう。
【参照先】
「名前の定義」について詳しく知りたい方はこちら
「テーブル化」について詳しく知りたい方はこちら
検索ファイルのあるファイルは「名前を付けて保存」しているか(「book1」のままになっていないか)
ファイルを新規で作った場合、「book1」のままで放置してはいけません。
「名前を付けて保存」してから、数式を組むようにしましょう。
うっかり閉じてしまうと再現できません。
【参照先】
「book1」のまま放置したときの失敗事例はこちら

7-5. ④列番号の指定に関するチェック項目

検索値のある列も含めて数えているか
列番号は、検索値のある列も含めて、左から右へ数えます。うっかり、検索値の隣から数え始めてないか、確認しましょう。
横に長い表の場合、数え損ねることがあるので注意しましょう。
【参照先】
列番号の数え方について基礎知識を確認したい方はこちら

列の番地は通常アルファベット表示です。幅の広い表だと「Z」を超え、その次は「AA」になります。アルファベットは26文字なので、「AA」は27列目です。

なお、横への移動は、Ctrl + Shift + マウススクロールでできます。

列番号の指定が1以上の数になっているか
列番号の指定は、1以上の数にする必要があります。それより小さい数や、マイナスはNGです。
数式で指定している場合は気付きにくいので、別のセルに貼り付けてチェックしましょう。
【参照先】
列番号の数え方について基礎知識を確認したい方はこちら
計算式で指定している場合、正しい数字になっているか
列番号を計算式で指定する場合、正しく数字になっているか計算式を見ただけでは判断できません
その場合は、計算式の中のセルを指定している部分だけをコピーして、空いているセルに貼り付けてみましょう。
先頭に「=」をつけるのをお忘れなく。

7-6. ⑤検索方法に関するチェック項目

0=FALSE、1=TRUEになることを理解して選んでいるか
検索方法の指定で「0」を入れると「FALSE」、「1」を入れると「TRUE」の扱いになります。
混同しやすいので、正しく指定できているか確認しましょう。
【参照先】
検索方法について基礎知識を確認したい方はこちら
省略=TRUEになることを理解した上で省略しているか
検索方法の指定を省略すると、「TRUE検索」になります。省略していると目に見えないため、間違いに気付きにくいものです。
近似値検索であることに問題ないか、理解した上で省略しましょう。
【参照先】
参考:(推奨)どちらにするか迷ったら、ひとまず「FALSE」を選ぶ

7-7. 【注意】そもそもVLOOKUPでできないことをやろうとしていないか

VLOOKUPのイメージ図

うまくいかないとお悩みのところ、そもそもVLOOKUPではできないことをやろうとしていないか、念のため確認しましょう。

次の3つは、VLOOKUPにとって、できそうでできないことです。

VLOOKUPにとって、できそうでできないこと

  • 検索結果は一つしか返せない・・・複数見つけても1つ返したらおしまい
  • 集計ができない・・・検索後の演算・集計は他の関数に助けてもらう必要あり
  • 表を横方向へは検索できない・・・検索範囲となる表には様々な制限あり

では、一つずつ解説します。

検索結果は一つしか返せない
VLOOKUPは、同時に複数の検索ができません。検索値が重複していても、1つ目を見つけたら、その結果を返しておしまいです。
それゆえ、検索範囲において検索値が重複していないか確認することが、とても重要になってきます。
検索値に重複がないかを一括チェックする方法はこちら
集計ができない
VLOOKUPは、検索のための関数なので、集計はできません。膨大なデータの中から探し出してくることが仕事です。
しかし、集計が必要なケースもあります。その場合は、他の別の関数と組み合わせる必要があります。
表を横へは検索できない
VLOOKUPは、検索範囲を横方向へ検索できません。検索できるのは縦方向だけです。検索範囲の表も、それに合わせて設計しましょう。

まとめ

いかがでしたでしょうか。

この記事では、VLOOKUPについて次のことを解説しました。

この記事でわかったこと

  1. VLOOKUPでできること
  2. VLOOKUPの基本的な使い方
  3. エラー解決方法
  4. 便利技・応用技20選
  5. 困ったときにチェックすべき項目

初心者の方は、学びたい情報は得られましたでしょうか。習熟されている方は、新しい発見はありましたでしょうか。

幅広く網羅的に解説していますので、この記事を通してVLOOKUPをぜひマスターしてください。

エクセルでお困りのことがありましたら、『エクセルの窓口』で検索してください。

エクセルの窓口検索

ブックマークお気に入りに登録していただけたら幸いです。

スキルに自信がついたら新しい世界へ飛び出そう!
転職サイト登録なび
転職サイト登録なび