Note: The other languages of the website are Google-translated. Back to English
ログイン  \/ 
x
or
x
今すぐ登録  \/ 
x

or

Excelのいくつかの基本的な例と高度な例を含むVLOOKUP関数

Excelでは、VLOOKUP関数はほとんどのExcelユーザーにとって強力な関数であり、データ範囲の左端の値を検索し、以下のスクリーンショットのように指定した列から同じ行に一致する値を返すために使用されます。 。 このチュートリアルでは、Excelの基本的な例と高度な例を使用してVLOOKUP関数を使用する方法について説明します。

目次:

1. VLOOKUP関数の紹介–構文と引数

2.基本的なVLOOKUPの例

3.高度なVLOOKUPの例

4. VLOOKUPに一致する値は、セルのフォーマットを維持します

5.VLOOKUPサンプルファイルをダウンロードします


VLOOKUP関数の紹介–構文と引数

VLOOKUP関数の構文:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

引数:

参照値:検索したい値。 table_array範囲の最初の列にある必要があります。

テーブル・アレイ:ルックアップ値列と結果値列が配置されているデータ範囲またはテーブル。

Col_index_num:一致した値が返される列の数。 これは、テーブル配列の左端の列から1で始まります。

範囲検索:このVLOOKUP関数が完全一致または近似一致のどちらを返すかを決定する論理値。

  • 近似一致1 / 真:完全一致が見つからない場合、数式は最も近い一致(ルックアップ値よりも小さい最大値)を検索します。 この場合、ルックアップ列を昇順で並べ替える必要があります。
    = VLOOKUP(lookup_value、table_array、col_index、TRUE)
    = VLOOKUP(lookup_value、table_array、col_index、1)
  • 完全に一致0 / 偽:これは、ルックアップ値と正確に等しい値を検索するために使用されます。 完全に一致するものが見つからない場合は、エラー値#N / Aが返されます。
    = VLOOKUP(lookup_value、table_array、col_index、FALSE)
    = VLOOKUP(lookup_value、table_array、col_index、0)

注意:

  • 1. Vlookup関数は、左から右にのみ値を検索します。
  • 2.ルックアップ値に基づいて一致する値が複数ある場合、Vlookup関数を使用して最初に一致した値のみが返されます。
  • 3.ルックアップ値が見つからない場合は、#N / Aエラー値を返します。

基本的なVLOOKUPの例

1。 完全一致Vlookupと近似一致Vlookupを実行します

Excelで完全一致のVlookupを実行します

通常、Vlookup関数と完全に一致するものを探している場合は、最後の引数でFALSEを使用する必要があります。

たとえば、特定のID番号に基づいて対応する数学のスコアを取得するには、次のようにします。

1. 結果を取得する空白のセルに次の数式を適用します。

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

2。 次に、塗りつぶしハンドルをこの数式に入力するセルまでドラッグすると、必要な結果が得られます。 スクリーンショットを参照してください:

注意:

  • 1.上記の式では、 F2 一致する値を返したい値です。 A2:D7 テーブル配列、数です 3 一致した値が返される列番号であり、 間違った情報 完全一致を指します。
  • 2.基準値がデータ範囲に見つからない場合、エラー値#N / Aが表示されます。

Excelで近似一致Vlookupを実行します

近似一致は、データ範囲間の値を検索する場合に役立ちます。 完全に一致するものが見つからない場合、近似Vlookupは、ルックアップ値よりも小さい最大値を返します。

たとえば、次の範囲データがある場合、指定された注文は[注文]列にありません。列Bで最も近い割引を取得するにはどうすればよいですか?

1。 結果を入力するセルに次の数式を入力します。

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

2。 次に、塗りつぶしハンドルをセルまでドラッグしてこの数式を適用すると、指定された値に基づいて近似一致が得られます。スクリーンショットを参照してください。

注意:

  • 1.上記の式では、 D2 相対情報を返したい値です。 A2:B9 はデータ範囲、数値です 2 一致した値が返される列番号と TRUE 近似一致を指します。
  • 2.近似一致は、特定のルックアップ値よりも小さい最大値を返します。
  • 3. Vlookup関数を使用して近似一致値を取得するには、データ範囲の左端の列を昇順で並べ替える必要があります。そうしないと、間違った結果が返されます。

2。 Excelで大文字と小文字を区別するVlookupを実行する

デフォルトでは、Vlookup関数は大文字と小文字を区別しないルックアップを実行します。つまり、小文字と大文字は同じものとして扱われます。 場合によっては、Excelで大文字と小文字を区別するルックアップを実行する必要があります。インデックス、一致、および正確な関数、またはルックアップと正確な関数を使用すると便利です。

たとえば、次のデータ範囲があり、ID列に大文字または小文字のテキスト文字列が含まれています。ここで、指定されたID番号の対応する数学スコアを返します。

式1:EXACT、INDEX、MATCH関数の使用

1。 結果を取得する空白のセルに、以下の配列数式を入力またはコピーしてください。

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

