ExcelVLOOKUP関数の例

VLOOKUP数式を使用してExcelリストからデータを取得する方法。問題が発生した場合は、VLOOKUPのトラブルシューティングを行います。 VLOOKUPの例を含むステップバイステップのビデオとExcelワークブック。

VLOOKUPビデオ

以下に示す製品価格ルックアップビデオでVLOOKUP式を作成する手順をご覧ください。書面による指示はビデオの下にあります。ビデオと一緒にフォローする製品価格ルックアップサンプルファイルを入手します（ファイル＃4をダウンロード）。

ルックアップテーブルの場所を選択する

各ルックアップを保存することをお勧めします。ブック内の別のワークシート上のテーブル。次に、ルックアップテーブルの行を追加および削除するときに、他のテーブルの行を誤って追加または削除することはありません。

この例では、ルックアップテーブルはProductsという名前のシートにあります。

ルックアップテーブルを作成する

ルックアップ式は、垂直方向に機能して列の下の値を検索することも、水平方向に機能して行全体の値を検索することもできます。。この例では、情報は縦方向に格納され、値は列の下に表示されます。後で、VLookup式を使用して縦方向のルックアップを実行します。

最初の行に見出しを入力します
最初の列には、ルックアップの基礎となる一意のキー値が含まれている必要があります。この例では、特定の製品コードの価格。
ワークシートに他のデータがある場合は、表の下部に少なくとも1つの空白行を残し、表の右側に1つの空白列を残して、他のデータからテーブルを検索します。

注：テーブルを参照しやすくするために、範囲に名前を付けることができます。手順は次のとおりです。範囲の名前付け▲TOP

VLOOKUP関数の引数

VLOOKUP関数には4つの引数があります。

lookup_value ：どの値を検索しますか？この例では、製品コードはセルA7にあり、そのprを検索します。製品名。
table_array：ルックアップテーブルはどこにありますか？相対参照（A2：C5）の代わりに絶対参照（$ A $ 2：$ C $ 5）を使用すると、数式に他のセルにコピーするのが簡単になります。または、ルックアップテーブルに名前を付け、名前で参照します。
col_index_num：返される値を持つ列はどれですか。この例では、製品名はルックアップテーブルの2番目の列にあります。
：完全に一致させますか？おおよその一致は大丈夫ですか？
最後の引数としてTRUEを使用するか、最後の引数を省略すると、近似一致が返されます。この例の最後の引数はFALSEであるため、製品コードが見つからない場合、結果は＃N / Aになります。（注：Excelはかなり寛容であり、FALSEの代わりに0を受け入れ、TRUEの代わりに1を受け入れます。）▲TOP

VLookup式を作成する

ルックアップテーブルを作成したら、ワークブックで他の数式を作成し、製品リストから情報を取得できます。たとえば、請求書を作成するために製品コードを入力すると、数式は製品テーブルから製品名または価格を取得します。

この例では、請求書はInvoiceという名前のシートに作成されます。 VLOOKUP式は、製品コードに完全に一致するものを見つけて、製品名を返す必要があります。

製品価格を計算するVLOOKUP式を作成するには、次の手順に従います。

請求書シートを選択します
セルA7に製品コードA23を入力します
セルB7で、VLOOKUP式を開始します：
= VLOOKUP（
をクリックしますセルA7で参照を入力します。
コンマを入力して、最初の引数を終了します
製品シートを選択します
セルA2：C5（製品リスト）を選択します）
F4キーを押して、セル参照を絶対に変更します。式は次のようになります。
= VLOOKUP（A7、Products！$ A $ 2：$ C $ 5
2番目の引数を終了するにはコンマを入力します。
製品名を含むルックアップテーブルの列である2を入力します。
コンマを入力して3番目の引数。
FALSEと入力して、製品コードに完全に一致するものが見つかったことを指定し、閉じ括弧を追加します。
式はlになります。 ike this：
= VLOOKUP（A7、Products！$ A $ 2：$ C $ 5,2、FALSE）
Enterキーを押して数式を完成させます。商品名が表示されます。

