エクセルで内線表検索

社員数が多いため複数行で作成されている内線表から、名称で内線を検索する方法を教えていただけないでしょうか。
VLOOKUPの組み合わなのかと思うのですが・・・

内線表は印刷しても良いように部署/内線/名前が複数行で記載されております。
名前は氏名で記載されており、複数名で内線を使用する場合は
スペース区切りで複数名の名前が記載されております。
氏と名は区切りなく連続で記載されております。

希望
(1)　B1に氏名を入力するとC1に該当者の内線を表示
(2)　検索範囲はシート内の全データ（複数行で記載されているため）
(3)　同姓の方が居ない場合は名字だけでも検索可能

　　A　　　B　　　C　　　　　　　D　　　E　　F
1 　　　検索名　検索結果
2
3 部署　内線　名前　　　　　　部署　内線　名前
4 ABC 100 Aさん　Bさん BCD 200 Cさん
5 ABC 101 Dさん　 BCD 201 Eさん Fさん
6 ABC 102 Gさん　Hさん BCD 202 Iさん
7 : :
8 : :

No.4 ベストアンサー 回答者： KURUMITO 回答日時： 2009/10/16 11:49

Ｃ1セルには次の式を入力します。

=IF(COUNTIF(C3:C1000,"*"&B1&"*")=0,"",OFFSET(INDEX(A:I,MATCH("*"&B1&"*",C3:C1000,0)+2,COLUMN(C1)),0,-1))&IF(COUNTIF(F3:F1000,"*"&B1&"*")=0,"",OFFSET(INDEX(A:I,MATCH("*"&B1&"*",F3:F1000,0)+2,COLUMN(F1)),0,-1))&IF(COUNTIF(I3:I1000,"*"&B1&"*")=0,"",OFFSET(INDEX(A:I,MATCH("*"&B1&"*",I3:I1000,0)+2,COLUMN(I1)),0,-1))
この式では行が1000行まで列がＩ列までの表に対応しています。適宜変形してください。
なお、式ではＢ1セルに入力された文字をあるセルの位置から内線を求める式となっています。同姓がいない場合には名字だけでも検索可能とはどういうことでしょう。名字だけしか入力されていないということでしょうか？　その場合にはＢ1セルに名字を入力することになりますが、名字だけでは別の性の方がいるかもしれませんね。少なくともダブる可能性を持つ入力の仕方は問題がありますね。ダブらないようにする工夫が必要でしょう。

この回答へのお礼

お礼が遅れて大変申し訳ございませんでした。

うゎ～すごい式ですね。
試させていただきましたら、ずばりです。
こんなに長い式を考えていただけるなんて、かなりの時間と能力を費やしていただいたことと思いますが、大変ありがとうございます。

単に式をまねるのではなく、式の意味を理解して、今後は自分でも周りの方の役に立てるよう頑張ってみます。

お礼日時：2009/10/27 23:07

No.3 回答者： tom04 回答日時： 2009/10/16 11:31

こんにちは！

参考になるかどうか判りませんが・・・
↓の画像のように表を作ってみました。

同じ内線番号を使っている方が複数いるみたいなので
その人数分だけ作業用の列が必要になります。
（今回の質問では二人なので作業列を2列にしています）

そして、内線番号だけ表示したのではどこの部署の○○さんか判らないとおもいますので、
部署も一緒に表示させるようにしています。

表のC2セルに氏名の一部でも入力するとその文字列が含まれている人をピックアップさせるようにしてみました。

作業列のA7セルに
=IF($C$2="","",IF(COUNTIF(E7:F7,"*"&$C$2&"*"),ROW(A1),""))
B7セルに
=IF($C$2="","",IF(COUNTIF(I7:J7,"*"&$C$2&"*"),ROW(A1),""))
として、A7・B7セルを範囲指定し、B7セルのフィルハンドルでオートフィルで下へコピーします。
（数式が100行まで対応できるようにしていますので、100行目くらいまでコピーしても構いません）

次に
D2セルに
=IF(COUNT($A$7:$A$100)>=ROW(A1),INDEX($D$7:$D$100,SMALL($A$7:$A$100,ROW(A1))),"")

E2セルに
=IF(D2="","",INDEX($C$7:$C$100,SMALL($A$7:$A$100,ROW(A1))))

F2セルに
=IF($C$2="","",IF(COUNT($B$7:$B$100)>=ROW(A1),INDEX($H$7:$H$100,SMALL($B$7:$B$100,ROW(A1))),""))

G2セルに
=IF(F2="","",INDEX($G$7:$G$100,SMALL($B$7:$B$100,ROW(A1))))

という数式を入れ、D2～G2セルを範囲指定し、G2セルのフィルハンドルで下へコピーすると画像のような感じになります。

これでC2セルに入力文字が増えていけば絞り込めるはずです。

本来であれば同じ項目データは列方向に複数あるよりも、行方向にあった方がもっと簡単なのですが、
条件として最初から列方向に複数ある！ということなので
こんな感じに考えてみました。

以上、参考になれば幸いですが、
他に良い方法があったり、的外れなら読み流してくださいね。
どうも長々と失礼しました。m(__)m

この回答へのお礼

お礼が遅れて大変申し訳ございませんでした。

そうですよね、私も複数の検索欄を考えてはみたのですが、複数の欄を設けるならば、普通に検索した方が速いので、やはり１っ箇所での検索を試みたかったんです。

お礼日時：2009/10/27 23:05

No.2 回答者： CoalTar 回答日時： 2009/10/15 23:39

C1セルに

=INDEX(B4:B6,MATCH("* "&B1&" *"," "&C4:C6&" ",0))
[Ctrl]+[Shift] ＋[Enter] で確定、配列数式です({}で囲まれる)

「鈴木一郎」と「鈴木一郎太」が区別されなくて良いなら
=INDEX(B4:B6,MATCH("*"&B1&"*",C4:C6,0))

＞(3)　同姓の方が居ない場合は名字だけでも検索可能
は まず無理。
↓苗字があるのかわかりませんが
坂上(さかがみ)、坂上(さかのうえ)、坂上田 なんてのがあったらアウト
判別付きません

この回答へのお礼

お礼が遅れて大変申し訳ございませんでした。

一列なら私もできたんですが、複数行にわたって記載されていたので、関数の組み合わせをする段階で挫折しておりました。

お礼日時：2009/10/27 23:03

No.1 回答者： kmetu 回答日時： 2009/10/15 22:46

普通の検索で探すのでは駄目なんでしょうか

Vlookupだと名前と内線が逆じゃないと駄目ですね。
B列とE列に名前C列とF列に内線番号
名前はB列とE列にしか書かない
で　C1　と　D1　にそれぞれどちらかに結果を出すようにすればいかがでしょう
=VLOOKUP(B1,B2:B100,2,FALSE)
=VLOOKUP(B1,E2:E100,2,FALSE)
名前は完全一致です。

この回答へのお礼

お礼が遅れて大変申し訳ございませんでした。

私も同じところで苦戦していました。

お礼日時：2009/10/27 23:01