2。 次に、 Ctrl + Shift + Enter キーを同時に押して最初の結果を取得し、数式セルを選択して、この数式を入力するセルまで塗りつぶしハンドルを下にドラッグすると、必要な正しい結果が得られます。 スクリーンショットを参照してください:

注意:

  • 1.上記の式では、 A2:A10 検索する特定の値を含む列です。 F2 ルックアップ値です。 C2:C10 結果が返される列です。
  • 2.複数の一致が見つかった場合、この式は常に最初の一致を返します。

式2:ルックアップ関数と正確な関数の使用

1. 結果を取得する空白のセルに次の数式を適用してください。

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

2. 次に、塗りつぶしハンドルをこの数式をコピーするセルまでドラッグすると、下のスクリーンショットに示すように、大文字と小文字が区別された一致する値が取得されます。

注意:

  • 1.上記の式では、 A2:A10 検索する特定の値を含む列です。 F2 ルックアップ値です。 C2:C10 結果が返される列です。
  • 2.複数の一致が見つかった場合、この式は常に最後の一致を返します。

3。 Excelの右から左へのVlookup値

Vlookup関数は、常にデータ範囲の左端の列の値を検索し、対応する値を列から右に返します。 以下のスクリーンショットのように、右側の特定の値をルックアップし、左側の列に対応する値を返すことを意味する逆Vlookupを実行する場合:

クリックして、このタスクの詳細を段階的に確認してください…


4。 ExcelでXNUMX番目、n番目、または最後に一致する値をVlookup

通常、Vlookup関数の使用時に一致する値が複数見つかった場合、最初に一致したレコードのみが返されます。 このセクションでは、Vlookup関数を使用して、XNUMX番目、n番目、または最後に一致する値を取得する方法について説明します。

Vlookupを実行し、XNUMX番目またはn番目の一致する値を返します

列Aに名前のリストがあり、列Bに購入したトレーニングコースがあるとします。ここで、特定の顧客が購入した2番目またはn番目のトレーニングコースを探しています。 スクリーンショットを参照してください:

1。 指定された基準に基づいてXNUMX番目またはn番目の一致値を取得するには、次の配列数式を空白のセルに適用してください。

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

2。 次に、 Ctrl + Shift + Enter キーを一緒に押して最初の結果を取得し、数式セルを選択して、この数式に入力するセルまで塗りつぶしハンドルをドラッグすると、指定された名前に基づいてXNUMX番目に一致するすべての値が一度に表示されます。スクリーンショットを参照してください。

注意:

  • この式では、 A2:A14 ルックアップのすべての値を含む範囲です。 B2:B14 戻りたい一致する値の範囲です。 E2 はルックアップ値であり、最後の数値です 2 取得する3番目に一致する値を示します。XNUMX番目に一致する値を返したい場合は、必要に応じてXNUMXに変更する必要があります。

Vlookupを実行し、最後に一致した値を返します

以下のスクリーンショットに示すように、vlookupして最後に一致する値を返したい場合は、これ Vlookupと最後に一致する値を返す チュートリアルは、最後に一致する値を詳細に取得するのに役立つ場合があります。


5。 XNUMXつの指定された値または日付の間のVlookupマッチング値

XNUMXつの値または日付の間の値を検索し、以下のスクリーンショットに示すように対応する結果を返したい場合があります。この場合、LOOKUP関数と並べ替えられたテーブルを使用できます。

式を使用して、XNUMXつの指定された値または日付の間で値を照合するVlookup

1。 まず、元のテーブルは並べ替えられたデータ範囲である必要があります。 次に、次の数式をコピーするか、空白のセルに入力します。

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

2。 次に、塗りつぶしハンドルをドラッグして、この数式を必要な他のセルに塗りつぶします。これで、指定された値に基づいて一致するすべてのレコードが取得されます。スクリーンショットを参照してください。

注意:

  • 1.上記の式では、 A2:A6 小さい値の範囲であり、 B2:B6 データ範囲内のより大きな数値の範囲です。 E2 対応する値を取得する指定された値です。 C2:C6 抽出する列データです。
  • 2.この式は、以下のスクリーンショットに示すように、XNUMXつの日付間で一致する値を抽出するためにも使用できます。

便利な機能を備えたXNUMXつの指定された値または日付間のVlookupマッチング値

上記の式に苦労している場合は、ここで簡単なツールを紹介します– Kutools for Excelそのと XNUMXつの値の間のルックアップ 機能では、数式を覚えていなくても、XNUMXつの値または日付の間の特定の値または日付に基づいて対応するアイテムを返すことができます。   今すぐKutoolsfor Excelをダウンロードするには、ここをクリックしてください。


6。 Vlookup関数の部分一致にワイルドカードを使用する

Excelでは、ワイルドカードをVlookup関数内で使用できます。これにより、ルックアップ値に対して部分一致が実行されます。 たとえば、Vlookupを使用して、ルックアップ値の一部に基づいてテーブルから一致した値を返すことができます。