注：商品の価格を返すには、ルックアップテーブルの列3を参照するVLOOKUP式を作成します。たとえば、セルC7に次の数式を入力します。
= VLOOKUP（A7、Products！$ A $ 2：$ C $ 5,3、FALSE）▲TOP

値の範囲のVLOOKUP数式

状況によっては、近似一致が優先されるため、複数の値が同じ結果を返します。たとえば、学生の論文を採点する場合、85以上の成績のすべての論文はAグレードを取得する必要があります。

この数式を作成する手順を表示するには、以下に示すVLOOKUPビデオをご覧ください。書面による説明はビデオの下にあります。

この例では、ルックアップテーブルはGradesという名前のシートに作成されています。ルックアップテーブルを作成するには、列Aに各成績の最小スコアを入力します。列Bに一致する成績を入力します。スコアを昇順で並べ替えます。

セルA2：B6にはGradeListという名前が付けられました。

スコアはレポートカードという名前のシートに入力され、VLOOKUP数式でグレードが計算されます。

以下のスクリーンショットでは、数式が行6にコピーされており、セルC6に数式が表示されています。

VLOOKUPとMATCHの組み合わせ

列番号をVLOOKUP数式に入力する代わりに、MATCH関数を使用してルックアップテーブルで正しい列を見つけます。これにはいくつかの利点があります。

数式を柔軟にするため、ワークシート全体に数式を簡単にコピーできます。
新しい列が追加された場合の問題を防ぐことができます。ルックアップテーブル内、またはルックアップ列が再配置されている場合。

このビデオは手順を示しており、ビデオの下に別の例の説明が書かれています。

お使いのブラウザはこのフレームを表示できません。これがページへのリンクです

VLOOKUPと注文の詳細の一致

この例では、VLOOKUP式は、注文ID番号に基づいてルックアップテーブルから注文の詳細を返します。。これがtblOrdersという名前のルックアップテーブルです。

注：この例は、OrdersMATCHという名前のシートのサンプルワークブック＃1にあります。

これは、VLOOKUP式を含むワークシートです。注文ごとに地域、注文日、注文金額が必要なので、3つのVLOOKUP式が必要です。

数式に列番号を入力する場合は、列ごとに異なる数式が必要です。

地域：= VLOOKUP（$ B6、tblOrdersALL、2,0）
OrderDate：= VLOOKUP（$ B6、tblOrdersALL、3,0）
OrderAmt：= VLOOKUP（$ B6、tblOrdersALL、4,0）

MATCH関数

VLOOKUP数式に列番号を入力する代わりに、MATCH関数を使用できます。 MATCH関数は、リスト内のアイテムの位置を検索し、位置番号を返します。

以下のスクリーンショットでは、MATCH数式は、ルックアップテーブルの見出しセル（A1：D1）の「Region」の位置として2を返します。

= MATCH（ C5、Orders_ALL！$ A $ 1：$ D $ 1、0）

注：この手法を正しく機能させるには、 VLOOKUPシートの見出しは、ルックアップテーブルの見出しと正確に一致する必要があります。完全に一致させるために、VLOOKUP見出しセルはルックアップテーブル見出しセルにリンクされています。

VLOOKUPにMATCHを追加

VLOOKUP数式にMATCH関数を追加するには、入力された列番号

= VLOOKUP（$ B6、tblOrdersALL、2,0）

MATCH式：

= VLOOKUP（$ B6、tblOrdersALL、MATCH （C5、Orders_ALL！$ A $ 1：$ D $ 1、0）、0）

VLOOKUP式をコピーします

これで、列ごとに異なる数式を必要とする代わりに、数式をコピーして、すべての列で同じ数式を使用できるようになりました。各列で、その列の見出しセルを参照し、ルックアップテーブルでその位置を見つけます。

