業務において効率的なデータ検索を実現したい場合、検索関数の活用は欠かすことができません。
検索関数は、指定した任意の値に基づいて表の検索を行うことができるため、表に入力されているデータの抽出を行う際に重宝されます。
今回は、検索関数の一つである「〜LOOKUP関数」について詳しく紹介します。
チャット一括管理アプリ「OneChat」が今なら初月無料!!
OneChatのダウンロードはこちらから
ExcelでのLOOKUP関数の基本
はじめに、Excelにおける様々な「〜LOOKUP関数」の基本的な情報について理解を深めていきましょう。
VLOOKUP関数とHLOOKUP関数の違い
VLOOKUP関数とHLOOKUP関数の違いについてみていきます。
| VLOOKUP | HLOOKUP | |
| 書式 | =VLOOKUP(検索値, 範囲, 列番号, [検索の型]) | =HLOOKUP(検索値, 範囲, 行番号, [検索の型]) |
| 検索する方向 | 縦方向（垂直方向:Vertical） | 横方向（水平方向:Horizontal） |
| 返す値の位置 | 検索を行った列と同一の列 | 検索を行った行と同様の行 |
VLOOKUP関数とHLOOKUP関数、どちらも指定された値を特定の方向で検索する関数です。検索する値の位置の指定方法や返す行・列の指定、検索方法（検索の型）の指定は、対応する引数で指定することが可能である点は共通となります。
参考：
VLOOKUP関数とXLOOKUP関数の比較
VLOOKUP関数とXLOOKUP関数の違いについてみていきましょう。
| VLOOKUP | XLOOKUP | |
| 書式 | =VLOOKUP(検索値, 範囲, 列番号, [検索の型]) | =XLOOKUP(検索値, 範囲, 戻り配列,[見つからない場合], [一致モード], [検索モード]) |
| 検索する方向 | 縦方向（垂直方向:Vertical）のみ | 検索する方向の制限はなし |
| 検索範囲 | 特定の1つの範囲 | 複数の範囲を指定可能 |
| 返す値の位置 | 検索を行った列と同一の列 | 返す値の範囲を引数で指定できる |
| 利用可能なExcelのバージョン | すべてのExcelのバージョンで利用することができる | Excel 365またはExcel 2019以降のバージョンから利用することができる |
XLOOKUP関数は、VLOOKUP関数と比べて検索する方向や検索範囲などの制限がないため、より柔軟に目的の値を検索することができます。特に、複数の範囲を指定して検索することができるため、検索条件に当てはまる値を取得したい場合にはとても便利です。
参考：XLOOKUP 関数 – Microsoft サポート
ExcelのVLOOKUP関数使い方マスター
ExcelのVLOOKUP関数の使い方についてマスターしていきましょう。
VLOOKUP関数の基本的な使い方
VLOOKUP関数とは、Excelに入力されているデータを表の縦方向に検索を行い、指定した条件と一致した値と同じ行に入力されているデータを表示させることのできる関数です。
VLOOKUP関数の基本的な書式は次の通りです。
=VLOOKUP(検索値, 範囲, 列番号, [検索の型])
| 項目 | 内容 |
| 検索値 | 検索の対象とする値を指定します。数字やセル参照以外の方法（文字列など）で検索値を指定する際は、「”〇〇”」のように指定したい内容を「”」で囲むことにより指定できます。入力されている文字や数字の全角・半角を区別することができますが、英字の大文字・小文字を区別することはできません。 |
| 範囲 | 第一引数で指定した検索値の検索を行う範囲を指定します。返る値の列が指定した範囲に含まれていること、検索値が左端の列に含まれている必要があります。 |
| 列番号 | 第二引数で指定した範囲から、計算の結果として表示したい値が左から数えて何列目にあるのかを指定します。 |
| 検索の型 | 「TRUE」または「FALSE」で指定します。前者では近似照合が行われるため、指定した範囲の列の中から、検索値に一番近い値を対象とします。後者では完全一致による検索が行われるため、指定した範囲の列の中から、検索値と一致した値を対象とします。第四引数を省略した場合は、「TRUE」で関数計算が行われます。 |
VLOOKUP関数で複数条件を設定する方法
VLOOKUP関数で複数条件を設定する方法についてみていきましょう。
前提として、VLOOKUP関数は特定の1つの検索条件に対して値を返すことができるかんすうであるため、そのままでは複数条件を設定することはできません。そのため、複数条件を設定する際は工夫を凝らす必要があります。
| 行\列 | A | B | C |
| 1 | 学年 | 姓 | 点数 |
| 2 | 3年 | 田中 | 75 |
| 3 | 2年 | 山田 | 82 |
| 4 | 3年 | 鈴木 | 61 |
| 5 | 1年 | 佐藤 | 94 |
| 6 | 1年 | 高橋 | 52 |
| 7 | 2年 | 渡辺 | 91 |
例えば、上記のデータをもとにして、「1年」「佐藤」の複数条件を設定して該当データの点数をVLOOKUP関数を用いて抽出する手順について解説します。
| 行\列 | F | G |
| 1 | 学年＋姓 | 点数 |
| 2 | 1年佐藤 |
1.「学年」と「姓」を組み合わせたデータが入力された列を追加する
今回の条件である「学年」と「姓」を組み合わせたデータが入力された列を追加します。VLOOKUP関数の特性上、列は左端に追加する必要があります。この際、結合したいデータが入力されているセル（この場合「学年」と「姓」）を参照して、その二つを「&」で繋げると結合が簡単に行えます。
=B2&C2
入力した数式をA2以下のセルにオートフィルを用いてコピーしましょう。
| 行\列 | A | B | C | D |
| 1 | 学年＋姓 | 学年 | 姓 | 点数 |
| 2 | =B2&C2 | 3年 | 田中 | 75 |
| 3 | 2年 | 山田 | 82 | |
| 4 | 3年 | 鈴木 | 61 | |
| 5 | 1年 | 佐藤 | 94 | |
| 6 | 1年 | 高橋 | 52 | |
| 7 | 2年 | 渡辺 | 91 |
| 行\列 | A | B | C | D |
| 1 | 学年＋姓 | 学年 | 姓 | 点数 |
| 2 | 3年田中 | 3年 | 田中 | 75 |
| 3 | 2年山田 | 2年 | 山田 | 82 |
| 4 | 3年鈴木 | 3年 | 鈴木 | 61 |
| 5 | 1年佐藤 | 1年 | 佐藤 | 94 |
| 6 | 1年高橋 | 1年 | 高橋 | 52 |
| 7 | 2年渡辺 | 2年 | 渡辺 | 91 |
2.「学年＋姓」のセルを検索するVLOOKUP関数の数式を作成する
「G2」セルにVLOOKUP関数の数式を入力します。この際、①で作成した「学年＋姓」のセルを検索するように範囲指定をしましょう。
=VLOOKUP(F2, A2:D7, 4, FALSE)
| 行\列 | F | G |
| 1 | 学年＋姓 | 点数 |
| 2 | 1年佐藤 | =VLOOKUP(F2, A2:D7, 4, FALSE) |
以上が、VLOOKUP関数にて複数条件を設定してデータの抽出を行う一例です。
| 行\列 | A | B | C | D |
| 1 | 学年＋姓 | 学年 | 姓 | 点数 |
| 2 | 3年田中 | 3年 | 田中 | 75 |
| 3 | 2年山田 | 2年 | 山田 | 82 |
| 4 | 3年鈴木 | 3年 | 鈴木 | 61 |
| 5 | 1年佐藤 | 1年 | 佐藤 | 94 |
| 6 | 1年高橋 | 1年 | 高橋 | 52 |
| 7 | 2年渡辺 | 2年 | 渡辺 | 91 |
| 行\列 | F | G |
| 1 | 学年＋姓 | 点数 |
| 2 | 1年佐藤 | 94 |
VLOOKUP関数で#N/Aエラーの原因・対応方法
VLOOKUP関数を使用している中で、「#N/Aエラー」が発生してしまうことはよくあることです。その場合も慌てることなく対処できるように、頻繁におこるエラー原因とその対処方法について見ていきましょう。
| 原因 | 対処法 |
| 完全一致を指定した際に、該当データが存在しないため | 検索方法を変更しましょう。範囲内に指定したデータと完全に一致するデータが存在するがエラーとなる場合は、検索値にスペースなどが含まれていないか確認しましょう。 |
| 検索値が指定の範囲の左端の列にないため | 検索値は第二引数で指定した範囲の最も左の列に存在している必要がVLOOKUP関数ではあります。左端の列に検索値が含まれていない場合は調整しましょう。 |
| 近似一致を指定した際に、参照列が昇順とされていないため | 近似一致を指定した場合は、参照列が昇順となっている必要があります。その場合は並び替えることで正常な結果を表示可能です。 |
| 近似一致を指定した際に、検索値が指定した範囲の最小値よりも小さいため | 検索値の変更を必要に応じて行いましょう。変更ができない場合は、MATCH関数とINDEX関数を組み合わせるなどの代替手段で可能です。 |
ExcelのHLOOKUP関数活用術
ExcelのHLOOKUP関数の使い方について理解を深めましょう。
HLOOKUP関数の基本的な使い方
HLOOKUP関数とは、Excelに入力されているデータを表の横方向に検索を行い、指定した条件と一致した値と同じ行に入力されているデータを表示させることのできる関数です。
HLOOKUP関数の基本的な書式は次の通りです。
=HLOOKUP(検索値, 範囲, 行番号, [検索の型])
| 項目 | 内容 |
| 検索値 | 検索の対象とする値を指定します。数字やセル参照以外の方法（文字列など）で検索値を指定する際は、ダブルクォーテーションで指定したい内容を囲むことにより指定できます。HLOOKUP関数の検索値は、入力されている文字や数字の全角・半角を区別することができますが、英字の大文字・小文字を区別することはできません。 |
| 範囲 | 第一引数で指定した検索値の検索を行う範囲を指定します。この際、検索値が左端の列に含まれていること、返る値の列が指定した範囲に含まれている必要があります。 |
| 行番号 | 第二引数で指定した範囲から、計算の結果として表示したい値が上から数えて何行目にあるのかを指定します。 |
| 検索の型 | 「TRUE」または「FALSE」で指定します。前者では近似照合が行われるため、指定した範囲の列の中から、検索値に一番近い値を対象とします。後者では完全一致による検索が行われるため、指定した範囲の列の中から、検索値と一致した値を対象とします。第四引数を省略した場合は、「TRUE」で関数計算が行われます。 |
HLOOKUP関数で最適な結果を得る方法
HLOOKUP関数で最適な結果を得るためには、次の3つのポイントについて意識しましょう。
1.範囲は正確に指定する
HLOOKUP関数の検索範囲は、検索値が含まれている行が範囲内に存在しなければなりません。範囲内に検索値が含まれない場合はエラー表示となるため注意しましょう。
2. 行番号（返す値の位置）は正確に指定する
行番号の指定を正確に行わなければ、表示させたい結果と別の内容が表示されてしまいます。HLOOKUP関数では、指定した範囲の最初の行を起点として行番号を指定します。目的のデータが存在する行番号を正確に指定しましょう。
3.範囲を昇順に並び替えておく
完全一致にて検索する場合はその限りではありませんが、近似一致で検索する場合は検索範囲が昇順に並び替えられていなければなりません。昇順となっていない場合は、あらかじめ並び替えておきましょう。
ExcelのXLOOKUP関数徹底解説
ExcelのXLOOKUP関数の使い方について紹介します。
XLOOKUP関数の基本的な使い方
XLOOKUP関数とは、Excel365、Excel 2019以降のバージョンに追加された検索関数です。この関数では、VLOOKUP関数やHLOOKUP関数と異なり、検索値の配置などが固定されていないため、柔軟に検索を行うことができる点が特徴です。
XLOOKUP関数の基本的な書式は次の通りです。
=XLOOKUP(検索値, 範囲, 戻り配列,[見つからない場合], [一致モード], [検索モード])
| 項目 | 内容 |
| 検索値 | 検索の対象とする値を指定します。数字やセル参照以外の方法（文字列など）で検索値を指定する際は、ダブルクォーテーションで指定したい内容を囲むことにより指定できます。 |
| 検索範囲 | 第一引数で指定した検索値の検索を行う範囲を指定します。 |
| 戻り配列 | 検索値と一致するデータが検索範囲にあった際に結果として表示するデータの範囲や配列を指定します。 |
| 見つからない場合 | 検索値と一致するデータが検索範囲になかった場合に表示させる内容を指定します。 |
| 一致モード | 一致の種類について指定します。 |
| 検索モード | 検索方法について指定します。 |
XLOOKUP関数による簡単な転記術
XLOOKUP関数による簡単な転記術について例をもとに紹介します。
単価表
| 行\列 | A | B |
| 1 | 商品名 | 単価 |
| 2 | 商品A | 570 |
| 3 | 商品B | 360 |
| 4 | 商品C | 820 |
| 5 | 商品D | 650 |
| 6 | 商品E | 980 |
| 7 | 商品F | 260 |
売上明細
| 行\列 | A | B | C | D | E |
| 1 | 日付 | 商品名 | 単価 | 売上数 | 金額 |
| 2 | 2024/4/1 | 商品B | 20 | 0 | |
| 3 | 2024/4/1 | 商品A | 15 | 0 | |
| 4 | 2024/4/2 | 商品D | 20 | 0 | |
| 5 | 2024/4/3 | 商品D | 10 | 0 | |
| 6 | 2024/4/4 | 商品F | 30 | 0 | |
| 7 | 2024/4/4 | 商品C | 25 | 0 | |
| 8 | 2024/4/5 | 商品E | 15 | 0 |
例えば、上記売上明細に、別シートに入力されている単価表をもとにした単価を転記して売上明細を完成させる手順について解説します。
1.「C2」セルにXLOOKUP関数を入力する
「C2」セルに「B2」セルに入力されている商品名を検索値とした単価を単価表から抽出するXLOOKUP関数を入力します。
この際、オートフィルでコピーしても値がずれないように、第二・第三引数は絶対参照としておきましょう。
=XLOOKUP(B2, ‘単価表’!$A$2:$A$7, ‘単価表’!$B$2:$B$7)
単価表に入力されている項目が増えた場合に引数に入力されている数値をいちいち書き換えなくてもいいように、列で商品名と単価を指定することもできます。この場合は、第二・第三引数を絶対参照とする必要はありません。
=XLOOKUP(B2, ‘単価表’!A:A, ‘単価表’!B:B)
2.「B2」セルに入力した数式をコピーする
「B2」セルに入力した数式を「B2」セル以下のセルにコピーしましょう。その際、金額欄に「単価」と「売上数」を乗算した計算式を入力すると自動で「金額」も計算されます。
売上明細
| 行\列 | A | B | C | D | E |
| 1 | 日付 | 商品名 | 単価 | 売上数 | 金額 |
| 2 | 2024/4/1 | 商品B | =XLOOKUP(B2, ‘単価表’!A:A, ‘単価表’!B:B) | 20 | 0 |
| 3 | 2024/4/1 | 商品A | 15 | 0 | |
| 4 | 2024/4/2 | 商品D | 20 | 0 | |
| 5 | 2024/4/3 | 商品D | 10 | 0 | |
| 6 | 2024/4/4 | 商品F | 30 | 0 | |
| 7 | 2024/4/4 | 商品C | 25 | 0 | |
| 8 | 2024/4/5 | 商品E | 15 | 0 |
売上明細
| 行\列 | A | B | C | D | E |
| 1 | 日付 | 商品名 | 単価 | 売上数 | 金額 |
| 2 | 2024/4/1 | 商品B | 360 | 20 | 7,200 |
| 3 | 2024/4/1 | 商品A | 570 | 15 | 8,550 |
| 4 | 2024/4/2 | 商品D | 650 | 20 | 13,000 |
| 5 | 2024/4/3 | 商品D | 650 | 10 | 6,500 |
| 6 | 2024/4/4 | 商品F | 260 | 30 | 7,800 |
| 7 | 2024/4/4 | 商品C | 820 | 25 | 20,500 |
| 8 | 2024/4/5 | 商品E | 980 | 15 | 14,700 |
XLOOKUP関数の実用的な活用例
XLOOKUP関数の実用的な活用例についていくつか紹介します。
| 活用例 | 詳細 |
| 顧客の管理 | 顧客名や顧客IDを用いて、顧客のデータベースから顧客の連絡先や住所といった顧客情報を取得する。 |
| 予算の管理 | 部門名や部門IDを用いて、予算のデータベースから部門ごとの予算実績などの予算情報を取得する。 |
| プロジェクトの管理 | プロジェクト名やプロジェクトIDを用いて、プロジェクトの進捗や予算といったプロジェクトの情報を取得する |
| 在庫の管理 | 商品名や商品コードを用いて、データベースより在庫数や価格といった商品情報を取得する。 |
| 売上データを分析する | 売上データを用いて、顧客情報や製品情報の取得を行い、売上データの分析に用いることができます。 |
よくある質問
検索関数「〜LOOKUP関数」に関連するよくある質問について紹介します。
エクセルのVlookupとXlookupの違いは何ですか？
VLOOKUP関数とXLOOKUP関数関数の違いを以下の表に簡単にまとめました。
| VLOOKUP | XLOOKUP | |
| 書式 | =VLOOKUP(検索値, 範囲, 列番号, [検索の型]) | =XLOOKUP(検索値, 範囲, 戻り配列,見つからない場合,一致モード,検索モード) |
| 検索する方向 | 縦方向（垂直方向:Vertical）のみ | 検索する方向の制限はなし |
| 検索範囲 | 特定の1つの範囲 | 複数の範囲を指定可能 |
| 返す値の位置 | 検索を行った列と同一の列 | 返す値の範囲を引数で指定できる |
| 利用可能なExcelのバージョン | すべてのExcelのバージョンで利用することができる | Excel 365またはExcel 2019以降のバージョンから利用することができる |
XLOOKUP関数は、VLOOKUP関数と比べて検索する方向や検索範囲などの制限がないため、より柔軟に目的の値を検索することができます。特に、複数の範囲を指定して検索することができるため、検索条件に当てはまる値を取得したい場合にはとても便利です。
エクセルのHlookupとVlookupの違いは何ですか？
HLOOKUP関数とVLOOKUP関数の違いを以下の表に簡単にまとめました。
| HLOOKUP | VLOOKUP | |
| 書式 | =HLOOKUP(検索値, 範囲, 行番号, [検索の型]) | =VLOOKUP(検索値, 範囲, 列番号, [検索の型]) |
| 検索する方向 | 横方向（水平方向:Horizontal） | 縦方向（垂直方向:Vertical） |
| 返す値の位置 | 検索を行った行と同様の行 | 検索を行った列と同一の列 |
HLOOKUP関数とVLOOKUP関数、どちらも指定された値を特定の方向で検索する関数です。検索する値の位置の指定方法や返す行・列の指定、検索方法（検索の型）の指定は、対応する引数で指定することが可能である点は共通となります。
エクセルのLookupとVlookupの違いは何ですか？
LOOKUP関数とVLOOKUP関数の違いを以下の表に簡単にまとめました。
| LOOKUP | VLOOKUP | |
| 書式 | =LOOKUP(検索値, 検索範囲, [対応範囲]) | =VLOOKUP(検索値, 範囲, 列番号, [検索の型]) |
| 検索する方向 | 検索する方向の制限はなし | 縦方向（垂直方向:Vertical）のみ |
| 検索範囲 | 1つの行または1つの列 | 特定の1つの範囲 |
| 返す値の位置 | 検索範囲と同様の範囲 | 検索を行った列と同一の列 |
| 検索方法 | 近似一致 | 完全一致と近似一致を選択可能 |
LOOKUP関数を現在のバージョンのエクセルでも用いることは可能です。しかし、機能強化版の関数であるVLOOKUP関数やHLOOKUP関数、XLOOKUP関数といった関数が存在するため、現在では非推奨となっています。
まとめ
今回は、検索関数の中でも「〜LOOKUP関数」について詳しく紹介しました。
今回紹介した関数は、業務内でデータ検索を効率的に行おうと考えた場合に欠かすことのできないと言っても過言ではない関数たちです。
この記事を参考にして、これらの関数の使い方をマスターしてください。
Excellookup関数に関する重要用語
| 用語 | 説明 |
| 絶対参照 | セルの移動やコピーを行った場合でも、参照先のセルが変更されずに固定した状態で参照する方法です。絶対参照では、セルの行・列番号を「$」記号を用いて指定します。 |
| 相対参照 | セルの移動やコピーを行った場合に、参照もとのセルから相対的な位置で指定されたセルを参照する方法です。 |