以下のスクリーンショットに示すようなデータの範囲があるとすると、今度は、(フルネームではなく)名に基づいてスコアを抽出したいと思います。 このタスクをExcelでどのように解決できますか?

1。 通常のVlookup関数は正しく機能しません。テキストまたはセル参照をワイルドカードで結合する必要があります。次の数式をコピーするか、空白のセルに入力してください。

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

2。 次に、塗りつぶしハンドルをドラッグして、この数式を必要な他のセルに塗りつぶすと、一致したすべてのスコアが次のスクリーンショットのように返されます。

注意:

  • 1.上記の式では、 E2&” *” ルックアップ値、の値 E2* ワイルドカード(「*」は任意のXNUMX文字または任意の文字を示します)、 A2:C11 ルックアップ範囲、数です 3 返す値を含む列。
  • 2.ワイルドカードを使用する場合のVlookupでは、Vlookup関数の最後の引数にFALSEまたは0を使用して完全一致モードを設定する必要があります。

ヒント:

1.特定の値で終わる一致する値を見つけて返します。次の式を適用してください。 =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2.指定されたテキストがテキスト文字列の前、後ろ、または中央にあるかどうかに関係なく、テキスト文字列の一部に基づいて一致した値を検索して返すには、セル参照またはテキストの周囲にXNUMXつの*文字を結合する必要があります。 この式で行ってください: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7。 別のワークシートからのVlookup値

通常、複数のワークシートで作業する必要がある場合があります。Vlookup関数を使用して、XNUMXつのワークシートと同じように別のシートからデータを検索できます。

たとえば、以下のスクリーンショットに示すようにXNUMXつのワークシートがあり、指定したワークシートから対応するデータを検索して返すには、次の手順を実行してください。

1. 一致するアイテムを取得する空白のセルに、次の数式を入力またはコピーしてください。

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

2. 次に、この数式を適用するセルまで塗りつぶしハンドルをドラッグすると、必要に応じて対応する結果が得られます。スクリーンショットを参照してください。

注意: 上記の式では:

  • A2 ルックアップ値を表します。
  • データシート は、データを検索するワークシートの名前です(シート名にスペース文字または句読文字が含まれている場合は、シート名を一重引用符で囲む必要があります。それ以外の場合は、= VLOOKUP(A2、データシート!$ A $ 2:$ C $ 15,3,0));
  • A2:C15 データを検索しているデータシートのデータの範囲です。
  • 3 返される一致データを含む列番号です。

8。 別のブックからのVlookup値

このセクションでは、ルックアップについて説明し、Vlookup関数を使用して別のブックから一致する値を返します。

たとえば、最初のワークブックには製品リストとコストリストが含まれているので、次に示すスクリーンショットのように、製品アイテムに基づいてXNUMX番目のワークブックの対応するコストを抽出します。

1。 別のブックから相対コストを取得するには、まず、使用する両方のブックを開き、結果を配置するセルに次の数式を適用します。

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

2。 次に、この数式をドラッグして、必要な他のセルにコピーします。スクリーンショットを参照してください。

注意:

  • 1.上記の式では:
    B2 ルックアップ値を表します。
    [製品リスト.xlsx]シート1 データを検索するワークブックとワークシートの名前です(ワークブックへの参照は角括弧で囲まれ、ワークブック+シート全体は一重引用符で囲まれます)。
    A2:B6 データを検索している別のワークブックのワークシート内のデータの範囲です。
    2 返される一致データを含む列番号です。
  • 2.ルックアップワークブックが閉じている場合、ルックアップワークブックの完全なファイルパスは、次のスクリーンショットのように数式で表示されます。

9。 Vlookupを実行し、0または#N / Aエラー値の代わりに空白または特定のテキストを返します

通常、vlookup関数を適用して対応する値を返す場合、一致するセルが空白の場合は0が返され、一致する値が見つからない場合は、以下のスクリーンショットに示すようなエラー#N / A値が表示されます。 0または#N / Aの値を空白のセルまたはその他の任意の値で表示する代わりに、これ 0またはN / Aの代わりに空白または特定の値を返すVlookup チュートリアルはあなたに段階的に賛成するかもしれません。


高度なVLOOKUPの例

1。 Vlookup関数を使用した双方向ルックアップ(行と列のVlookup)

場合によっては、2次元ルックアップを実行する必要があります。これは、行と列の両方で同時にVlookupを実行することを意味します。 たとえば、次のデータ範囲がある場合、指定した四半期の特定の製品の値を取得する必要がある場合があります。 このセクションでは、Excelでこのジョブを処理するための数式をいくつか紹介します。

式1:VLOOKUP関数とMATCH関数の使用

Excelでは、VLOOKUP関数とMATCH関数を組み合わせて使用​​して、双方向のルックアップを実行できます。次の数式を空白のセルに適用して、を押してください。 入力します 結果を得るための鍵。

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

注意: 上記の式では:

  • H1:対応する値を取得する列のルックアップ値。
  • A2:E6:行ヘッダーを含むデータ範囲。
  • H2:対応する値を取得する行のルックアップ値。
  • A1:E1:列ヘッダーのセル。

