あいまい検索でヒットするデータを表示する方法

質問

office2016

ある機種のデータをsheet1のA列２行目以降に取り込みます
code　　←題目
1233
127011　←(A)
C00001
C00233
…
C90001　←(B)

その後昇順にならんだデータにします。約50行くらいあります。

(A)のデータは
1270で始まるデータで
127011,127021の様に６ケほど存在します。
(A)のデータは機種に必須のデータなので６ケの中でどれか１つが必ず存在します。
文字列と数字が混載表示のデータで1270が含まれるデータはありません。
C91270の様なデータは無いということです。

(B)のデータは
C9000で始まるデータで
C90001,C9002の様に5ケほど存在します。
(B)のデータは機種に必須ではないので、存在しない場合があります。

A列のデータの中から
(A)で存在するデータはsheet2のG3セルへ
(B)で存在するデータはsheet2のM3セルへ
それぞれ表示したいのですが、何か簡単に一発で表示される良い方法があれば教えていただきたく。

現状の構成は次の通りです。
マクロで下記を対応してます。
Aデータの有無を確認する作業シート(シート名はA)を設けて
A列にコピーし
B2セルに判定として
=IF(ISERROR(SEARCH("1270",A2,1)),"対象外",SEARCH("1270",A2,1))
これをA列の行分コピー
B列で対象外の行を削除
1行目が題目で2行目に対象のデータが残るのでそれを表示

code
127011 　　1

の状況になるので
SHEET2のG3セルは
=IF(A!B2=1,A!A2,"")
とすると
127011がG3セルに表示される

Bデータの有無を確認する作業シート(シート名はB)を設けて
A列にコピーし
B2セルに判定として
=IF(ISERROR(SEARCH("C9000",A2,1)),"対象外",SEARCH("C9000",A2,1))
これをA列の行分コピー
B列で対象外の行を削除
1行目が題目で2行目に対象のデータがあれば残るのでそれを表示

code
C90001 　　1

の状況になるので
SHEET2のM3セルは
=IF(B!B2=1,B!A2,"")
とすると
C90001がM3セルに表示される
C9****のデータが存在しない場合、M3セルは空欄表示でOK

これで一応やりたい内容は達成できていますが、関数で一発表示ができたらとの思いです。マクロでも構いません。
よろしくお願いします。

回答No.1

kkkkkm · ベストアンサー

ヒットする対象が一個だけでよろしければ

G3セルに
=INDEX(Sheet1!A2:A50,SUMPRODUCT((Sheet1!A2:A50>=127000)*(Sheet1!A2:A50<=127099)*ROW(A2:A50))-1,1)
M3セルに
=INDEX(Sheet1!A2:A50,MATCH("C9000*",Sheet1!A2:A50,0),1)

で試してみてください。

お礼

回答ありがとうございます。とても参考になりました。