doodle-on-web

自分で調べたことや、仕事の中で質問されたことなどをまとめています。

VLOOKUPでn番目の一致を取得

スポンサーリンク

VLOOKUPでn番目の一致を取得

f:id:doodle-on-web:20200807171035p:plain

一般式

= VLOOKUP( ID &" - " & 検索対象ID,対象データ範囲,データ番号,0 )

説明

VLOOKUPを使用してn番目のMATCHを取得するには、カウントを含む一意のIDを構築する補助列をテーブルに追加してやる必要があります。元々のテーブルに列を追加出来ない場合には、代わりにINDEXとMATCHを利用した数式を使用できます。

= VLOOKUP( ID &" - " & I6,データ,4,0 )

この数式の仕組み

この数式は、ヘルパー列に依存しています。補助列は、最初の列としてソースデータテーブルに追加されます。補助列には、既存のIDとカウンターから一意のルックアップ値を作成する式が含まれています。カウンターは、一意のIDがデータテーブルに表示された回数をカウントします。

この例では、ヘルパー列の数式セルB4は次のようになります。

f:id:doodle-on-web:20200807171117p:plain

=D4&"-"&COUNTIF($D$4:D4,D4)

この数式はD4の値を取得し、文字の連結(&)を使用してハイフンとCOUNTIF関数の結果を追加します。COUNTIF関数は、複合参照($D$4:D4,D4)を使用して 、データ内のIDの現在のカウントを生成します。

ルックアップ側では、VLOOKUPを使用して、「n番目」の発生を考慮して、テーブルから値を取得します。この場合のトリックとしては、ヘルパー列に表示される値と同じ構造のルックアップ値を作成し、「n番目」を同指定してやるのかを再現することです。VLOOKUP側でもCOUNTIFと同様作成するのですが、この場合も、ハイフンと「n番目」のセルを登録してやることで解決します。