Excel関数： テーブルの複数行列範囲を検索し、複数の該当セルの値をセル位置順に取得したい。

３行２列のブロックからできているテーブルより特定の文字列（数値、日付、文字列）を、左上から右下のブロックまで行番号の小さい順に、かつ列番号の小さい順に複数検索し、該当ブロックの検索値の一つ上のセルと一つ下の左右のデータを取出して一覧表を作成したいと思います。

因みに、A列B列だけだとしたら、
セル11の取り出しは、　{=IF(COUNTIF($A$1:$A$18,$M$1)<ROW($A1),"",INDEX($A$1:$A$18,SMALL(IF($A$1:$A$18=$M$1,ROW($A$1:$A$18)-1),ROW($A1))))}で
セル26の取り出しは、上を列方向にオートフィルして取り出せました。
同様に、データ11とデータ26も取り出せました。
また、コード11とコード26の取り出しは、難しかったですが
{=IF(COUNTIF($A$1:$A$18,$M$1)<ROW($A1),"",INDEX($B$1:$B$18,SMALL(IF($A$1:$A$18=$M$1,ROW($B$1:$B$18)+1),ROW($A1))))}としたら、
取り出せました。

しかし、質問のように複数の列がある場合にはどうすればよいのか、または別の関数（例えばSUMPRODUCT関数など）を利用しなければできないのか、としたら、どういう計算式になるのか、まったく混迷しています。
関数だけで解決できる方法が有りましたら是非とも教えてください。

No.3 回答者： mike_g 回答日時： 2018/12/21 15:23

[No.2]からの続き

７．次式を入力したセル Q5 を下方にオートフィル
￣￣=IFERROR(SMALL(TmpTbl,ROW(A1)),"")
８．セル M5 に次式を入力
￣￣=IF($Q5="","",OFFSET($A$1,$Q5/10-2,MOD($Q5,10)-1))
９．セル N5 に次式を入力
￣￣=IF($Q5="","",OFFSET($A$1,$Q5/10,MOD($Q5,10)-1))
10．セル O5 に次式を入力
￣￣=IF($Q5="","",OFFSET($A$1,$Q5/10,MOD($Q5,10)))
結果を此処の添付図に示しています。

No.2 回答者： mike_g 回答日時： 2018/12/21 14:19

[No.1]からの続き

此処の添付図に示す作業表を作成します。
なお、セル M1 に検索日付(例として、2018/12/3)を入力しておきます。
２．次式を入力したセル R2 を下方にズズーッと(取り敢えず 10行目まで)オートフィル
￣￣=IFERROR(SMALL(IF(table=$M$1,ROW(table)),ROW(A1)),0)
￣￣【お断り】上式は必ず配列数式として入力のこと
３．次式を入力したセル S2 を右方に９列、下方に８行オートフィル
￣￣=IFERROR(SMALL(IF(OFFSET($A$1,$R2-1,,,10)=$M$1,COLUMN(table)),COLUMN(A1)),"")
￣￣【お断り】上式は必ず配列数式として入力のこと
４．次式を入力したセル AC2 を下方にズズーッとオートフィル
￣￣=IF(R2,IF(COUNTIF(R$2:R2,R2)>1,"",R2),"")
５．次式を入力したセル AD2 を右方に９列、下方に８行オートフィル
￣￣=IFERROR($AC2*10+S2,"")
６．ステップ１と同様の手法で、範囲 AD2:AM10 に名前 TmpTbl を付ける

【続く】

No.1 回答者： mike_g 回答日時： 2018/12/21 12:15

かなり面倒なので、数回に分けて記します。

添付図を出来るだけ鮮明にするために、文字数を少なくした同等のテーブルで説明します。
今回はベースとなる「３行２列のブロックからできているテーブル」を添付図に示しておくだけェ～。
１．範囲 A1:J18 を選択 ⇒ Alt+MMD ⇒ [名前]ボックスに table と入力 ⇒ [OK]

【続く】

この回答へのお礼

早々とご回答いただきまして有難うございます。

ALT+MMDて何？というレベルですので、これから数式の意味を理解しながら、検証していきます。

宜しくお願い致します。

お礼日時：2018/12/22 14:18