式2:INDEX関数とMATCH関数の使用

これは、2次元ルックアップの実行に役立つ別の数式です。以下の数式を適用してから、を押してください。 入力します 必要な結果を得るための鍵。

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

注意: 上記の式では:

  • B2:E6:一致したアイテムを返すデータ範囲;
  • H1:対応する値を取得する列のルックアップ値。
  • A2:A6:行ヘッダーには、探したい製品が含まれています。
  • H2:対応する値を取得する行のルックアップ値。
  • B1:E1:列ヘッダーには、検索する四半期が含まれています。

2。 XNUMXつ以上の基準に基づくVlookupマッチング値

XNUMXつの基準に基づいて一致する値を検索するのは簡単ですが、XNUMXつ以上の基準がある場合、何ができますか? ExcelのLOOKUPまたはMATCHおよびINDEX関数は、このジョブをすばやく簡単に解決するのに役立ちます。

たとえば、以下のデータテーブルがあります。特定の商品とサイズに基づいて一致した価格を返すには、次の式が役立ちます。

式1:LOOKUP関数の使用

結果を取得するセルに次の数式を適用してから、Enterキーを押してください。スクリーンショットを参照してください。

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

注意:

  • 1.上記の式では:
    A2:A12 = G1:範囲A1:A2のG12の基準を検索することを意味します。
    B2:B12 = G2:範囲B2:B2でG12の基準を検索することを意味します。
    D2:D12:対応する値を返したい範囲。
  • 2. XNUMXつ以上の基準がある場合は、次のような他の基準を数式に結合する必要があります。 =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

式2:INDEXT関数とMATCH関数の使用

Index関数とMatch関数の組み合わせを使用して、複数の基準に基づいて一致した値を返すこともできます。 次の式をコピーまたは入力してください。

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

次に、Ctrl + Shift + Enterキーを同時に押して、必要に応じて相対値を取得します。 スクリーンショットを参照してください:

注意:

  • 1.上記の式では:
    A2:A12 = G1:範囲A1:A2のG12の基準を検索することを意味します。
    B2:B12 = G2:範囲B2:B2でG12の基準を検索することを意味します。
    D2:D12:対応する値を返したい範囲。
  • 2. XNUMXつ以上の基準がある場合は、次のように、新しい基準を数式に結合する必要があります。 =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3。 XNUMXつ以上の条件で複数の一致する値を返すVlookup

Excelでは、Vlookup関数は値を検索し、対応する値が複数見つかった場合にのみ最初に一致する値を返します。 場合によっては、対応するすべての値を行、列、または単一のセルに返したいことがあります。 このセクションでは、ワークブック内のXNUMXつ以上の条件で複数の一致する値を返す方法について説明します。

XNUMXつ以上の条件に基づいてすべての一致する値を水平方向にルックアップする

XNUMXつの条件に基づいてすべての一致する値を水平方向にルックアップします。

Vlookupを実行し、XNUMXつの特定の値に基づいて一致するすべての値を水平方向に返すには、一般的な式は次のとおりです。

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
注意: m は、戻り範囲の最初のセルの行番号から1を引いたものです。
      n 最初の数式セルの列番号から1を引いたものです。

1。 以下の数式を空白のセルに適用してから、を押してください Ctrl + Shift + Enter 最初に一致した値を取得するために一緒にキーを押します。スクリーンショットを参照してください。

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2。 次に、最初の数式セルを選択し、空白のセルが表示され、対応するすべてのアイテムが抽出されるまで、塗りつぶしハンドルを右側のセルにドラッグします。スクリーンショットを参照してください。

ヒント:

返されたリストに重複する一致する値がある場合、重複を無視するには、この式を使用してから、を押してください。 入力します 最初の結果を得るには: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

この式を入力し続けます: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") 最初の結果の横のセルに入力し、を押します Ctrl + Shift + Enter キーを合わせてXNUMX番目の結果を取得し、この数式を右のセルにドラッグして、空白のセルが表示されるまで他のすべての一致する値を取得します。スクリーンショットを参照してください。


XNUMXつ以上の条件に基づいてすべての一致する値を水平方向にルックアップします。

Vlookupを実行し、より具体的な値に基づいて一致するすべての値を水平方向に返すための一般的な式は次のとおりです。

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
注意: m は、戻り範囲の最初のセルの行番号から1を引いたものです。
      n 最初の数式セルの列番号から1を引いたものです。

1。 結果を出力する空白のセルに次の数式を適用します。

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2。 次に、数式セルを選択し、空白のセルが表示され、特定の基準に基づいて一致したすべての値が返されるまで、塗りつぶしハンドルを右側のセルにドラッグします。スクリーンショットを参照してください。

注意:その他の基準については、lookup_valueとlookup_rangeを数式に結合する必要があります。 =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


XNUMXつ以上の条件に基づいてすべての一致する値を垂直方向にルックアップする

XNUMXつの条件に基づいてすべての一致する値を垂直方向にルックアップします。