注：異なる書式で列全体に数式を入力する場合は、次の手順に従います。

コピーする数式を含むセルを選択します
選択したセルの塗りつぶしハンドル（右下隅の小さな正方形）をポイントします
マウスの右ボタンを押して、数式が必要な最後のセルまでドラッグします
マウスボタンを離し、書式なしで塗りつぶしをクリックします

IFとVLOOKUPの組み合わせ

IF式とVLookup式を使用して、見つかった場合は正確な値と空の文字列を返すことができます見つからない場合。

IFおよびVLOOKUP式を設定する手順を確認するには、この短いビデオをご覧ください。書面による指示はビデオの下にあります。

IFとVLOOKUPを組み合わせてエラーを非表示にするには、次の手順に従います。

請求書シートのセルA8に、製品を入力します。コードA28。セルB8のVLookup数式の4番目の引数がFALSEの場合、ルックアップテーブルの製品コードと完全に一致するものがないため、結果は＃N / Aになります。
VLookup数式をIF式（この例では製品リストに名前が付けられています）、ISNA関数を使用して＃N / Aエラーをチェックします：

= IF（ISNA（VLOOKUP（A8、ProductList、2、FALSE））、 “”、VLOOKUP（A8、ProductList、2、FALSE））

Enterキーを押すと、セルが空白で表示されます。完全に一致するものが見つからなかったため、VLookup式は＃N / Aを返しました。したがって、ISNA関数の結果はTRUEです。 IF式は、これを空の文字列に変換しました。

IFERRORとVLOOKUPを組み合わせる

この式を提案してくれたChipPearsonに感謝します。

Excel 2007では、新しい関数IFERRORが導入されました。。 VLookupでIFERROR式を使用して、いくつかのテーブルで値をチェックし、見つかった場合は関連情報を返すことができます。この例では、西、東、中央の3つの地域にオーダーシートがあります。各シートには、OrdersW、OrdersE、OrdersCという名前の付いた範囲があります。

Ordersという名前のシートで、Order IDを入力し、IFERRORを指定したVLOOKUPを使用して各名前付き範囲を確認できます。、および選択した注文に関する情報を表示します。

注文シートのセルB6で、OrderIDとして4を入力します。その注文は中央地域で行われました。
東地域のテーブルを簡単に確認するには、セルC6のVLOOKUP数式は次のようになります。
= VLOOKUP（B6、OrdersE、2、FALSE）
Enterキーを押すと、注文ID 4が東部地域の注文テーブルにないため、VLOOKUP式は＃N / Aを返します。
3つの領域のいずれかに配置されている場合は、各テーブルをチェックする数式が必要です。
最初のテーブルに注文IDが見つからない場合、数式は2番目のテーブルをチェックする必要があります。注文IDが2番目のテーブルにない場合テーブルの場合、3番目のテーブルをチェックする必要があります。注文IDが3番目のテーブルにない場合は、セルに「見つかりません」というメッセージが表示されます。
IFERROR数式を使用すると、値をチェックできます。次に、エラーが見つかった場合の対処方法を指定します。既存の数式でIFERRORを使用する場合、＃N / Aエラーの代わりに「見つかりません」を表示できます：
= IFERROR（VLOOKUP（B6、OrdersE、2、FALSE）、 “見つかりません”）
3つのテーブルすべてをチェックするには、次のIFERRORおよびVLOOKUP式を実行できます。

これにより、OrdersEテーブルがチェックされ、エラーが見つかった場合は、OrdersWテーブル、次にOrdersCがチェックされます。 3つのテーブルのいずれにもOrderIDが見つからない場合は、セルにNotFoundメッセージが表示されます。

IFERRORが使用できない古いバージョンのExcelで、次のコマンドを使用して複数のテーブルを確認することもできます。長い数式：

結合値のVLOOKUP

