BCD列の下行にある文字列から、同行のA列の文字列を取り出したい

質問

長文、失礼します。
エクセルで、A列が人物名、BCD列が物品名として、シート１に
　 A　　 B　　 C　　 D　　
１青木　あさ　かず　あき
２山田　かわ　こめ　あめ　
３鈴木　こつ　あく
と言うブックを作り、これをベースとして
シート２
　 A　　 B
１あさ
２あき
３あめ
４あく
シート３
　 A　　 B
１かず
２かわ
シート４
　 A　　 B
１こめ
２こつ
のように、シート１を参照した物品名の母音別索引シートを作りました。
そこで今度は物品名に対応した人物名を、シート１からそれぞれのシートのB列に飛ばしたいのですが、VLOOKやHLOOK、INDEXやMACH等、思いつく関数を試してみましたが、どうしてもできません。

何か良い方法をご存知の方がいらっしゃればご教示下さい。

回答No.2

mu2011 · ベストアンサー

次の方法は如何でしょうか。
=IF(COUNTIF(Sheet1!$B$1:$D$3,A1),INDEX(Sheet1!A:A,SUMPRODUCT((Sheet1!$B$1:$D$3=A1)*ROW(A1:A3))),"")
※セル範囲は調整して下さい。

お礼

報告が遅くなりまして、申し訳ありません。
関数の意味が飲み込めずに四苦八苦しましたが、理解できれば意外にあっさりと表ができました。
エクセルは難しいと言うのが私の感想ですが、したいことが出来るのは気持ちが良いですね。
ありがとうございました。

回答No.3

noname#204879

スマートではないけど、次のような方法は如何かと。

１．Sheet1 の範囲 A1:D3 を選択して、[挿入]→[名前]→[作成]を実行
２．“左端列”のみにチェックを入れて[OK]をクリック

次に Sheet2 における作業手順を述べます。

Sheet2　　　　　　　　　　　　　　　　　 Sheet2
　　A　　 B　　 C　　 D　　　　　　　　　　　A　　 B　
1　　　　青木　山田　鈴木　　　　　　　　1　あさ　青木
2　あさ　青木　#N/A　#N/A　　→→→　　　2　あき　青木
3　あき　青木　#N/A　#N/A　　　　　　　　3　あめ　山田
4　あめ　#N/A　山田　#N/A　　　　　　　　4　あく　鈴木
5　あく　#N/A　#N/A　鈴木

１．セル A2 から下に「物品名」を入力
２．セル B1 から右に「人物名」を入力
３．セル B2 に次式を入力して、此れを範囲 B2:D5 に複写（→結果を上の左に示す）
　　=IF(MATCH($A2,INDIRECT(B$1),0),B$1)
４．セル A1 を選択して、[編集]→[ジャンプ]→[セル選択]を実行
５．“アクティブセル領域”に目玉を入れて[OK]をクリック
６．ステップ４に同じ
７．“数式”に目玉を入れた後、その配下の“エラー値”以外の項目のチェックを
　　外して[OK]をクリック
８．[編集]→[削除]を実行
９．“左方向にシフト”に目玉を入れて[OK]をクリック
10．Sheet2 全体を選択（←行番号枠と列記号枠の交点である左上隅をマウスでチョ
　　ーン）して、[コピー]→[値の貼り付け]を実行
11．１行目を削除

その結果を、上の右に示しています。

Sheet3、Sheet4 も同様の手順を実行すれば好い。

お礼

報告が遅くなりまして、申し訳ありません。
他の方の意見も参考にさせて頂き、どうにか思っていたことが出来ました。
教えて頂いた方法、こんな方法もあるのかと、とても勉強になりました。
詳しいアドバイス、ありがとうございました。

回答No.1

kapox

ちょっと、違うけど、作ってみました。

　 A　　 B　　 C　　 D　　
１青木　あさ　かず　あき
２山田　かわ　こめ　あめ　
３鈴木　こつ　あく
↑を「元表」シートとして、
仮に15行目くらいまで入力されると、
仮定して、それを、
「並替」シートにリンクさせる。
「元表」シートのBからB15へ「並替」シートのA1からA15
「元表」シートのC1からC15へ「並替」シートのA16からA30
「元表」シートのD1からD15へ「並替」シートのA31からA45
ただ、そのままだと、昇順に並び替えると、「あ」から順に
並んでくれないんで、
「並替」シートのA1は、=IF(元表!B1="","了",元表!B1)
「並替」シートのA16は、=IF(元表!C1="","了",元表!C1)
「並替」シートのA31は、=IF(元表!D1="","了",元表!D1)
としてやる。
で、「並替」シートのB列へは、それに対応した氏名を入れる。
「並替」シートのB1は、=INDIRECT("元表!A"&ROW(元表!B1))
「並替」シートのB16は、=INDIRECT("元表!A"&ROW(元表!C1))
「並替」シートのB16は、=INDIRECT("元表!A"&ROW(元表!D1))
という風に、
後は、オートフィルで、A1からA15に、
A16からA30に、A31からA45へコピーする。（B列も同じく）

後は、A1セルをアクティブにして、昇順で並び替えるだけ。
全昇順の対応した名前が表示される。
ちなみに、「了」が邪魔な場合は、「条件付書式」で、
出てきた時に、フォントが白色になるようにすれば、いいと思う。

アップローダーで、作ったファイルを上げておいたので、参考にして下さい。（条件付書式はしてません）

参考URL：

http://up.kabubu.net/cgi/img2/25663.zip

お礼

報告が遅くなりまして、申し訳ありません。
他の方の意見も参考にさせて頂き、どうにか思っていたことが出来ました。
お早いアドバイス、ありがとうございました。
本当に助かりました。