Vlookupを実行し、XNUMXつの特定の値に基づいて一致するすべての値を垂直方向に返すには、一般的な式は次のとおりです。

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
注意: m は、戻り範囲の最初のセルの行番号から1を引いたものです。
      n 最初の数式セルの行番号から1を引いたものです。

1。 結果を取得するセルに次の数式をコピーまたは入力して、を押します。 Ctrl + Shift + Enter 最初に一致した値を取得するために一緒にキーを押します。スクリーンショットを参照してください。

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2。 次に、最初の数式セルを選択し、空白のセルが表示され、対応するすべての項目が列に一覧表示されるまで、塗りつぶしハンドルを他のセルにドラッグします。スクリーンショットを参照してください。

ヒント:

返される一致する値の重複を無視するには、次の式を使用してください。 =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

次に、 Ctrl + Shift + Enter キーを合わせて最初に一致した値を取得し、空白のセルが表示されるまでこの数式セルを他のセルにドラッグすると、必要に応じて結果が得られます。


XNUMXつ以上の条件に基づいてすべての一致する値を垂直方向にルックアップします。

Vlookupを実行し、より具体的な値に基づいて一致するすべての値を垂直方向に返すには、一般的な式は次のとおりです。

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
注意: m は、戻り範囲の最初のセルの行番号から1を引いたものです。
      n 最初の数式セルの行番号から1を引いたものです。

1。 以下の数式を空白のセルにコピーして、を押します Ctrl + Shift + Enter 最初に一致したアイテムを取得するために一緒にキーを押します。

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2。 次に、空白のセルが表示されるまで数式セルを他のセルにドラッグします。スクリーンショットを参照してください。

注意:その他の基準については、lookup_valueとlookup_rangeを数式に結合する必要があります。 =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


XNUMXつ以上の条件に基づくすべての一致する値を単一のセルにルックアップします

Vlookupを実行し、一致した複数の値を指定された区切り文字を使用してXNUMXつのセルに返す場合、TEXTJOINの新機能を使用すると、このジョブをすばやく簡単に解決できます。

XNUMXつの条件に基づいて一致するすべての値を単一のセルにVlookupします。

以下の簡単な数式を空白のセルに適用して、を押してください Ctrl + Shift + Enter 一緒にキーを押して結果を取得します。

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

ヒント:

返される一致する値の重複を無視するには、次の式を使用してください。 =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


XNUMXつ以上の条件に基づいて一致するすべての値をXNUMXつのセルにルックアップします。

一致するすべての値をXNUMXつのセルに返すときに複数の条件を処理するには、次の数式を適用してから、を押してください。 Ctrl + Shift + Enter 一緒にキーを押して結果を取得します。

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

注意:

1. TEXTJOIN関数は、Excel2019およびOffice365でのみ使用できます。

2. Excel 2016以前のバージョンを使用する場合は、以下の記事のユーザー定義関数を使用してください。


4。 一致したセルの行全体または行全体を返すVlookup

このセクションでは、Vlookup関数を使用して一致した値の行全体を取得する方法について説明します。

1。 結果を出力する空白のセルに以下の数式をコピーまたは入力して、を押してください 入力します 最初の値を取得するためのキー。

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

2。 次に、行全体のデータが表示されるまで数式セルを右側にドラッグします。スクリーンショットを参照してください。

注意:上記の式では、 F2 は、に基づいて行全体を返すルックアップ値です。 A1:D12 使用するデータ範囲は、 A1 データ範囲内の最初の列番号を示します。

ヒント:

一致した値に基づいて複数の行が見つかった場合、対応するすべての行を返すには、次の式を適用してください。 =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")、を押してから Ctrl + Shift + Enter キーを一緒に押して最初の結果を取得し、塗りつぶしハンドルをセルに右にドラッグします。スクリーンショットを参照してください。

次に、下のスクリーンショットに示すように、セル全体で塗りつぶしハンドルを下にドラッグして、一致するすべての行を取得します。


5。 Excelで複数のVlookup関数(ネストされたVlookup)を実行します

以下のスクリーンショットに示すように、テーブルのいずれかに特定のルックアップ値が含まれている場合、複数のテーブルで値を検索したい場合があります。この場合、XNUMXつ以上のVlookup関数をIFERROR関数と組み合わせて、複数のルックアップを実行できます。

ネストされたVlookup関数の一般式は次のとおりです。

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

注意:

  • 参照値:あなたが探している価値;
  • Table1, Table2, Table3、...:ルックアップ値と戻り値が存在するテーブル。
  • コル:一致する値を返すテーブルの列番号。
  • 0:これは完全一致に使用されます。

1。 結果を入力する空白のセルに次の数式を適用してください。

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2。 次に、この数式を適用するセルまで塗りつぶしハンドルを下にドラッグすると、一致するすべての値が次のスクリーンショットのように返されます。

