INDEXとMATCHでn番目の一致を取得する
今回の数式を利用することで、Vlookupのような最初の一致だけではなく、二番目、三番目といった指定での検索が可能になります。
一般的な式
{=INDEX(配列,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),))}
説明
数式を使用してデータのセットから複数の一致する値を取得するには、IF関数とSMALL関数を使用して、各一致の行番号を計算し、その値をINDEXに渡しています。図に入力されているH7の式は次のとおりです。
{=INDEX(テーブル35[料金],SMALL(IF(テーブル35[ID]=$H$3,ROW(テーブル35[ID])-ROW(INDEX(テーブル35[ID],1,1))+1),G6))}
配列=テーブル35[料金]（D4:D14）、vals=テーブル35[ID]（C4:C14）、val=（H3）、nth=G6
これは配列数式であり、Ctrl + Shift + Enterで入力する必要があることに注意してください。
この数式の仕組み
この数式は、特定の位置にある配列の値を取得するINDEX関数を利用しています。
この場合のINDEX関数は、取得したいnの値はG列を参照し、その値をもとに行を特定します。このINDEX関数が取得する行数が「n番目」の一致に対応していきます。
さらにIF関数では、一致する行が含まれる行を特定する作業を行い、SMALL関数ではそのリストからn番目の値を返します。IF関数での数式は次のとおりです。
ID = H3
この配列を生成します：
{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}
2番目と6番目の位置で顧客IDが一致し、TRUEと表示されていることに注意してください。IFの「trueの場合の値」引数は 、次の式で相対行番号のリストを生成します。
ROW(ID)-ROW(INDEX(ID,1,1))+1
これはこの配列を生成します：
{1;2;3;4;5;6;7}
次に、この配列は論理テスト結果によって「フィルター」され、IF関数は次の配列結果を返します。
{1;FALSE;FALSE;4;FALSE;FALSE;FALSE}
行1と行2に有効な行番号があることに注意してください。
次に、この配列はSMALLによって処理されます。SMALLは、列Hの値を使用して「n番目」の値を返すように構成されています。SMALL関数は、配列内の論理値TRUEおよびFALSEを自動的に無視します。最終的に、式は次のようになります。
=INDEX(料金,2) // H6, returns \1,200 =INDEX(料金,6) // H7, returns \600
エラーの処理
特定のIDに一致するものがなくなると、SMALL関数は#NUMエラーを返します。このエラーを処理するには、IFERROR関数を使用するか、一致をカウントするロジックを追加し、列Hの数値が一致カウントよりも大きくなると処理を中止します。この例は、1つのアプローチを示しています。
