Excelの機能の中に様々な種類の検索関数があります。用途に応じた適切な検索関数を駆使することで、エクセル内のデータ検索や抽出を手軽に行うことができます。
この記事では、検索関数の中でも頻繁に使われることの多いVLOOKUP・INDEX・XLOOKUP関数を中心に解説していきます。
チャット一括管理アプリ「OneChat」が今なら初月無料!!
OneChatのダウンロードはこちらから
VLOOKUP関数による効率的な検索手法
VLOOKUP関数は、表の縦方向にデータを検索し、一致した値と同じ行にあるデータを返すExcelの関数です。
特定の値で表の検索を行うことで、必要な情報を抽出することができます。
例えば、商品コードから商品情報を取り出したり、顧客コードから顧客情報を抽出するなど、さまざまな用途があります。また、データの抜け漏れチェックや表の結合などにも利用可能です。
VLOOKUPの基本的な使い方と設定方法
VLOOKUPに関する基本的内容を理解していきましょう。VLOOKUP関数の構文は以下の通りです。
=VLOOKUP(検索値, 範囲, 列番号, 検索の型)
| 項目 | 内容 |
| 検索値 | 検索対象となる値の入力を行います。数字以外の文字列などを検索値に指定したい場合は、ダブルクォーテーションで囲った状態（「”〇〇”」）で入力しましょう。 検索値は、全角・半角の区別は可能ですが、英字の大文字・小文字の区別はできないため、英字が検索値に含まれる場合は注意しましょう。 |
| 範囲 | 検索値の検索を実行する範囲の指定を行います。この際は、検索値が左端の列に含まれていること、範囲指定は帰る値の列が含まれていることに注意しましょう。 |
| 列番号 | 範囲で指定した中から、取得したい値が左から何列目のものであるかの指定を行います。 |
| 検索の型 | 「TRUE」「FALSE」の値の入力をします。「TRUE」の場合は、近似照合が行われ、指定した範囲の列のうち、検索値に最も近い値が返ってきます。 「FALSE」の場合は、完全一致による検索となるため、指定した範囲の列のうち、検索値に対応する値が返ってきます。検索の型の入力を省略した場合は「TRUE」として処理されます。 |
参考：VLOOKUP 関数 – Microsoft サポート
一致と近似の検索：完全一致と部分一致の違い
完全一致と部分一致は、どのような違いがあるのでしょうか。
完全一致
完全一致検索は、指定された条件と完全に一致するデータを見つけます。
例えば、検索キーワードが「apple」の場合、完全一致検索は「apple」という文字列のみを含むセルを見つけます。そのため、「pineapple」 や「applesauce」などは対象外となるため値は返ってきません。
部分一致
部分一致検索は、指定された条件に部分的に一致するデータを見つけます。
例えば、検索キーワードが「apple」の場合、部分一致検索は「apple」を含む文字列を持つすべてのセルを見つけます。つまり、部分一致検索では、「apple」だけでなく「pineapple」や「applesauce」といった、「apple」が含まれるものも対象となるため値が返ってきます。
完全一致は指定された条件と完全に一致するものを見つけるのに対し、部分一致は条件の一部が一致するものを見つけるのです。
エクセルにおける完全一致検索は、通常は「VLOOKUP関数」の最後の引数にFALSEを指定することで実行されます。一方、部分一致検索は「FIND関数」「SEARCH関数」「FILTER関数」などに使用されることが多いです。
| 関数 | 構文 | 概要 |
| FIND | =FIND(検索文字列,対象,開始位置) | 指定されたテキスト内で指定された文字列を検索し、その位置を返す |
| SEARCH | =SEARCH(検索文字列,対象,開始位置) | 指定されたテキスト内で指定された文字列を検索し、その位置を返す |
| FILTER | =FILTER(範囲, 条件,空の配列) | 指定された条件に基づいてデータをフィルタリングし、条件に一致する行または列のみの抽出を行う |
参考：
VLOOKUPのエラー対処法と代替手段
VLOOKUP関数を活用する中で、「#N/A」などのエラーが表示されることがあります。
以下では、VLOOKUP関数のエラーの原因とその対処方法・代替手段について紹介します。
- 検索値が指定した範囲の最初の列に存在しない
- 「FALSE」を指定した際に指定の項目が見つからない
- 「TRUE」を指定した際に検索値が指定した範囲の最小値よりも小さい
- 検索の型を「TRUE」と指定した際に参照列が昇順となっていない
検索値が指定した範囲の最初の列に存在しない
VLOOKUP関数では、検索される値はテーブル配列の左端の列にある値となります。そのため、検索値が配列の左端の列に存在しない場合、エラーが表示されます。
対処方法
VLOOKUP関数が正しい列を検索できるように調整しましょう。もし、調整が難しい場合は列の移動を行う方法が簡単です。
ただし、セルの値が他の計算結果である場合などは列の移動ができないこともあると思います。そのような場合は、この後紹介するINDEX関数とMATCH関数を組み合わせて使用することにより解決できます。この方法では、参照するテーブル内の列の位置に囚われず、列の値を検索することができます。
「FALSE」を指定した際に指定の項目が見つからない
検索の型を「FALSE」と指定した際に、データ内に検索値と完全一致する項目を見つけられなかった場合、エラーが表示されます。
対処方法
検索値と完全一致するデータがシート内に確実に存在するがVLOOKUP関数で見つけることができない場合は、参照対象のセルに印刷されない文字や非表示のスペースが含まれている可能性があります。
また、セルが正しいデータ型になっていない可能性もあります。例えば、数値が含まれるセルの場合、文字列ではなく数値として書式設定が行われている必要があります。
「TRUE」を指定した際に検索値が指定した範囲の最小値よりも小さい
検索の型を「TRUE」と指定した際に、指定した範囲の最小値よりも検索値が小さい場合、エラーが表示されます。「TRUE」を指定した場合、指定した範囲から検索値に近似一致したデータを検索します。この際、検索値が数値の場合は、検索値よりも小さいかつ最も近い値が返されます。
対処方法
必要に応じて検索値の修正を行います。
検索値の変更が行えず、値のマッチングを柔軟に行いたい場合は、INDEX関数とMATCH関数を組み合わせて使用することにより解決できます。
INDEX関数とMATCH関数を組み合わせて使用した場合、検索値よりも大きい値・小さい値・一致する値それぞれの検索を行うことができます。
検索の型を「TRUE」と指定した際に参照列が昇順となっていない
検索の型を「TRUE」と指定した際に、いずれかの参照列が昇順で並び替えが行われていない場合はエラーが表示されます。
対処方法
昇順に並び替える必要があるのは近似照合を行う「TRUE」の検索の型の場合だけです。そのため、検索の型を「FALSE」とし、完全一致検索をするように変更すれば並び替える必要はありません。
また、INDEX関数とMATCH関数を組み合わせて使用することにより、並び替えずとも検索を行うことができます。
セルに大きな10進数や時間値が含まれている場合、浮動小数点制度によってエラーが表示されます。
そのため、正常に関数を動作させるためには、非常に大きな浮動小数点数をもつ数値を5桁の小数点以下に丸めることが必要です。
対処方法
ROUND関数を活用することで5桁以下の小数点の桁数を丸めることができます。ROUND関数の構文は、以下の通りです。
=ROUND(数値, 桁数)
| 項目 | 内容 |
| 数値 | 四捨五入の対象になる数値の指定を行います。 |
| 桁数 | 数値を四捨五入した際の結果の桁数の指定を行います。正の数を指定した場合、数値の小数点以下（右側）が指定した桁数の分四捨五入となります。0に指定した場合、最も近い整数として四捨五入となります。負の数を指定した場合、数値の整数部分（小数点の左側）が四捨五入となります。 |
参考：
VLOOKUP 関数の #N/A エラーを修正する方法 – Microsoft サポート
INDEX関数とMATCH関数を組み合わせた検索法
INDEX関数とMATCH関数を組み合わせた検索法について紹介します。
INDEX関数の基本概念と使い方を解説
INDEX関数は、指定された範囲内から値を抽出するために使用されます。INDEX関数の構文は以下の通りです。
=INDEX(配列, 行番号, [列番号])
| 項目 | 内容 |
| 配列 | 値を抽出する対象となる範囲や配列定数を指定します。 配列が1行や1列のみである場合、それぞれの行番号や列番号を省略することが可能です。 |
| 行番号 | 配列の中にあって値を返す行を数値によって指定します。行番号を省略する際は、列番号が必須となります。 |
| 列番号 | 配列の中にあって値を返す列を数値によって指定します。列番号を省略する際は、行番号が必須となります。 |
MATCH関数による検索条件と行番号の特定
MATCH関数は、指定された値が範囲内で最初に出現する位置を見つけるために使用されます。MATCH関数の構文は以下の通りです。
=MATCH(検査値, 検査範囲, [照合の型])
| 項目 | 内容 |
| 検査値 | 検索範囲の中から検索を行う値の指定をします。検索値では、値（文字列や数値、論理値）やこれらの値に対するセル参照の指定が可能です。 |
| 検査範囲 | 検索を行うセルの範囲の指定をします。 |
| 照合の型 | 検索範囲の中から、検索キーを検索する方法を指定します。1：検索キー以下の最大値で検索（検索範囲を昇順で並べる）0：完全一致-1：検索キー以上の最小値で検索（検索範囲を降順で並べる）省略した場合は「1」が選択されます。 |
INDEXとMATCHを組み合わせてデータを抽出
INDEX関数とMATCH関数を組み合わせてデータを抽出する方法について紹介します。
INDEX関数とMATCH関数を組み合わせると、指定した条件に基づいてデータを抽出することができます。具体的な手順は以下の通りです。
MATCH関数を使用して条件に一致する行番号または列番号を見つける
- MATCH関数を使用して、条件に一致するデータの行番号または列番号を見つける
- データの中で特定の値を探す場合、MATCH関数を使用してその値が存在する行番号または列番号を見つける
INDEX関数を使用してデータを抽出する
- INDEX関数を使用して、見つけた行番号または列番号に対応するデータを抽出する
- 見つけた行番号または列番号をINDEX関数の引数として指定し、データを抽出を行う
INDEX関数とMATCH関数を組み合わせた具体的な構文は以下のようになります。
=INDEX(データの範囲, MATCH(条件, 検索範囲, 0))
この式では、MATCH関数を使用して条件に一致するデータの行番号または列番号を見つけます。その行番号または列番号をINDEX関数の引数として指定してデータの抽出を行うことができます。
XLOOKUP関数を用いた複数条件の検索
XLOOKUP関数を用いた複数条件の検索について紹介します。
XLOOKUP関数の基本的な使い方と設定方法
XLOOKUP関数は、Excel365およびExcel 2019以降のバージョンで利用可能な新しい検索関数です。XLOOKUP関数は、指定された値または条件に基づいてデータを検索し、対応する結果を返します。
VLOOKUP関数とは異なり、検索値の配置が制限されていないため、利用可能なバージョンであるならば、ぜひマスターしたい関数です。XLOOKUP関数の構文は以下の通りです。
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
| 項目 | 内容 |
| 検索値 | 検索したい値を指定します。数字以外の文字列などを検索値に指定したい場合は、ダブルクォーテーションで囲った状態（「”〇〇”」）で入力しましょう。 |
| 検索範囲 | 検索を実行する範囲の指定を行います。 |
| 戻り配列 | 検索範囲内に指定した検索値と一致するものが存在した場合に結果を返す範囲・配列の指定をします。 |
| 見つからない場合 | 検索範囲内に指定した検索値と一致するものが存在しなかった場合に返される内容を指定します。省略した場合は「#N/A」が返されます。 |
| 一致モード | 検索を完全一致で行うか、近似値も検索を行うかを指定します。0：完全一致で検索します。見つからない場合は「#N/A」となります。（既定値）-1：完全一致で検索します。見つからない場合は検索値より小さい次の値を一致とします。1：完全一致で検索します。見つからない場合は検索値より大きい次の値を一致とします。2：ワイルドカードの一致を検索します。 |
| 検索モード | 検索方法について指定します。1：先頭の項目から検索を行います。（既定値）-1：末尾の項目から検索を行います。2：昇順で並べ替えされた指定範囲のバイナリ検索を行います。-2：降順で並べ替えされた指定範囲のバイナリ検索を行います。 |
参考：XLOOKUP 関数 – Microsoft サポート
XLOOKUPで複数条件を設定する方法と例
XLOOKUPにおいて、複数条件を設定しデータ抽出を行う方法について紹介します。
複数条件に設定する方法は次の通りです。
- それぞれの条件の統合を行ったXLOOKUP関数を表に挿入する
- XLOOKUP関数の初めの因数にである「検索値」に「&」を用いる
| 行\列 | A | B | C |
| 1 | 店舗 | 商品名 | 値段（円） |
| 2 | A店 | シャンプー | 800 |
| 3 | A店 | コンディショナー | 1000 |
| 4 | B店 | シャンプー | 900 |
| 5 | B店 | コンディショナー | 800 |
| 6 | C店 | シャンプー | 600 |
| 7 | C店 | コンディショナー | 1200 |
| 行\列 | G | H | I |
| 1 | 店舗 | 商品名 | 値段（円） |
| 2 | A店 | コンディショナー |
上記表を例として、「A店」の「コンディショナー」の値段を抽出する方法について解説します。
①キー列を表に追加する
「商品名」の右隣に1列追加を行い、「店舗」と「商品名」の両者を「&」を用いて文字の結合をします。
=B2&C2
この数式を下のセルにコピーする。
| 行\列 | A | B | C | D |
| 1 | 店舗 | 商品名 | キー | 値段（円） |
| 2 | A店 | シャンプー | =B2&C2 | 800 |
| 3 | A店 | コンディショナー | 1000 | |
| 4 | B店 | シャンプー | 900 | |
| 5 | B店 | コンディショナー | 800 | |
| 6 | C店 | シャンプー | 600 | |
| 7 | C店 | コンディショナー | 1200 |
| 行\列 | A | B | C | D |
| 1 | 店舗 | 商品名 | キー | 値段（円） |
| 2 | A店 | シャンプー | A店シャンプー | 800 |
| 3 | A店 | コンディショナー | A店コンディショナー | 1000 |
| 4 | B店 | シャンプー | B店シャンプー | 900 |
| 5 | B店 | コンディショナー | B店コンディショナー | 800 |
| 6 | C店 | シャンプー | C店シャンプー | 600 |
| 7 | C店 | コンディショナー | C店コンディショナー | 1200 |
②第1引数の指定を行う
抽出結果を入力したいセルにXLOOKUP関数を入力し、第1引数に「店舗」と「商品名」の両者を「&」を用いて文字の結合をして入力する
=XLOOKUP(G2&H2,
③第2引数の指定を行う
①で追加したキー列をドラッグによって第2引数「検索範囲」への指定を行う。
必要に応じ、絶対参照も併用しましょう。
=XLOOKUP(G2&H2,$C$2:$C$7,
④第3引数の指定を行う
「値段」の列をドラッグし、「戻り範囲」に指定を行う。
必要に応じ、絶対参照も併用しましょう。
=XLOOKUP(G2&H2,$C$2:$C$7,$D$2:$D$7)
条件が3つになった場合も同様の手順でデータ抽出を行うことができます。
VLOOKUPとXLOOKUPの違いと選び方
VLOOKUPとXLOOKUPは、どちらもExcelでデータを検索するための関数ですが、いくつかの違いがあります。以下に、それらの違いと選び方について説明します。
| 機能の違い | Excelのバージョン | スプレッドシートでの利用 | |
| VLOOKUP | 垂直方向にデータを検索し、一致する値が見つかったときにそれに対応する列から値を返します。ただし、検索対象のデータは左側から右側に配置されている必要があります。 | すべてのバージョンのExcelで利用可能です。 | スプレッドシートでも利用可能 |
| XLOOKUP | 任意の方向にデータを検索できます。また、複数の範囲や条件を指定して検索することができ、見つからない場合のデフォルトの値を指定できます。XLOOKUPは、VLOOKUPよりも柔軟で強力な機能を提供します。 | Excel 365またはExcel 2019以降のバージョンでのみ利用できます。 | スプレッドシートでも利用可能 |
選び方
単純な垂直方向の検索が必要な場合や、データが左から右に配置されている場合は、VLOOKUPを使用します。
複雑な検索やデータの方向に制約がない場合は、XLOOKUPを使用することを検討しましょう。Excel 365やExcel 2019以降を利用している場合は、XLOOKUPの使用を推奨します。
XLOOKUPは、より柔軟で高機能な検索関数であり、新しい機能が利用可能な場合には優先的に使用することが推奨されます。ただし、環境によってはVLOOKUPを使う必要がある場合もありますので、状況に応じて適切な関数を選択してください。
その他の便利な検索関数と特殊な使い方
ここまで紹介した検索関数以外にも、便利な検索関数はあります。そのような便利な検索関数とその特殊な使い方について紹介します。
ある文字列（検索文字列）を対象文字列内で検索し、最初に出現する位置を左端から数えてその番号を返すのがFIND関数です。SEARCH関数も同様に文字列を検索しますが、FIND関数と異なり、大文字と小文字を区別し、ワイルドカード文字を使用することができません。
FIND関数の構文は以下の通りです。
=FIND(検索するテキスト, 対象テキスト, [開始位置])
| 項目 | 内容 |
| 検索文字列 | 検索したい文字列を指定します。 |
| 対象 | 検索されるテキストを指定します。 |
| 開始位置 | 検索を開始する位置を指定します。省略した場合、テキストの先頭から検索が開始されます。 |
複数条件に一致したデータを合計するSUMIF関数
SUMIF関数は、Excelで特定の条件を満たすセルの値の合計を計算するための関数です。具体的には、指定された範囲内のセルの値を条件でフィルタリングし、条件に一致するセルの値だけを合計します。
SUMIF関数の構文は以下の通りです。
=SUMIF(範囲, 検索条件, [合計範囲])
| 項目 | 内容 |
| 範囲 | 条件を満たすかどうかを検証するセル範囲を指定します。 |
| 検索条件 | 範囲内のセルに対して評価される条件を指定します。 |
| 合計範囲 | 条件を満たしたセルの値を合計する範囲を指定します。省略した場合、条件を満たした範囲の値が合計されます。 |
空白セルをカウントするCOUNTBLANK関数
COUNTBLANK関数は、指定された範囲内で空白のセル（値が空または空白文字列）の数を数えるために使用されます。
COUNTBLANK関数の構文は以下の通りです。
=COUNTBLANK(範囲)
| 項目 | 内容 |
| 範囲 | 空白のセルを数える対象となるセル範囲を指定します。 |
参考：COUNTBLANK 関数 – Microsoft サポート
よくある質問
Excelの検索関数に関するよくある質問について3つ紹介します。
エクセルで文字を検索する関数は？
エクセルで文字列を検索するための主な関数は、FIND関数とSEARCH関数です。これらの関数は、指定されたテキスト内で指定された文字列を検索し、その位置を返します。主な違いは大文字と小文字の区別の有無です。
FIND関数とSEARCH関数の構文は以下の通りです。
=FIND(検索文字列,対象,開始位置)
=SEARCH(検索文字列,対象,開始位置)
| 項目 | 内容 |
| 検索文字列 | 検索したい文字列を指定します。 |
| 対象 | 検索されるテキストを指定します。 |
| 開始位置 | 検索を開始する位置を指定します。省略した場合、テキストの先頭から検索が開始されます。 |
参考：
Excelで関数のセルを探すには？
Excelで関数のセルを探すには、「セルの検索」を活用すると良いでしょう。
セルの検索の方法は以下の通りです。
- 「ホーム」タブの「編集」グループにある「検索と選択」メニューから「検索」を選択する
- 検索ボックスに検索したい関数名を入力する
- 検索結果ウィンドウに関連するセルが一覧表示される
- 関数のあるセルに移動するには、該当するセルをダブルクリックする
この方法を使って、Excelのワークシート内で特定の関数を探すことができます。
関連記事：【2022年最新！】ExcelやGoogleスプレッドシートのガントチャートテンプレートを紹介！
Vlookupと似たような関数は？
VLOOKUP関数と似た関数として、INDEX関数とMATCH関数の組み合わせやXLOOKUP関数があります。
INDEX関数とMATCH関数を組み合わせた具体的な構文は以下のようになります。
=INDEX(データの範囲, MATCH(条件, 検索範囲, 0))
この式では、MATCH関数を使用して条件に一致するデータの行番号または列番号を見つけます。その行番号または列番号をINDEX関数の引数として指定してデータの抽出を行うことができます。
XLOOKUP関数は、指定された値または条件に基づいてデータを検索し、対応する結果を返します。
XLOOKUP関数の構文は以下の通りです。
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
まとめ
今回は、Excelの検索関数について解説しました。数多くの種類がある検索関数を、目的・用途に合わせて適切に活用することによりExcelの利便性や活用の幅を格段に向上させることができます。
Excelの検索関数に関する重要用語
| 関数＋構文 | 概要 |
| VLOOKUP=VLOOKUP(検索値, 範囲, 列番号, 検索の型) | 表の縦方向にデータを検索し、一致した値と同じ行にあるデータを返す |
| FIND=FIND(検索文字列,対象,開始位置) | 指定されたテキスト内で指定された文字列を検索し、その位置を返す |
| SEARCH=SEARCH(検索文字列,対象,開始位置) | 指定されたテキスト内で指定された文字列を検索し、その位置を返す |
| FILTER=FILTER(範囲, 条件,空の配列) | 指定された条件に基づいてデータをフィルタリングし、条件に一致する行または列のみの抽出を行う |
| ROUND=ROUND(数値, 桁数) | 5桁以下の小数点の桁数を丸めることができる |
| INDEX=INDEX(配列, 行番号, [列番号]) | 指定された範囲内から値を抽出する |
| MATCH=MATCH(検査値, 検査範囲, [照合の型]) | 指定された値が範囲内で最初に出現する位置を見つける |
| XLOOKUP=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード]) | 指定された値または条件に基づいてデータを検索し、対応する結果を返す |
| SUMIF=SUMIF(範囲, 検索条件, [合計範囲]) | 指定された範囲内のセルの値を条件でフィルタリングし、条件に一致するセルの値だけを合計する |
| COUNTBLANK=COUNTBLANK（範囲） | 指定された範囲内で空白のセル（値が空または空白文字列）の数を数える |