注意:

  • 1.上記の式では、 J3 あなたが探している価値です。 A3:B7, D3:E7, G3:H7 ルックアップ値と戻り値が存在するテーブル範囲です。 番号 2 一致する値を返す範囲の列番号です。
  • 2.ルックアップ値が見つからない場合は、エラー値が表示されます。エラーを読み取り可能なテキストに置き換えるには、次の式を使用してください。 =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6。 別の列のリストデータに基づいて値が存在するかどうかを確認するVlookup

Vlookup関数は、別のリストに基づいて値が存在するかどうかを確認するのにも役立ちます。たとえば、列Cで名前を検索し、以下のスクリーンショットのように列Aで名前が見つかったかどうかにかかわらず、YesまたはNoを返します。示されています。

1。 次の数式を空白のセルに適用してください。

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

2。 次に、塗りつぶしハンドルをこの数式に入力するセルまでドラッグすると、必要に応じて結果が得られます。スクリーンショットを参照してください。

注意:上記の式では、 C2 チェックするルックアップ値です。 A2:A10 ルックアップ値が見つかる範囲のリストです。 番号 1 範囲内の値をフェッチする列番号です。


7。 行または列で一致したすべての値をVlookupして合計します

数値データを使用する場合、テーブルから一致する値を抽出するときに、複数の列または行の数値を合計する必要がある場合もあります。 このセクションでは、Excelでこのジョブを完了するための数式をいくつか紹介します。

Vlookupを実行し、XNUMX行または複数行で一致したすべての値を合計します

以下のスクリーンショットに示すように、数か月間の売上のある製品リストがあるとします。ここで、指定された製品に基づいて、すべての月のすべての注文を合計する必要があります。

Vlookupを実行し、行で最初に一致した値を合計します。

1。 次の数式をコピーするか、空白のセルに入力して、を押してください Ctrl + Shift + Enter 最初の結果を得るために一緒にキーを押します。

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

2。 次に、塗りつぶしハンドルを下にドラッグして、この数式を必要な他のセルにコピーすると、最初に一致した値の行のすべての値が合計されます。スクリーンショットを参照してください。

注意:上記の式では: H2 探している値を含むセルです。 A2:F9 ルックアップ値と一致した値を含むデータ範囲(列ヘッダーなし)です。 番号 2,3,4,5,6 {} 範囲の合計を計算するために使用される列番号です。


Vlookupを実行し、一致したすべての値を複数の行で合計します。

上記の式は、最初に一致した値の行の値のみを合計できます。 複数の行のすべての一致を合計する場合は、次の数式を使用してから、この数式を適用するセルまで塗りつぶしハンドルをドラッグすると、必要な結果が得られます。スクリーンショットを参照してください。

=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)

注意:上記の式では: H2 探しているルックアップ値です。 A2:A9 ルックアップ値を含む行ヘッダーです。 B2:F9 合計する数値のデータ範囲。


Vlookupを実行し、XNUMXつまたは複数の列で一致したすべての値を合計します

Vlookupを実行し、列内で最初に一致した値を合計します。

以下のスクリーンショットに示すように、特定の月の合計値を合計する場合。

以下の数式を空白のセルに適用し、塗りつぶしハンドルを下にドラッグして、この数式を他のセルにコピーします。これで、列の特定の月に基づいて最初に一致した値が合計されました。スクリーンショットを参照してください。

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

注意:上記の式では: H2 探しているルックアップ値です。 B1:F1 ルックアップ値を含む列ヘッダーです。 B2:F9 合計する数値のデータ範囲。


Vlookupを実行し、一致したすべての値を複数の列に合計します。

Vlookupを実行し、一致したすべての値を複数の列で合計するには、次の式を使用する必要があります。

=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))

注意:上記の式では: H2 探しているルックアップ値です。 B1:F1 ルックアップ値を含む列ヘッダーです。 B2:F9 合計する数値のデータ範囲。


Vlookupを実行し、最初に一致した値または一致したすべての値を強力な機能で合計します

上記の式は覚えにくいかもしれませんが、この場合は便利な機能をお勧めします- ルックアップと合計 of Kutools for Excel、この機能を使用すると、可能な限り簡単に結果を得ることができます。    今すぐKutoolsfor Excelをダウンロードするには、ここをクリックしてください。


行と列の両方で一致したすべての値をVlookupして合計します

たとえば、列と行の両方を一致させる必要があるときに値を合計して、下のスクリーンショットに示すように、XNUMX月の製品Sweaterの合計値を取得する場合。

次の数式をセルに適用し、Enterキーを押して結果を取得してください。スクリーンショットを参照してください。

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

注意:上記の式では: B2:F9 合計する数値のデータ範囲です。 B1:F1 is 列ヘッダーには、合計に基づくルックアップ値が含まれています。 I2 探している列ヘッダー内のルックアップ値です。 A2:A9 行ヘッダーには、合計に基づくルックアップ値が含まれています。 H2 探している行ヘッダー内のルックアップ値です。


8。 XNUMXつ以上のキー列に基づいてXNUMXつのテーブルをマージするVlookup

日常業務では、データを分析するときに、XNUMXつ以上のキー列に基づいて必要なすべての情報をXNUMXつのテーブルに収集する必要がある場合があります。 この仕事を解決するために、Vlookup関数もあなたに有利に働きます。