一部のテーブルでは、一意の値がない場合がありますルックアップテーブルの任意の列。たとえば、次の表では、ジャケットは列Aに2回リストされています。ただし、ジャケットとサイズの組み合わせごとに1つのレコードしかありません。4行目にはジャケットミディアム、5行目にはジャケットラージです。

大きなジャケットの価格を見つける必要がある場合、列Aのみに基づくVLOOKUPは、リストされている最初のジャケットの価格を返します（中）。ジャケットの価格を低く設定することになります。65.00ではなく60.00で販売します。

一意のルックアップ値を作成するには、テーブルの左側に新しい列を挿入し、数式を使用して製品とサイズを組み合わせることができます。セルA2では、数式はB2の値とパイプ文字およびC2の値を組み合わせたものです。

= B2 & “|” & C2

その数式をデータの最後の行にコピーして、各行の列Aに一意の値を設定します。

注：パイプ文字の代わりに、データに含まれていない別の文字を使用できます。

次に、 VLOOKUP数式で、積とサイズをLookup_valueとして組み合わせます。セルH1で、数式はF1の値とパイプ文字およびG1の値を組み合わせます。

= VLOOKUP（F1 &” | “& G1、$ A $ 2：$ D $ 5,4、FALSE）

注：価格は現在列3ではなく列4。▲TOP

VLOOKUP式のトラブルシューティング

VLOOKUP式探している値がルックアップテーブルにあるように見えても、＃N / Aを返す場合があります。これの一般的な原因は次のとおりです。

テキストと数字

スペース文字

その他の文字

テキストと数字

このエラーの一般的な原因は、値の1つが数値で、もう1つがテキストであるということです。たとえば、ルックアップテーブルに「123（テキスト）」が含まれ、ルックアップする値が123（数値）である場合があります。

可能であれば、次のいずれかを使用してテキストを数値に変換します。ここに示す方法：テキストを数値に変換する
データを変換できない場合は、以下のいずれかのソリューションを使用して、VLOOKUP式内のルックアップ値を変換できます。
1. ルックアップ値はテキスト、テーブルには数値
2. ルックアップ値は数値、テーブルにはテキスト
どの値がテキストで、どの値が数値かを確認するには、を参照してください。詳細な番号またはテキストのトラブルシューティング手順

1。ルックアップ値はテキストで、テーブルには数値が含まれています

ルックアップテーブルに数値が含まれ、ルックアップする値がテキストの場合は、次のような式を使用します。

= VLOOKUP （–A7、製品！$ A $ 2：$ C $ 5,3、FALSE）

二重単項（-）はテキストを数値に変換し、ルックアップ値が数値であっても正しく機能します。

2。ルックアップ値は数値であり、テーブルにはテキストが含まれています

ルックアップテーブルにテキストが含まれ、ルックアップする値が数値の場合は、次のような式を使用します。

= VLOOKUP （A7 & “”）、Products！$ A $ 2：$ C $ 5,3、FALSE）
または
= VLOOKUP（TEXT（A7、 “00000”）、Products！$ A $ 2：$ C $ 5,3、FALSE）

TEXT関数は数値をテキストに変換し、ルックアップ値がテキストであっても正しく機能します。最初の例では、&演算子はフォーマットされていない数値からテキスト文字列を作成します。 2番目の例では、先行ゼロでフォーマットされた数値（たとえば、00123）は、テキスト「数値」を先行ゼロと一致させます。

ルックアップテーブルにテキスト値がある場合のVLOOKUP問題を修正する手順を確認するには、この短いビデオチュートリアルをご覧ください。

一方の値にスペースがあり、もう一方の値にはスペースがありません

一致する値が見つからないもう1つの潜在的な原因は、スペースの違いです。値の1つに先頭のスペース（または末尾のスペース、埋め込みスペース）が含まれている場合と、含まれていない場合があります。値をテストするには、LEN関数を使用して各値の長さを確認します。

例：= LEN（A7）は、セルA7の文字数を返します。ルックアップテーブルの一致するセルの文字数と同じである必要があります。

