Excel検索した値の一つ右のセルの内容を返す関数

A B C D E
１ あ 松本 か 今田
２　い　山田　 き　武田
３　う 武井 く　 島本　
４　え 佐藤 け　斎藤

たとえば、"く"を検索して"島本"を返す関数を探しています。
VLOOKUP,SUMRODUCT,MATCHとかを使って作るのかな～と思っていますが
まったく分かりません。だれかお知恵をください

No.7 ベストアンサー 回答者： tom04 回答日時： 2010/11/20 19:03

No.6です！

数式を拝見すると、確かに惜しいです！

=IF(BV2="","",INDEX($C$2:$BT$34,SUMPRODUCT(($C$2:$BT$34=BV2)*ROW($C$2:$C$34)),SUMPRODUCT(($C$2:$BT$34=BV2)*COLUMN(($C$2:$BT$2)))+1)

の中の
ROW($C$2:$C$34)　を
ROW($C$1:$C$33)　に訂正　→　ROW($A$1:$A$33)　でも構いません。

COLUMN(($C$2:$BT$2))　を
COLUMN(($A$2:$BR$2))　に訂正　→　COLUMN(($A$1:$BR$1)) でも構いません。

上記のように行・列番号に注意して変更してみてください。
たぶん大丈夫だと思います。

INDEX関数で範囲指定した中で、行・列とも1行・1列目からの数値にします。
行に関しては　ROW(A1)から範囲指定した行数分を！
列に関しては COLUMN(A1)から範囲指定した列数分を掛けます。

蛇足ですがROW()　の括弧内部分の列番号は何でも構いません、
B1やC1でもなんでもOKです。なぜならROW(A1)=ROW(B1)=ROW(BT1)・・・すべて同じ値（1）です！
列に関しても同様のことが言えます。


仮に検索文字がINDEX関数で範囲指定した中の2行目にあった場合、
お示しの関数では一つ下の行を！
列方向に関しては2列右側を返す数式になってしまいます。

以上、長々と書きましたが
今度はちゃんと表示されれば良いのですが・・・m(__)m

この回答へのお礼

できました。
各関数の理解はあまり出来ていませんが、成功しました。

ありがとうございました。

お礼日時：2010/11/22 12:52

No.8 回答者： tom04 回答日時： 2010/11/20 20:12

No.6・7です！

たびたびごめんなさい。
前回の投稿の内容を自分で読み返してみても判りにくいものになっていたと思います。

もう一度画像をアップして数式の意味を理解してもらえばと思いお邪魔しました。

↓の画像でB2セルに
=INDEX(C6:H10,SUMPRODUCT((C6:H10=A2)*ROW(A1:A5)),SUMPRODUCT((C6:H10=A2)*COLUMN(A1:F1))+1)
という数式を入れています。
「け」を検索し、その右隣の「HH」を返したいので、
INDEX関数で範囲指定した4行目の3列目が検索できて、その右となりのデータが返れば良いということになります。
SUMPRODUCT関数で行*（1行目～行数分）　列*（1列目～列数分）　をかけると
画像のような仕組みになります。
列を見てもらうと
SUMPRODUCT((C6:H10=A2)*COLUMN(A1:F1))部分の
SUMPRODUCT((C6:H10=A2)　が12行目です。検索文字がない列は「0」を返します。
それに列番号をかけているので「3」が返ることになります。
行に関しても同様です。
すなわちINDEX関数で「4」行目・「3」列目に「け」がありますよ！ということになります。
ただし、列に関してはその右となりを返したいので「+1」としています。

前回に行・列とも1行目・1列目からかけてくださいといったのはこういう意味です。

仮に前回お示しの$C$2:$BT$2　だと画像では「1」から始まるべきところが
行に関しては「2」から、列に関しては「3」から始まりますので、
1行下で2列右側のデータがヒットすることになります。

画像では必要ないデータを空白にしていますが、データが入っていても
検索文字以外の場合は「0」となりますので敢えて入力していません。

以上、今回も長々と失礼しました。m(__)m

No.6 回答者： tom04 回答日時： 2010/11/18 18:32

こんばんは！

一例です。
↓の画像のようなデータの配置だとして・・・（A1～F5の範囲での数式です）

I2セルに
=IF(H2="","",INDEX($A$1:$F$5,SUMPRODUCT(($A$1:$F$5=H2)*ROW($A$1:$A$5)),SUMPRODUCT(($A$1:$F$5=H2)*COLUMN(($A$1:$F$1)))+1))
という数式を入れオートフィルで下へコピーすると
画像のような感じになります。
当然のコトですが、検索文字に重複がないとしています。

尚、データ量によって数式内の範囲指定と、
ROW($A$1:$A$5)　COLUMN(($A$1:$F$1)))+1
の部分は適宜変更してみてください。

以上、参考になれば良いのですが・・・m(__)m

この回答へのお礼

ありがとうございます。
とても惜しいところまで来ました。実際のデータの範囲はC2:BT34です。なので、
IF(BV2="","",INDEX($C$2:$BT$34,SUMPRODUCT(($C$2:$BT$34=BV2)*ROW($C$2:$C$34)),SUMPRODUCT(($C$2:$BT$34=BV2)*COLUMN(($C$2:$BT$2)))+1)
で下にコピーするのですが、いくつかで0と表示されます。なにかヒントがあれば教えてください。

お礼日時：2010/11/20 12:38

No.5 回答者： KURUMITO 回答日時： 2010/11/18 18:10

回答Ｎｏ3です。

Ａ列に数値が1，2，3とあるものとして回答しています。あ、いなどがＡ1セルから下方にあるとしたら次のような式になります。

=IF(COUNTIF(A:A,F1)>0,INDEX(B:B,MATCH(F1,A:A,0)),IF(COUNTIF(C:C,F1)>0,INDEX(D:D,MATCH(F1,C:C,0)),""))

No.4 回答者： kagakusuki 回答日時： 2010/11/18 17:06

　今仮に、「く」等の検索するデータを、I1セルに入力するものとします。

　I1セルと同じデータが、B列かD列のどちらかに、必ず存在する場合には、検索結果を表示させるセルに、次の様な数式を入力すると良いと思います。

=VLOOKUP(I1,IF(COUNTIF(B:B,I1)>0,B:C,D:E),2,0)

　尚、I1セルが空欄の場合や、B列にもD列にも存在しないデータが入力された場合にも、エラー表示を出さない様にするためには、上記の数式を応用して、次の様な数式になります。

=IF(COUNTIF(B:B,I1)+COUNTIF(D:D,I1)>0,"",VLOOKUP(I1,IF(COUNTIF(B:B,I1)>0,B:C,D:E),2,0))

　又、B列～D列に加えて、F列とG列にも、平仮名と苗字が並んでいる場合には、次の様な数式になります。

=IF(SUMPRODUCT((COUNTIF(OFFSET(B:B,,(ROW(A$1:A$3)-1)*2),I1)>0)*1)=0,"",VLOOKUP(I1,OFFSET(B:C,,(SUMPRODUCT((COUNTIF(OFFSET(B:B,,(ROW(A$1:A$3)-1)*2),I1)>0)*ROW(A$1:A$3))-1)*2),2,0))

No.3 回答者： KURUMITO 回答日時： 2010/11/18 15:03

Ｆ1セルに「く」と入力してその答えをＧ1セルに表示させるとしてらＧ1セルには次の式を入力します。

=IF(COUNTIF(B:B,F1)>0,INDEX(C:C,MATCH(F1,B:B,0)),IF(COUNTIF(D:D,F1)>0,INDEX(E:E,MATCH(F1,D:D,0)),""))

この回答へのお礼

ありがとうございます。
しかし、空白で表示されてしまうのですが、なにか分かりますか？

お礼日時：2010/11/18 17:23

No.2 回答者： MackyNo1 回答日時： 2010/11/18 14:38

元データがＡ～Ｄ列にあり、元データのデータ範囲に「データ範囲」と名前を付けておいて（もちろん絶対参照の数式でもＯＫです）、例えばＦ２セルに検索値を入力し、以下の式を入力すれば検索値の右側の値を求めることができます。

=INDEX(A:D,MAX(INDEX((F2=データ範囲)*ROW(データ範囲),)),MAX(INDEX((F2=データ範囲)*COLUMN(データ範囲),))+1)

ただし、上記の数式は、検索データが元のリストに2つ以上無いこと（１：１で対応していること）が条件になります。

No.1 回答者： SaKaKashi 回答日時： 2010/11/18 14:27

=VLOOKUP(探す文字の入っているセルまたは"く",検索範囲D:E,2,true)

とか