XNUMXつのキー列に基づいてXNUMXつのテーブルをマージするVlookup

たとえば、XNUMXつのテーブルがあり、最初のテーブルには商品と名前のデータが含まれ、XNUMX番目のテーブルには商品と注文が含まれています。ここで、共通の商品列をXNUMXつのテーブルに一致させることにより、これらXNUMXつのテーブルを結合します。

式1:VLOOKUP関数の使用

キー列に基づいてXNUMXつのテーブルをXNUMXつにマージするには、結果を取得する空白のセルに次の数式を適用してから、この数式を適用するセルまで塗りつぶしハンドルをドラッグします。キー列データに基づいて、注文列が最初のテーブルデータに結合しているマージされたテーブルを取得します。

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

注意:上記の式では、 A2 あなたが探している価値です、 E2:F8 検索するテーブル、番号です 2 値を取得するテーブルの列番号です。

式2:INDEX関数とMATCH関数の使用

XNUMX番目のテーブル内の右側の共通データと左側の列に返されたデータが注文列をマージする場合、Vlookup関数はジョブを実行できません。 右から左にルックアップするには、INDEX関数とMATCH関数を使用してVlookup関数を置き換えることができます。

以下の数式をコピーするか、空白のセルに入力してから、数式を列にコピーすると、注文列が最初のテーブルに結合されます。スクリーンショットを参照してください。

=INDEX($E$2:$E$8, MATCH($A2, $F$2:$F$8, 0))

注意:上記の式では、 A2 探しているルックアップ値です、 E2:E8 返したいデータの範囲です。 F2:F8 ルックアップ値を含むルックアップ範囲です。


複数のキー列に基づいてXNUMXつのテーブルをマージするVlookup

結合するXNUMXつのテーブルに複数のキー列がある場合、これらの共通の列に基づいてテーブルをマージするには、INDEX関数とMATCH関数が役立ちます。

複数のキー列に基づいてXNUMXつのテーブルをマージするための一般的な式は次のとおりです。

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1。 結果を入力する空白のセルに次の数式を適用して、を押してください Ctrl + Shift + Enter 最初に一致した値を取得するために一緒にキーを押します。スクリーンショットを参照してください。

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

注意:上記の数式で、セル参照が表すものは、以下のスクリーンショットに示されています。

2次に、最初の数式セルを選択し、塗りつぶしハンドルをドラッグして、必要に応じてこの数式を他のセルにコピーします。

ヒント:Excel 2016以降のバージョンでは、 パワークエリ キー列に基づいてXNUMXつ以上のテーブルをXNUMXつにマージする機能。 ステップバイステップで詳細を知るためにクリックしてください.

9。 複数のワークシート間で値を照合するVlookup

複数のワークシートで値をVlookupしようとしたことがありますか? データの範囲を含む次のXNUMXつのワークシートがあるとします。次に、これらXNUMXつのワークシートの基準に基づいて対応する値の一部を取得し、以下のスクリーンショットのような結果を取得します。 この場合、 複数のワークシートにわたるVlookup値 チュートリアルはあなたに段階的に賛成するかもしれません。


VLOOKUPに一致する値は、セルの書式を維持します

1。 ルックアップ値とともにセルの書式設定(セルの色、フォントの色)を取得するVlookup

ご存知のとおり、通常のVlookup関数は、別のデータ範囲から一致した値を返すのに役立つだけですが、塗りつぶしの色、フォントの色、フォントのスタイルなどのセルの書式とともに、対応する値を返すことが必要な場合があります。以下のスクリーンショットのように。 このセクションでは、Excelで戻り値を使用してセルの書式を取得する方法について説明します。

セルの書式設定とともに対応する値を検索して返すには、次の手順を実行してください。

1。 ワークシートにVlookupするデータが含まれている場合は、シートタブを右クリックして、 コードを表示 コンテキストメニューから。 スクリーンショットを参照してください:

2。 オープンで アプリケーション向け Microsoft Visual Basic ウィンドウの場合は、以下のVBAコードをコードウィンドウにコピーしてください。

VBAコード1:ルックアップ値とともにセルフォーマットを取得するVlookup

Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Range(xDic.Keys(I)).Interior.Color = _
                Range(xDic.Items(I)).Interior.Color
                Range(xDic.Keys(I)).Font.FontStyle = _
                Range(xDic.Items(I)).Font.FontStyle
                Range(xDic.Keys(I)).Font.Size = _
                Range(xDic.Items(I)).Font.Size
                Range(xDic.Keys(I)).Font.Color = _
                Range(xDic.Items(I)).Font.Color
                Range(xDic.Keys(I)).Font.Name = _
                Range(xDic.Items(I)).Font.Name
                Range(xDic.Keys(I)).Font.Underline = _
                Range(xDic.Items(I)).Font.Underline
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
End Sub

