ExcelのMATCH関数での列指定

質問

エクセルで作られた社員名簿のデータを別シートにINDEX関数を使って抜き出したいのですが、MATCH関数での列指定が上手くいきません。
表は
　　　A　　　　B　　　　　C　　　　　D　　　　　E　　　　　F
1 社員番号　社員氏名A　家族1氏名　家族1年齢　家族2指名　家族年齢
2 社員番号　社員氏名B　家族1氏名　家族1年齢　家族2指名　家族年齢
3 社員番号　社員氏名C　家族1氏名　家族1年齢　家族2指名　家族年齢
と、社員1名につきその家族が一行で表示されています。
これを別シートに家族1名につき一行の表があるので、年齢だけをこの表から持っていきたいのですが、一行に複数の家族がいるので、MATCH関数での列指定が上手くいきません。何かいい方法はないでしょうか？

回答No.2

zap35 · ベストアンサー

＃０１です
OFFSET関数は基準セルから行数、列数を指定して離れたセルを参照しますが、基準セルがOFFSET(A1,0,0)、右となりならOFFSET(A1,0,1)になります。C1を指すときはOFFSET(A1,0,2)です

一方MATCH関数で列方向の検索をする場合はA列を基準(1)として何列目かを返します。C列に目的の検索語があれば3が返ります。
MATCHで得た３をOFFSET関数で使うために　－１　しています。

今回はOFFSET(基準セル、行位置、列位置、行数、列数）の型も使用しています。OFFSET関数のHELPを見て、意味を考えて見て下さい

２０は「２０列目まで」を範囲とするために指定しましたが、別に４０でも１００でもかまいません。２０を指定したので（家族氏名、年齢）の対は９個までと書いたのです（最初の２列は社員番号、社員氏名）

回答No.1

zap35

シート１が以下の時に
　　　　Ａ列　　　Ｂ列　　　　Ｃ列　　　　Ｄ列　　Ｅ列　　　　Ｆ列
１行目　123456　社員氏名A　　家族Ａ１　　40　　家族Ａ２　　18
２行目　234567　社員氏名B　　家族Ｂ１　　35　　家族Ｂ２　　9
３行目　345678　社員氏名C　　家族Ｃ１　　29　　

シート２に以下の結果を得るなら
　　　　Ａ列　　　Ｂ列　　　　Ｃ列　　　　Ｄ列
１行目　123456　社員氏名A　　家族Ａ１　　40
２行目　123456　社員氏名A　　家族Ａ２　　18
３行目　234567　社員氏名B　　家族Ｂ１　　35
４行目　234567　社員氏名B　　家族Ｂ２　　9
５行目　345678　社員氏名C　　家族Ｃ１　　29　　

シート２のＤ１の式は以下になります
=OFFSET(Sheet1!$A$1,MATCH($A1,Sheet1!A:A,0)-1,MATCH($C1,OFFSET(Sheet1!$A$1,MATCH($A1,Sheet1!A:A,0)-1,0,1,20),0))

Ｄ１を下方向にコピーしてください。なお扶養家族は９人までＯＫです

補足

早速の回答ありがとうございます。こんな関数があったとは知りませんでした。勉強になりました。
ちなみに質問なんですが・・・
この式で－1や20という数字は何を意味しているのでしょうか？私にはちょっと複雑すぎて式の意味がいまいちよく理解できません^^;