可能であれば、不要な文字を削除します。スペースを指定すると、VLOOKUP式が正しく機能するはずです。スペースを削除できない場合は、VLOOKUPのTRIM関数を使用して、先頭、末尾、または重複するスペースを削除してください。例：

= VLOOKUP（TRIM（A7）、ProductList、2、FALSE）

その他の文字

TRIM関数だけでは問題が解決しない場合、次のいずれかの提案を試すことができます。

SUBSTITUTE関数
CLEAN関数
Web文字を削除するマクロ

SUBSTITUTE関数

SUBSTITUTE関数を使用して、不要な文字を削除します。Contexturesブログに例があります：TRIMとSUBSTITUTEを使用したExcelデータのクリーンアップ

CLEAN関数

VLOOKUPの問題を修正する別の方法は、CLEAN関数を使用することです。これにより、テキストから不要な文字を削除できます。CLEAN関数の詳細については、このContexturesブログ投稿を参照してください：30日間で30のExcel関数：29-CLEAN

一方の値にWeb文字が含まれ、もう一方の値には含まれない

Webページからデータをコピーした場合、HTMLの区切りなしのスペースが含まれている可能性があります（& nbsp）文字。DavidMcRitchieは、oとともにそれらを削除するマクロを作成しました。スペース文字

VLOOKUP数式を並べ替えるときの問題

VLOOKUP数式は最初は正しい結果を返す場合がありますが、アイテムのリストを並べ替えると間違った結果が表示されます。これは、ルックアップ値への参照にシート名が含まれている場合に発生する可能性があります。例：

= VLOOKUP（ “Order Form”！B5、Products！$ B $ 2：$ C $ 6,2、FALSE）

注：この問題は他の関数で発生する可能性がありますINDEX / MATCHルックアップ式などもあります。

このビデオを見て問題を修正する手順を確認し、VLOOKUP並べ替えの問題のサンプルファイルをダウンロードしてください。書面による指示はビデオの下にあります。

参照のシート名

このタイプの参照は、数式の作成中に別のシートをクリックすると作成されます。これを行うとすぐに、Excelは数式内の後続の参照にシート名を追加します。

スクリーンショット上記の場合、ドレスはセルB9にあり、セルC9は30ドルの正しい価格を示しています。

ただし、商品AZを並べ替えると、ドレスはセルB5に移動しますが、セルC5の数式は引き続き参照します。セルB9に。参照にはシート名が含まれているため、Excelは現在の行への参照を保持するのではなく、元の参照を保持します。セルC5は、ドレスではなくセーターの価格を示しています。 ▲TOP

問題の修正

問題を解決するには、不要なシート名を削除します。 VLOOKUPセル参照。セルC5の改訂された数式は次のとおりです。

= VLOOKUP（B5、Products！$ B $ 2：$ C $ 6,2、FALSE）

不要なシート名が削除された後、リストは安全に並べ替えることができ、アイテムごとに正しい結果が表示されます。

サンプルファイルをダウンロードする

1。 VLOOKUPサンプルワークブック（Excel 2007以降）を入手してください。 zipファイルはxlsx形式であり、マクロは含まれていません。

2。 Excel 2003の場合は、このバージョンのVLOOKUPサンプルファイルを入手してください。 zipファイルはxls形式であり、マクロは含まれていません。

3。 VLOOKUPでソートするときに発生する可能性のある問題を確認するには、このVLOOKUPソート問題のサンプルファイルを入手してください。 zipファイルはxlsx形式であり、マクロは含まれていません。

4。ビデオチュートリアルで使用されている製品価格ルックアップワークブックを入手してください。zipファイルはxlsx形式であり、マクロは含まれていません。 ▲TOP

その他のチュートリアル

VLOOKUP番号/テキストのトラブルシューティング

別のワークブックからのVLOOKUP

HLOOKUP

選択関数

ルックアップ-2つの基準

ルックアップ

インデックス/一致

ルックアップ関数の比較

関数リスト