3。 まだ アプリケーション向け Microsoft Visual Basic ウィンドウ、クリック インセット > モジュール、次に、以下のVBAコード2をモジュールウィンドウにコピーします。

VBAコード2:ルックアップ値とともにセルフォーマットを取得するVlookup

Public xDic As New Dictionary
Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepFormat = ""
        xDic.Add Application.Caller.Address, ""
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
    End If
End Function

4。 上記のコードを挿入した後、をクリックします BHツール > リファレンス アプリケーション向け Microsoft Visual Basic 窓。 次に、 Microsoftスクリプトランタイム のチェックボックス 参照– VBAProject ダイアログボックス。 スクリーンショットを参照してください:

5。 次に、をクリックします。 OK ダイアログボックスを閉じてから、コードウィンドウを保存して閉じます。次に、ワークシートに戻り、次の式を適用します。 =LookupKeepFormat(E2,$A$1:$C$10,3) 結果を出力する空白のセルに入力し、Enterキーを押します。 スクリーンショットを参照してください:

注意:上記の式では、 E2 調べる値です、 A1:C10 はテーブルの範囲であり、数値は 3 一致した値を返すテーブルの列番号です。

6。 次に、最初の結果セルを選択し、塗りつぶしハンドルを下にドラッグして、すべての結果とその書式を取得します。 スクリーンショットを参照してください。


2。 Vlookupの戻り値から日付形式を保持する

通常、Vloook関数を使用して一致した日付形式の値を検索して返す場合、以下のスクリーンショットに示すように、いくつかの数値形式が表示されます。 返された結果から日付形式を保持するには、TEXT関数をVlookup関数で囲む必要があります。

以下の数式を空白のセルに適用し、塗りつぶしハンドルをドラッグしてこの数式を他のセルにコピーすると、一致したすべての日付が以下のスクリーンショットのように返されます。

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

注意:上記の式では、 E2 外観値です、 A2:C9 ルックアップ範囲、数です 3 値を返す列番号です。 mm/dd/yyy 保持する日付形式です。


3。 Vlookupとセルコメント付きの一致する値を返す

次のスクリーンショットのように、一致したセルデータだけでなく、セルのコメントもExcelで返すようにVlookupを実行しようとしたことがありますか? このタスクを解決するために、以下のユーザー定義関数が役立ちます。

1。 を押し続けます Alt + F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。

2に設定します。 OK をクリックします。 インセット > モジュール、次に次のコードをコピーしてモジュールウィンドウに貼り付けます。

VBAコード:セルコメント付きのVlookupと戻り値:

Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
'Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant 'could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
        VlookupComment = "Not Found"
    Else
        Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
        VlookupComment = xCell.Value
        With Application.Caller
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            If Not xCell.Comment Is Nothing Then
                .AddComment xCell.Comment.Text
            End If
        End With
    End If
End Function

3。 次に、コードウィンドウを保存して閉じ、次の式を入力します。 =vlookupcomment(D2,$A$2:$B$9,2,FALSE) 空白のセルに移動して結果を見つけ、塗りつぶしハンドルをドラッグしてこの数式を他のセルにコピーします。これで、一致した値とコメントが一度に返されます。スクリーンショットを参照してください。

注意:上記の式では、 D2 対応する値を返したいルックアップ値です。 A2:B9 使用するデータテーブル、番号です 2 は、返したい一致した値を含む列番号です。


4。 Vlookupでテキストと実数を処理する

たとえば、ある範囲のデータがあり、元のテーブルのID番号は数値形式であり、テキストとして保存されているルックアップセルに、通常のVlookup関数を適用すると、#N / Aエラーの結果が次のスクリーンショットのように表示されます。示されています。 この場合、テーブル内のルックアップ番号と元の番号のデータ形式が異なる場合、どのようにして正しい情報を取得できますか?

Vlookup関数でテキストと実数を処理するには、次の数式を空白のセルに適用し、塗りつぶしハンドルを下にドラッグしてこの数式をコピーしてください。次のスクリーンショットのように正しい結果が得られます。

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

注意:

  • 1.上記の式では、 D2 対応する値を返したいルックアップ値です。 A2:B8 使用するデータテーブル、番号です 2 は、返したい一致した値を含む列番号です。
  • 2.この式は、数字がどこにあり、テキストがどこにあるかわからない場合にもうまく機能します。

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

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • スーパーフォーミュラバー (複数行のテキストと数式を簡単に編集できます); 読書レイアウト (多数のセルを簡単に読み取って編集する); フィルター範囲に貼り付け...
  • セル/行/列をマージする およびデータの保持。 分割セルコンテンツ; 重複する行と合計/平均を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 (すべてのセルは空です); スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • お気に入りの数式をすばやく挿入する、範囲、チャート、写真; セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター (フィルタースキームを保存して他のシートに適用します); 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • ピボットテーブルのグループ化 週番号、曜日など... ロック解除された、ロックされたセルを表示する さまざまな色で; 式/名前を持つセルを強調表示する...
kteタブ201905
  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性が50%向上し、毎日何百ものマウスクリックが減ります。
officetab下部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.