ExcelのOFFSET関数は、特定のセル範囲から指定した行数と列数の位置にあるセル範囲の参照を返す関数です。この関数を活用し、セルの値に基づいて参照範囲を動的に変更する事例です（MATCH関数も活用）。
内容：
事例の前提
セルの値で参照範囲を変更できると便利な場面として、下図のような事例です。
この事例では、下図左側のA列の値の出現状況に応じて、参照範囲を変更しています。
具体的には、”部門”という値が次に出現する位置（行数）を求め、その行数に応じた参照範囲の値を取得したいケースです。
なお、下図右側の結果はフィルター処理も含めた作業後の結果です。
セルの値で参照範囲を変更
上述の事例では、MATCH関数とOFFSET関数を組み合わせて、次のような数式を入力しています（１行分のみ例示）。
MATCH 関数の説明：検索文字が次に出現する位置（行数）の算出
検索文字が次に出現する位置（行数）を算出したい場合、MATCH関数が活用できます。構文は次のとおりです。
=MATCH(検査値, 検査範囲, [照合の型])
今回の事例では、セルA2に”部門A”があり、A列で次に”部門”を含むセル位置までの行数を算出したいので、次の数式を入力しています。
セルE2の場合：
=MATCH("部門*",A3:$A$16,0)
"部門*”とすることで、部分一致で検索しています。
A3:$A$16 は、セルA2の次に出現する位置を検索したいため、開始をセルA3としています。また、終了は検索範囲の最後でセルA16です。なお、この数式を下方向にコピーし、同様の計算をしたいため、セルA16は絶対参照として数式コピーでも検索範囲の最後が変わらないようにしています。
この数式で、検索文字が次に出現する位置（行数）の算出が可能です。
OFFSET 関数の説明：参照範囲の変更例
前述のMATCH関数で得た「次に出現する位置（行数）」を利用し、OFFSET関数で参照範囲を変更します。
OFFSET関数の構文は次のとおりです。
OFFSET(基準, 行数, 列数, [高さ], [幅])
今回の事例では、数式がある同列行（事例でセルA2）から「次に出現する位置（行数）：セルE2の結果」の範囲の参照をえたいため、次の数式を入力しています。
セルF2の数式（一部）：
OFFSET(A2,0,0,E2,1)
これにより、A2を基準に高さ3（E2の値）、幅1として、
セル範囲：A2:A4
の範囲を取得します。
今回は、この参照範囲のセルを結合するため
=TEXTJOIN("|",FALSE,OFFSET(A2,0,0,E2,1))
とした事例でした。
なお、TEXTJOIN関数は、複数の範囲や文字列からのテキストを結合（区切文字含む）する関数で構文は次のとおりです。
TEXTJOIN (区切り記号、ignore_empty、文字列 1, [文字列 2],...)
複数の範囲に、前述のOFFSET関数の結果（参照範囲）を利用しています。
参考
冒頭のアウトプットのような抽出をしたい場合は、上記数式を下方向にコピーし、フィルターで抽出します。
具体的な手順等については以下を参考にご覧ください。
以上、OFFSET関数を活用することで、セルの値に基づいて参照範囲を動的に変更する事例でした。MATCH関数と組み合わせることで、データの範囲の変更に対応した数式作成がより可能となります。
