ExcelのVLOOKUP関数
ExcelのVLOOKUP関数 は、テーブルの最初の列または垂直方向の範囲を照合して指定した値を検索し、同じ行の別の列から対応する値を返す強力なツールです。 VLOOKUP は非常に便利ですが、初心者にとっては理解しにくい場合があります。 このチュートリアルは、以下を提供することで VLOOKUP をマスターできるようにすることを目的としています。 引数の段階的な説明, 役立つ例 & よくあるエラーの解決策 VLOOKUP関数を使用するときに遭遇する可能性があります。
関連動画
引数のステップバイステップの説明
上のスクリーンショットに示されているように、VLOOKUP 関数は、指定された ID 番号に基づいて電子メールを検索するために使用されます。 この例での VLOOKUP の使用方法を、各引数を段階的に分けて詳しく説明します。
ステップ 1: VLOOKUP 関数を開始する
結果を出力するセル(この場合は H6)を選択し、次の内容を入力して VLOOKUP 関数を開始します。 数式バー.
=VLOOKUP(
ステップ 2: ルックアップ値を指定する
まず、VLOOKUP 関数でルックアップ値 (探している値) を指定します。 ここでは、特定の ID 番号 6 を含むセル G1005 を参照します。
=VLOOKUP(G6
ステップ 3: テーブル配列を指定する
次に、探している値と返したい値の両方を含むセル範囲を指定します。 今回はB6:E12の範囲を選択します。 式は次のようになります。
=VLOOKUP(G6,B6:E12
=VLOOKUP(G6,$B$6:$E$12
ステップ 4: 値を返す列を指定する
次に、値を返したい列を指定します。
この例では、ID 番号に基づいて電子メールを返す必要があるため、ここで数値 4 を入力して、データ範囲の XNUMX 列目の値を返すように VLOOKUP に指示します。
=VLOOKUP(G6,B6:E12,4
ステップ 5: 近似一致または完全一致を見つける
最後に、おおよその一致を探しているのか、完全に一致しているのかを決定します。
- を見つけるには 完全一致、あなたは使用する必要があります 間違った情報 最後の引数として。
- を見つけるには 近似一致には TRUE 最後の引数として指定するか、空白のままにします。
この例では、完全一致には FALSE を使用します。 式は次のようになります。
=VLOOKUP(G6,B6:E12,4,FALSE
Enter キーを押して結果を取得します
上の例で各引数を XNUMX つずつ説明することで、VLOOKUP 関数の構文と引数がより理解しやすくなりました。
構文と引数
=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])
- 参照値 (必須): 探している値 (実際の値またはセル参照)。 この値は table_array の最初の列になければならないことに注意してください。
- テーブル・アレイ (必須): セル範囲には、検索値の列と戻り値の列の両方が含まれます。
- 列インデックス (必須): 戻り値を含む列番号を整数で表します。 table_array の左端の列の番号 1 から始まります。
- 範囲検索 (オプション): VLOOKUP で近似一致を検索するか、完全一致を検索するかを決定する論理値。
- 近似一致 - この引数を次のように設定します TRUE, 1 またはそれを残す ブランク.
重要: 近似一致を見つけるには、VLOOKUP が間違った結果を返す場合に備えて、table_array の最初の列の値を昇順で並べ替える必要があります。 - 完全に一致 - この引数を次のように設定します 間違った情報 or 0.
- 近似一致 - この引数を次のように設定します TRUE, 1 またはそれを残す ブランク.
例 1: VLOOKUP での完全一致と近似一致
VLOOKUP を使用するときに完全一致と近似一致について混乱している場合は、このセクションがその混乱を解決するのに役立ちます。
VLOOKUP での完全一致
この例では、範囲 E6:E8 にリストされているスコアに基づいて対応する名前を検索するので、セル F6 に次の数式を入力し、オートフィル ハンドルを F8 までドラッグします。 この式では、最後の引数は次のように指定されます。 間違った情報 完全一致検索を実行します。
=VLOOKUP(E6,$B$6:$C$12,2,FALSE)
ただし、スコア 98 がデータ範囲の最初の列に存在しないため、VLOOKUP は #N/A エラー結果を返します。
VLOOKUPでのおおよその一致
引き続き上記の例を使用しますが、最後の引数を次のように変更すると、 TRUE, VLOOKUP は近似一致検索を実行します。 一致するものが見つからない場合は、検索値より小さい次に大きい値を見つけて、対応する結果を返します。
=VLOOKUP(E6,$B$6:$C$12,2,TRUE)
スコア 98 は存在しないため、VLOOKUP は 98 より小さい次に大きい値である 95 を見つけ、最も近い結果としてスコア 95 の名前を返します。
- この近似一致の場合、table_array の最初の列の値は昇順で並べ替える必要があります。 そうしないと、VLOOKUP が正しい値を返さない可能性があります。
- ここでは、複数のルックアップ値に対して一貫したデータのセットをすばやく参照するために、VLOOKUP 関数でテーブル配列 ($B$6:$C$12) をロックしました。
例 2: 複数の条件で VLOOKUP を使用する
このセクションでは、Excel で複数の条件で VLOOKUP を使用する方法を説明します。 以下のスクリーンショットに示されているように、指定された名前 (セル H5) と部門 (セル H6) に基づいて給与を検索しようとしている場合は、以下の手順に従ってください。
ステップ 1: ヘルパー列を追加してルックアップ列の値を連結する
この場合、ヘルパー列を作成して、 お名前 列と 部門 コラム。
- データ範囲の左側にヘルパー列を追加し、この列にヘッダーを与えます。 スクリーンショットを参照してください:
- このヘルパー列で、ヘッダーの下の最初のセルを選択し、次の数式を入力します。 数式バー、プレス 入力します.
=C6&" "&D6
ノート: この数式では、アンパサンド (&) を使用して XNUMX つの列のテキストを結合し、XNUMX つのテキストを作成します。- C6 のファーストネームです お名前 結合する列、 D6 の最初の部門です 部門 参加する列。
- これら XNUMX つのセルの値は、間にスペースを入れて連結されます。
- この結果セルを選択し、 オートフィルハンドル 下に押して、この数式を同じ列内の他のセルに適用します。
ステップ 2: 指定された条件で VLOOKUP 関数を適用する
結果を出力するセルを選択し(ここでは I7 を選択します)、フィールドに次の数式を入力します。 数式バー、を押してから 入力します.
=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
Result
- ヘルパー列は、データ範囲の最初の列として使用する必要があります。
- ここで、給与列はデータ範囲の XNUMX 番目の列であるため、次の数値を使用します。 5 式の列インデックスとして使用します。
- の基準に参加する必要があります I5 & I6 (I5&" "&I6) ヘルパー列と同じ方法で、連結された値を 参照値 式の引数。
- また、XNUMX つの条件を lookup_value 引数に直接入力し、スペースで区切ることもできます (条件がテキストの場合は、二重引用符で囲むことを忘れないでください)。
=VLOOKUP("Albee IT",B6:F12,5,FALSE)
- より良い代替案 - 複数の条件を使用して数秒で検索
マルチコンディションルックアップ の特徴 Kutools for Excel を使用すると、複数の条件を使用して数秒で簡単に検索できます。 今すぐ 30 日間の全機能を無料でお試しください!
一般的な VLOOKUP エラーと解決策
このセクションでは、VLOOKUP の使用時に発生する可能性のある一般的なエラーをリストし、それらを修正するための解決策を示します。
#N/A エラーが返されました
VLOOKUP で最も一般的なエラーは #N/A エラーです。これは、Excel が探している値を見つけることができなかったことを意味します。 VLOOKUP が #N/A エラーを返す理由をいくつか示します。
理由 1: ルックアップ値が table_array の最初の列にない
Excel VLOOKUP の制限の XNUMX つは、左から右へしか参照できないことです。 したがって、ルックアップ値は table_array の最初の列になければなりません。
以下のスクリーンショットに示すように、指定された役職に基づいて名前を返したいと考えています。 ここでのルックアップ値 (営業部長) は table_array の XNUMX 番目の列にあり、戻り値はルックアップ列の左側にあるため、VLOOKUP は #N/A エラーを返します。
ソリューション:
このエラーを修正するには、次のいずれかの解決策を適用できます。
- 列を並べ替えます
列を再配置して、ルックアップ列を table_array の最初の列に配置できます。 - INDEX 関数と MATCH 関数を一緒に使用する
ここでは、VLOOKUP の代わりに INDEX 関数と MATCH 関数を併用してこの問題を解決します。=INDEX(B6:B12,MATCH(F6,C6:C12,0))
- XLOOKUP 関数を使用する (Excel 365、Excel 2021 以降のバージョンで利用可能)
=XLOOKUP(F6,C6:C12,B6:B12)
理由 2: ルックアップ値がルックアップ列に見つからない (完全一致)
VLOOKUP が #N/A エラーを返す最も一般的な理由の XNUMX つは、探している値が見つからないことです。
以下の例に示すように、E98 で指定されたスコア 6 に基づいて名前を検索します。 ただし、このスコアはデータ範囲の最初の列に存在しないため、VLOOKUP は #N/A エラー結果を返します。
ソリューション:
このエラーを修正するには、次のいずれかの解決策を試してください。
- ルックアップ値より小さい次に大きい値を VLOOKUP 検索する場合は、最後の引数を変更します。 間違った情報 (完全一致) に TRUE (ほぼ一致)。 詳細については、を参照してください。 例 1: VLOOKUP を使用した完全一致と近似一致.
- 最後の引数の変更を回避し、検索値が見つからない場合にリマインダーを取得するには、IFERROR 関数内に VLOOKUP 関数を含めることができます。
=IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")
理由 3: ルックアップ値がルックアップ列の最小値より小さい (近似一致)
以下のスクリーンショットに示されているように、近似一致検索を実行しています。 探している値 (この場合は ID 番号 1001) はルックアップ列の最小値 1002 より小さいため、VLOOKUP は #N/A エラーを返します。
ソリューション:
ここでは XNUMX つの解決策を紹介します。
- ルックアップ値がルックアップ列の最小値以上であることを確認してください。
- ルックアップ値が見つからなかったことを Excel に通知させたい場合は、次のように VLOOKUP 関数を IFERROR 関数にネストします。
=IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")
理由 4: 数値はテキストとしてフォーマットされます
以下のスクリーンショットでわかるように、この例の #N/A エラー結果は、元のテーブルのルックアップ セル (G6) とルックアップ列 (B6:B12) の間のデータ型の不一致が原因です。 ここで、G6 の値は数値であり、B6:B12 の範囲の値はテキストとしてフォーマットされた数値です。
ソリューション:
この問題を解決するには、ルックアップ値を数値に変換し直す必要があります。 ここでは XNUMX つの方法を紹介します。
- 数値に変換機能を適用する
テキストを数値に変換したいセルをクリックし、このボタンを選択します セルの横にあるをクリックして選択します 数値に変換. - 便利なツールを適用してテキストと数値を一括変換する
テキストと数値の間で変換する の特徴 Kutools for Excel セル範囲をテキストから数値に、またはその逆に簡単に変換できます。 今すぐ 30 日間の全機能を無料でお試しください!
理由 5: VLOOKUP 数式を他のセルにドラッグすると、table_array が定数ではなくなります
以下のスクリーンショットに示されているように、E6 と E7 には 6 つのルックアップ値があります。 F6 で最初の結果を取得した後、VLOOKUP 数式をセル F7 から F6 にドラッグすると、#N/A エラー結果が返されます。 これは、セル参照 (B12:C7) がデフォルトで相対的であり、行を下に移動するにつれて調整されるためです。 テーブル配列は B13:C73 に移動され、ルックアップ スコア XNUMX は含まれなくなりました。
ソリューション
テーブル配列をロックして一定に保つ必要があります。 $ セル参照の行と列の前に署名します。 Excel の絶対参照について詳しくは、次のチュートリアルをご覧ください。 Excel絶対リファレンス(作り方と使い方).
#VALUE エラーが返されました
次の条件により、VLOOKUP が #VALUE エラー結果を返す可能性があります。
理由 1: ルックアップ値が 255 文字を超えています
以下のスクリーンショットに示すように、セル H4 のルックアップ値が 255 文字を超えているため、VLOOKUP は #VALUE エラー結果を返します。
ソリューション:
この制限を回避するには、より長い文字列を処理できる別の検索関数を適用できます。 次の式のいずれかを試してください。
- インデックスとマッチ:
=INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
- XLOOKUP関数 (Excel 365、Excel 2021 以降のバージョンで利用可能):
=XLOOKUP(H4,B5:B11,E5:E11)
理由 2:col_index 引数が 1 未満である
列インデックスは、返したい値を含むテーブル配列内の列番号を指定します。 この引数は、テーブル配列内の有効な列に対応する正の数である必要があります。
1 未満の列インデックス (つまり、XNUMX または負の値) を入力すると、VLOOKUP はテーブル配列内の列を見つけることができません。
ソリューション
この問題を解決するには、VLOOKUP 式の列インデックス引数が、テーブル配列内の有効な列に対応する正の数であることを確認してください。
#REF エラーが返されました
このセクションでは、VLOOKUP が #REF エラーを返す理由の XNUMX つを列挙し、この問題の解決策を示します。
理由:col_index 引数が列の数より大きいです。
以下のスクリーンショットでわかるように、テーブル配列には 4 つの列しかありません。 ただし、VLOOKUP 式で指定した列インデックスは 5 であり、テーブル配列の列数よりも大きくなります。 その結果、VLOOKUP は列を見つけることができず、最終的に #REF エラーを返します。
ソリューション:
- 正しい列番号を指定してください
VLOOKUP 式の列インデックス引数が、テーブル配列内の有効な列に対応する数値であることを確認してください。
- 指定された列ヘッダーに基づいて列番号を自動的に取得します。
テーブルに多くの列が含まれている場合、正しい列インデックス番号を決定するのが困難になることがあります。 ここでは、VLOOKUP 関数に MATCH 関数をネストして、確実な列ヘッダーに基づいて列の位置を見つけることができます。
=VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
Note: 上の式では、 MATCH("メール",B5:E5, 0) 関数は、「」の列番号を取得するために使用されます。メール日付範囲 B6:E12 の列。ここでの結果は 4 で、VLOOKUP 関数のcol_index として使用されます。
間違った値が返される
VLOOKUP が正しい結果を返さない場合は、次の理由が考えられます。
理由 1: ルックアップ列が昇順に並べ替えられていない
最後の引数を次のように設定した場合 TRUE (または 空のままにしておきました) 近似一致の場合、ルックアップ列が昇順で並べ替えられていない場合、結果の値が正しくない可能性があります。
ソリューション
ルックアップ列を昇順に並べ替えると、この問題を解決できます。 これを行うには、以下の手順に従ってください。
- ルックアップ列でデータ セルを選択し、 且つ タブをクリックします。 最小から最大に並べ替え セクションに 並べ替えとフィルター グループ。
- ソート警告 ダイアログボックスで 選択範囲を拡大 オプションをクリックして OK.
理由 2: 列が挿入または削除される
以下のスクリーンショットに示されているように、最初に返したかった値はテーブル配列の 4 番目の列にあるため、col_index 番号を XNUMX に指定します。新しい列が挿入されると、結果の列はテーブルの XNUMX 番目の列になります。配列が原因で、VLOOKUP が間違った列から結果を返します。
ソリューション:
ここでは XNUMX つの解決策を紹介します。
- 戻り列の位置に一致するように列インデックス番号を手動で変更できます。 ここの式は次のように変更する必要があります。
=VLOOKUP(H6,B6:F12,5,FALSE)
- この例の Email 列などの certian 列からの結果を常に返したい場合。 次の数式は、列がテーブル配列に挿入されるかテーブル配列から削除されるかに関係なく、指定された列ヘッダーに基づいて列インデックスを自動的に照合するのに役立ちます。
=VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)
その他の機能に関する注意事項
- VLOOKUP は左から右に値のみを検索します。
ルックアップ値は一番左の列にあり、結果値はルックアップ列の右側の任意の列にある必要があります。 - 最後の引数を空白のままにすると、VLOOKUP はデフォルトで近似一致を使用します。
- VLOOKUP は、大文字と小文字を区別しない検索を実行します。
- 複数の一致の場合、VLOOKUP は、テーブル配列内の行の順序に基づいて、テーブル配列内で見つかった最初の一致のみを返します。
関連記事
Excel の初心者および上級ユーザー向けの 20 以上の VLOOKUP の例
このチュートリアルでは、Excel で Vlookup 関数を使用する方法を、数十の基本的な例と高度な例とともに段階的に説明します。
右から左への VLOOKUP
他の列で特定の値を検索し、左側の相対値を返したい場合は、このチュートリアルのメソッドを使用すると、このタスクを実行できます。
下から上への Vlookup
このチュートリアルでは、一致する値を下から上に検索するのに役立つ XNUMX つの方法を提供します。
大文字と小文字を区別する vlookup を実行する
Excel で大文字と小文字を区別する VLOOKUP を実行したい場合は、このチュートリアルの方法が役に立ちます。
VLOOKUP はソースの書式を維持します
このチュートリアルでは、Excel で Vlookup を実行するときに、結果のセルのすべての書式設定を保持するのに役立つ方法を提供します。
最高のオフィス生産性向上ツール
🤖 | Kutools AI アシスタント: 以下に基づいてデータ分析に革命をもたらします。 インテリジェントな実行 | コードを生成 | カスタム数式の作成 | データを分析してグラフを生成する | Kutools関数を呼び出す... |
人気の機能: 重複を検索、強調表示、または識別する | 空白行を削除する | データを失わずに列またはセルを結合する | 数式なしのラウンド ... | |
スーパールックアップ: 複数の基準の VLookup | 複数の値の VLookup | 複数のシートにわたる VLookup | ファジールックアップ .... | |
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する | 依存関係のドロップダウン リスト | 複数選択のドロップダウンリスト .... | |
列マネージャー: 特定の数の列を追加する | 列の移動 | Toggle 非表示列の表示ステータス | 範囲と列の比較 ... | |
注目の機能: グリッドフォーカス | デザインビュー | ビッグフォーミュラバー | ワークブックとシートマネージャー | リソースライブラリ (自動テキスト) | 日付ピッカー | ワークシートを組み合わせる | セルの暗号化/復号化 | リストごとにメールを送信する | スーパーフィルター | 特殊フィルター (太字/斜体/取り消し線をフィルター...) ... | |
上位 15 のツールセット: 12 テキスト 工具 (テキストを追加, 文字を削除する、...) | 50+ チャート 種類 (ガントチャート、...) | 40+ 実用的 式 (誕生日に基づいて年齢を計算する、...) | 19 挿入 工具 (QRコードを挿入, パスから画像を挿入、...) | 12 変換 工具 (数字から言葉へ, 通貨の換算、...) | 7 マージ&スプリット 工具 (高度な結合行, 分割セル、...) | ... もっと |
Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。 最も必要な機能を入手するにはここをクリックしてください...
Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作業をはるかに簡単にします
- Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
- 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
- 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!