OFFSET 基準値 可変できないですか？

質問

初めてお世話になります。私なりにやりたいことをネットで検索して作ってますが行き詰まり悩んでいます。
その日のシフト勤務で早番の人はこの人、遅番の人はこの人と検索したいです。

SHEET１には勤務表が記載されています。

＿A　　　　B　　　　　C　　　　D　　　
１　　　　　１日　　　２日　　　３日
２名前A　早番　　早番　　　・・・・
３名前B　遅番　　早番　　　・・・・
４名前C　早番　　遅番　　　・・・・

SHEET２には、その日勤務する人で早番がこの人とこの人（例えば、上記でいえば２日に勤務する
早番は名前A、名前Bの２名と検索できるようにしたいです。

（A１の２日はSHEET１の日付と紐付けされていて、３日、４日と変わればC列の名前は変わっていく）
SHEET２
＿　A　　　　B　　　　C
１　２日　　早番　名前A
２　　　　　 早番　名前B
３
４

自分なりに調べて、C1のセルには、
＝INDEX（'SHEET１'A列の名前人数分,MATCH("早番",OFFSET('SHEET1'B2,0,SHEET2!A1-1,A列の名前人数分,1）,0),1)とし、名前Aは抽出できましたが、C2セルの２番目の名前Bは検索できません。

matchの抽出は複数検索値がある場合、２番目、３番目は検索できないとあったので、それではと
C2セルにOFFSETの基準値をC1で出たMATCHの結果+B2セルという風（C1で出た１番目の早番と出たセルを基準値とし、そこから下の行へ次の早番を探す）に考えましたが出た結果はセルではなく行数になってしまい無理でした。

２番目の検索までは、OFFSETの基準値を下にずらし、高さ調節でやりようはできるのですが（早番男１名、女１名はSHEET１のA列の名前欄は男性、女性である行で明確にわかれている為検索可）、ある日によっては早番が男２名だったりする場合は破綻してしまいます。
LARGEやSMALLとかも駄目もとで試しましたが、数値じゃないのか駄目駄目でした。
ちなみにSHEET１で記載される名前順は順番を変えたくないです。

柔軟にその日のシフトを検索できる方法はありますでしょうか？
宜しければご指導宜しくお願い致します。

回答No.1

keithin · ベストアンサー

先に簡単な方法（推奨）：

シート３に、シート１から
＿A　　　　B　　　　　　C　　　　　D　　　
１　　　　　１日　　　　２日　　　　３日
２名前A　早番１　　早番１　　　・・・・
３名前B　遅番１　　早番２　　　・・・・
４名前C　早番２　　遅番１　　　・・・・

Ｂ２：
=Sheet1!B2&COUNTIF(Sheet1!B$2:B2,B2)

のように作成してやれば、アナタが作成した数式がほとんどそのまま利用できます。

メンドクサイ方法（お勧めしません）：
シート２のＣ１には
=INDEX(Sheet1!A:A,SMALL(IF(OFFSET(Sheet1!$A$1:$A$10,0,MATCH($A$1,Sheet1!$1:$1,0)-1)=B1,ROW(Sheet1!$A$1:$A$10)),COUNTIF($B$1:B1,B1)))
と記入して、必ずコントロールキーとシフトキーを押しながらEnterで入力
以下コピー
しておくと出来ます。

お礼

さっそくのご回答ありがとうございます。助かりました！また何かありましたらご助力いただければと思います。

補足

おすすめの方法わかりました！matchで検索値を限定する為にシート３にシート１と同じ物を作り、番号を振り出すんですね。スッとしました。

メンドクサイ方法も時間を作り解明したいと思います。