エクセル 複数行の検索

質問

下のような表で
（D14）に
（a8）と入力すると答えに
（く）と出るようにまた

また同じセル（D14）に
（a16）と入れると答えに
（た）と出るようにしたいのですが
教えて下さい。

回答No.5

imogasi · ベストアンサー

この表でしか通用しない式であるが、
８は５で割ると余り３だから、表の左列より３列目、商は１で実際は表の最初行から３行目。
これで計算して交差するセルを割り出すと、求める物が出る。
（ただし「a1」といっている表現が、実際の表では、同じ文字＋数字添え字であるのかどうか、質問の記述がが不完全だと思う。名前の規則正しい数字添え字が無い場合はユーザー関数で公団に説明しておく）
ーー
例データ　B4：F11　以下略
a1 a2 a3 a4 a5
あ い う え お
a6 a7 a8 a9 a10
か き く け こ
a11 a12 a13 a14 a15
さ し す せ そ
a16 a17 a18 a19 a20
た ち つ て と
H４：J23
a1 1 あ
a2 2 い
a3 3 う
a4 4 え
a5 5 お
a6 6 か
a7 7 き
a8 8 く
a9 9 け
a10 10 こ
a11 11 さ
a12 12 し
a13 13 す
a14 14 せ
a15 15 そ
a16 16 た
a17 17 ち
a18 18 つ
a19 19 て
a20 20 と
ーーー
I4の式　=MID(H4,2,LEN(H4)-1)
J4の式　=INDEX($B$4:$F$20,(INT((I4-1)/5))*2+2,MOD(I4-1,5)+1)
下方向に式を複写
I列はわかりやすい（J列の式が見てくれで短くなる）ように作業列にしたが、この式をJ列の式のI4の所へ組み込まばI列は不要になる。
ーーーーー
何でも、表見だし（a１、a２．・・）がありなら、
ユーザー関数で標準モジュールに
Function fndR(a)
fndR = Range("A1: F30").Find(a).Row
End Function
Function fndC(a)
fndC = Range("A1:F30").Find(a).Column
End Function
シートの式は
=INDEX($A$1:$F$30,fndr(H4)+1,fndC(H4))
した方向に式を複写する。結果名
前段と同じ。
H4:H5を（元はa3,a7のところ）
田中
長野
にすると
田中 う
長野 き

お礼

ありがとうございます。
がんばってやってみます。

回答No.7

tom04

No.6です！
たびたびごめんなさい。

投稿後に気づいたのですが
他の方の回答をよく見るとNo.1さんと全く同様の方法を投稿してしまいました。

確認せずに投稿してごめんなさいね。m(__)m

お礼

ありがとうございます。
がんばってやってみます。

回答No.6

tom04

こんばんは！
一例です。

=IF(COUNTIF(B4:F10,D14),INDEX(B4:F11,SUMPRODUCT((B4:F11=D14)*ROW(A1:A8))+1,SUMPRODUCT((B4:F11=D14)*COLUMN(A1:E1))),"")

としてみてはどうでしょうか？m(__)m

お礼

ありがとうございます。
がんばってやってみます。

回答No.4

KURUMITO

a1からa20まで規則正しく並んでいるのでしたら次の式でもよいですね。

=IF(D14="","",INDEX(B4:F11,ROUNDUP(SUBSTITUTE(D14,"a","")/5,0)*2,MOD(SUBSTITUTE(D14,"a","")-1,5)+1))

お礼

ありがとうございます。
がんばってやってみます。

回答No.3

okormazd

作業セルを使わないなら、
D14:
=INDEX($B$4:$F$11,(INT((RIGHT(D14,LEN(D14)-1)-1)/5)+1)*2,MOD(RIGHT(D14,LEN(D14)-1)-1,5)+1)

とでもする。

「a8、a9、・・・」などの数値部分が連続だからできるが、そうなっていなければ、複雑になる。
もし、a8や「あ、い、う」などに規則性がないのなら、例示をそのようにしなけれれば、回答者の努力は無駄になるね。

お礼

ありがとうございます。
がんばってやってみます。

回答No.2

nattocurry

一例です。
かなり力ずくで不恰好ですが、D16に
=IF(ISNA(HLOOKUP(D14,B4:F5,2,0)),"",HLOOKUP(D14,B4:F5,2,0))&IF(ISNA(HLOOKUP(D14,B6:F7,2,0)),"",HLOOKUP(D14,B6:F7,2,0))&IF(ISNA(HLOOKUP(D14,B8:F9,2,0)),"",HLOOKUP(D14,B8:F9,2,0))&IF(ISNA(HLOOKUP(D14,B10:F11,2,0)),"",HLOOKUP(D14,B10:F11,2,0))

お礼

ありがとうございます。
がんばってやってみます。

回答No.1

mu2011

一例です。
=IF(COUNTIF(B4:F11,D14),INDEX(B4:F11,SUMPRODUCT((B4:F11=D14)*ROW(B4:B11))-2,SUMPRODUCT((B4:F11=D14)*COLUMN(B4:F4))-1),"")

お礼

ありがとうございます。
がんばってやってみます。