Excelで値の複数のインスタンスをルックアップする方法
ここにいる場合は、VLOOKUPを使用してデータセットから複数の値を取得しようとしていることを意味します。しかし、正直に言うと、VLOOKUPは複数の値を返すことができません。しかし、それは私たちがそれを行うことができないという意味ではありません。最初の値だけではなく、複数の値を取得するルックアップを実行できます。
一般式
{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(first cell of lookup_value_range)+1),ROW(1:1)))}
配列：データをフェッチする場所からの範囲。
lookup_value：フィルタリングするlookup_value。
lookup_value_range：lookup_valueをフィルタリングする範囲。
lookup_value範囲の最初のセル：lookup_value範囲が$ A $ 5：$ A $ 100の場合、その$ A $ 5。
重要：すべてが `link：/ excel-range-name-absolute-reference-in-excel [絶対参照]`である必要があります。 lookup_valueは、要件に応じて相対値にすることができます。
配列数式として入力します。数式を書き込んだ後、CTRL + SHIFT + ENTERキーを押して配列数式にします。
複数の結果のルックアップの例この学生データは範囲A2：E14にあります。セルG1には、領域値のドロップダウンがあります。中央、東、北、南、西。
ここで、G1のどの地域にいても、その地域のすべての学生のリストをH列に表示する必要があります。
Excelで複数の値を検索するために、変数を特定しましょう。
配列：$ C $ 2：$ C $ 14 lookup_value：$ G $ 1 lookup_value_range：$ A $ 2：$ A $ 14 lookup_valueの範囲の最初のセル：$ A $ 2上記のデータによると、Excelで複数の値を取得する式は次のようになります。
{=INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1)))}
この数式をH2にコピーし、CTRL + SHIFT + ENTERを押します。次に、＃NUMエラーが発生するまで、この数式を下にドラッグします。 #NUMは、そのlookup_valueに一致する値が他に残っていないことを示し、リストの最後です。
#NUMが気になる場合は、数式の前に `link：/ logical-formulas-excel-iferror-function [IFERROR function]`を付けて、エラーの代わりに意味のある情報を表示できます。
{=IFERROR(INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1))),"--List Ends--")}
これにより、すべてのアイテムが表示されるときに—ListEnds—が表示されます。
それがどのように機能するかを理解しましょう。
式は複雑に見えるかもしれませんが、アイデアは単純です。値が出現するたびにインデックス番号を取得してから、Excelの `link：/ lookup-formulas-excel-index-function [INDEXfunction]`を使用して値を取得する必要があります。
したがって、主な課題は、lookup_valueのインデックス番号の配列を取得することです。インデックス番号を取得するには、 link：/ Tips-if-condition-in-excel [IF] ʻと link：/ lookup-and-reference-excel-row-function [ROW] `
を使用しました。機能。公式は確かに完全に複雑です、それを分解しましょう。
Student列から値を取得したいので、 `link：/ lookup-formulas-excel-index-function [INDEXfunction]`の配列は$ C $ 2：$ C $ 14です。ここで、G1の値が存在する$ A $ 2：$ A $ 14（ルックアップ値）から行番号を指定する必要があります（今のところ、G1がその中心にあるとしましょう）。
IF（$ G $ 1 = $ A $ 2：$ A $ 14、ROW（$ A $ 2：$ A $ 14）：セルカウントのG1（中央）の値が$ A $ 2：$ Aの範囲にある場合、この部分は行番号を返します。 $ 14 elseはFALSEを返します。この例では、
\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}を返します。
-
ここで、上記の配列には1行目（1：1）の行番号が含まれており、配列（A2：A14）から始まる行が必要なため、-ROW（$ A $ 2）を使用します。 IF式で+1。これにより、配列を開始する前に行数が返されます。
この例では、-1です。A3から開始した場合は、-2などが返されます。これは数値は、IFによって返される配列内の各数値から減算されます。したがって、最後にIF（$ G $ 1 = $ A $ 2：$ A $ 14、ROW（$ A $ 2：$ A $ 14）-ROW（$ A $ 2）+1）これは\ {1; FALSE; FALSE; FALSE; FALSE; 6; 7; FALSE; FALSE; 10; FALSE; FALSE; FALSE}に変換されます。
-
次に、この配列は `link：/で囲まれます。 statistics-formulas-excel-small-function [SMALL] `
関数。この関数は、N番目の小さい値を返します。指定された配列の推定値。これで、 link：/ statistics-formulas-excel-small-function [SMALL]（\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE} 、ROW（1：1））。
ROW（1：1）は1を返します。したがって、上記の関数は配列内で最初に小さい値である2を返します。
この数式を以下のセルにコピーすると、ROW（1：1）はROW（2：2）になり、配列内で2番目に小さい値である7を返します。これにより、関数は最初に見つかった値を最初に返すことができます。ただし、最後に見つかった値を最初に取得する場合は、SMALL関数の代わりにLARGE関数を使用してください。
関数の上に返された値を使用して、INDEX関数は範囲から一致する各値を簡単に返します。
つまり、1つのルックアップ値で複数の対応する値をルックアップできます。上記のgifでは、IFERROR関数を使用してエラーをキャッチし、条件付き書式を使用して少し視覚的にしています。上記の例では、他の記事で説明した他の多くの関数と手法を使用しました。