EXCEL関数：複数の離れた列から抽出する方法

質問

シート(1)に番号、会社名、電話番号の一覧を作成していますが、
シート(2)へ番号を入力すると、自動で文字が入力できる方法を探しています。

ちなみに、1列からの検索でVLOOKUPを使う方法は分かるのですが、
今回は、複数の離れた列から検索を掛けたい場合はどの様な関数が使えるのでしょうか。
具体的には、別紙の通りです。

宜しくお願いします。

回答No.5

tom04 · ベストアンサー

こんにちは！
すでに色々回答が出ていますが・・・
一例です。

↓の画像で上側がSheet1でSheet2に表示するようにします。
Sheet1はA～I列までのデータとしていますので、実状に合わせて範囲は変更してみてください。

Sheet2のB2セルに
=IF($A2="","",IF(COUNTIF(Sheet1!$A:$I,$A2),INDEX(Sheet1!$A$1:$I$100,SUMPRODUCT((Sheet1!$A$1:$I$100=$A2)*ROW($A$1:$A$100)),SUMPRODUCT((Sheet1!$A$1:$I$100=$A2)*COLUMN($A$1:$I$1))+COLUMN(A1)),"該当データなし"))

という数式を入れ、列・行方向にオートフィルでコピーすると
画像のような感じになります。m（_ _）m

お礼

回答有難う御座います。
色々試してみます。

回答No.6

MarcoRossiItaly

質問文の画像のとおりに 0 番台、100 番台、200 番台、…というふうに番号の列が分かれている場合は、次式でもいいですね。INDEX 関数の第 4 引数は、領域を番号で選択します。No.2 さんの CHOOSE と似たようなものです。

=vlookup(a2,index((sheet1!a:c,sheet1!d:f,sheet1!g:i),,,int(a2/100)+1),3,)

もっとも、いちばん一般的な方法は、No.4 さんのおっしゃるとおり、検索の元データを 3 列にすることだと思います。人に見せるなど、何らかの理由で 9 列にしたいなら、9 列の表のシートを 3 列のシートとは別に用意し、3 列から 9 列に参照する VLOOKUP を 9 列のセルに記入すればいいだけですね。

お礼

回答有難う御座います。
色々試してみます。

回答No.4

nobita925

複雑な操作がしたくないなら、入力・確認用にだけシート(1)の様に作成して、検索用にABC列だけのシートを作るのがもっとも簡単。
関数の使い方では無いですが・・・

お礼

回答有難う御座います。
確かにＡＢＣ列だけが簡単ですが、
数がかなり有る為、３列での作業は厳しく…。
色々試してみます。

回答No.3

tsubuyuki

「知っている（であろう）関数だけでわかりやすく」を求めるなら、
例えば「会社名」欄（シート２のＢ２セル）なら
　　=IF(COUNTIF(シート１!A:A,A2)=0,"",VLOOKUP(A2,シート１!$A:$C,3,FALSE))&
　　　IF(COUNTIF(シート１!D:D,A2)=0,"",VLOOKUP(A2,シート１!$D:$F,3,FALSE))&
　　　IF(COUNTIF(シート１!G:G,A2)=0,"",VLOOKUP(A2,シート１!$G:$I,3,FALSE))
　　　※便宜上、改行を入れていますが、実際は改行せずに続けて入力しましょう。

ＩＮＤＥＸ関数やＭＡＴＣＨ関数を使って考えるのも手段ですが、
現在、ご自身が知っている関数だけで作るのもまた有効な手段です。
そちらの方が「あとでわかりやすい」でしょうから。

参考までにどうぞ。

好みの問題もあり、余計なお世話と言われるかもしれませんが、
シート名に「括弧や演算記号など」あるいは「丸数字のような環境依存文字」は
使わないようにしておく方が良いですよ。
前者は式が読みづらくなってしまうから、
後者はパソコンが変わると上手く動かなくなる可能性があるから、です。

お礼

回答有難う御座います。
色々試してみます。

回答No.2

keithin

ご利用のエクセルのバージョンが不明のご相談なので、Ｅｘｃｅｌ２００７以降を使い
=IFERROR(VLOOKUP(A2,Sheet1!A:C,2,FALSE),"")&IFERROR(VLOOKUP(A2,Sheet1!D:F,2,FALSE),"")&IFERROR(VLOOKUP(A2,Sheet1!G:I,2,FALSE),"")
のように計算するのが一番わかりやすい方法です。

ちなみにご相談は「例えば」でしょうからダメなんでしょうけど、仮に「実際にそうである」なら
=VLOOKUP(A2,CHOOSE(INT(A2/100)+1,Sheet1!A:C,Sheet1!D:F,Sheet1!G:I),2,FALSE)
と言った具合に、一つにまとめる工夫ができるかも？しれません。

お礼

回答有難う御座います。
色々試してみます。

回答No.1

Cupper-2

横に離れているなら普通にVLOOKUP関数でOK。
検索する列を変えれば良いだけです。

検索する範囲をOFFSET関数で指定して列方向をCOLUMNS関数で指定するとか工夫してみましょう。

お礼

回答有難う御座います。
色々試してみます。