エクセル VLOOKUP について

質問

エクセル関数について教えてください。

通常VLOOKUPは元データの右側の検索だと思いますが
左右両方への検索出来る方法はありますか？

例えば下のような配列の元データ（sheet2）があったとします。

　　A　 　B　　C　　 D　　 E
1　あ　　い　　う　　え　　お

2　か　　き　　く　　け　　こ

3　さ　　し　　す　　せ　　そ

sheet1の表「う（C1）」からの検索で左側の「あ（A1）」も右側の「お（E1）」も検索し、sheet1へ表示出来るようにしたいです。

仕事での管理書式で使用したいのですが、元データが客先支給なので配列が変えられません。

VLOOKUPにこだわってませんので何か方法はありますか？
よろしくお願いします。

回答No.5

tom04 · ベストアンサー

No.3です。
補足を読ませていただきました。

受注品SheetのA列は何とか問題なく表示できているとして、
C・D列の数式を知りたい訳ですね！

それぞれの列に数式を別途入れてもOKなのですが、
両Sheetの1行目項目名は一致しているようですのでC2セルだけに数式を入れてみました。

「受注品」SheetのC2セルに

=IF($B2="","",INDEX(受注データ!$A$2:$B$6,MATCH($B2,受注データ!$C$2:$C$6,0),MATCH(C$1,受注データ!$A$1:$B$1,0)))

という数式を入れ、隣のD2セルまでオートフィルでコピー！
そのまま下へコピーではどうでしょうか？m(_ _)m

お礼

ご回答ありがとうございます。

バッチリです！o(^▽^)o

助かりました。

わからない事はまた投稿させて頂きますので

今後ともよろしくお願いします！m(__)m

回答No.6

KURUMITO

回答No4です。
シート1に表示させるのでしたね。失礼しました。
シート1のA1セルにシート2のC列からの検索値「う」を入力し、シート2のA列およびE列のデータをシート1のA2セルとB2セルに表示させるのでしたら次の式をシート1のA2セルに入力しB2セルまでドラッグコピーすればよいでしょう。

=IF($A$1="","",INDEX(Sheet2!$A$1:$E$5,MATCH($A$1,Sheet2!$C$1:$C$5,0),IF(COLUMN(A1)=1,1,5)))

A1セルには「う」や他の「く」、「す」などの文字を入力してもよいでしょう。それ以外の文字が入力された場合にはエラーとなります。
エラーを回避するためには例えば次のような式にします。

=IF($A$1="","",IF(COUNTIF(Sheet2!$C$1:$C$5,$A$10)=0,"C列にデータが有りません",INDEX(Sheet2!$A$1:$E$5,MATCH($A$1,Sheet2!$C$1:$C$5,0),IF(COLUMN(A1)=1,1,5))))

シート2ではA列からE列で多くの行が使われるのでしたら列のみの式で良いので上の式は次のように書くこともできます。

=IF($A$1="","",IF(COUNTIF(Sheet2!$C:$C,$A$1)=0,"C列にデータが有りません",INDEX(Sheet2!$A:$E,MATCH($A$1,Sheet2!$C:$C,0),IF(COLUMN(A1)=1,1,5))))

お礼

ご回答ありがとうございます。

ものすごく勉強になります。o(^▽^)o

エラー表示も出来るんですね！

まだまだ色々出来そうな気がしてきました。

わからない事はまた投稿させて頂きますので

今後ともよろしくお願いします！m(__)m

回答No.4

KURUMITO

お示しの表がA1セルからE5セルの範囲内にあるとします。
C列で鵜を検索しA列とE列のデータを表示させるということですね。
例えばA10セルにうと入力してA11からB11セルにそれらのデータを表示させるとしたら次の式をA11セルに入力してB11セルまでドラッグコピーします。

=IF($A$10="","",INDEX($A$1:$E$5,MATCH($A$10,$C$1:$C$5,0),IF(COLUMN(A1)=1,1,5)))

A10セルにはうや他のく、すなどの文字を入力してもよいでしょう。それ以外の文字が入力された場合にはエラーとなります。
エラーを回避するためには例えば次のような式にします。

=IF($A$10="","",IF(COUNTIF($C$1:$C$5,$A$10)=0,"C列にデータが有りません",INDEX($A$1:$E$5,MATCH($A$10,$C$1:$C$5,0),IF(COLUMN(A1)=1,1,5))))

お礼

ご回答有難うございます。

今回は1シート内ではなく

sheet1へ検索値「う」の入力
　↓
sheet2の元データから検索
　↓
sheet1の別のセルへ検索値「あ」の表示

だった為、書いて頂いた式だと私には応用出来ませんでした。
（能力不足ですみません。m(__)m）

しかし、別の用途でかなり使用できるので、ものすごく参考になりました。

有難うございます。今後ともよろしくお願いします！

回答No.3

tom04

こんばんは！
Sheet2の表内で、検索データがある行のものをすべて表示したいというコトですよね？

↓の画像で右側がSheet2で、Sheet1のA1セルに検索データを入力するとします。

Sheet1のA3セルに
=IF(COUNTIF(Sheet2!$A:$E,$A$1),INDEX(Sheet2!A$1:A$100,SUMPRODUCT((Sheet2!$A$1:$E$100=$A$1)*ROW($A$1:$A$100))),"")
という数式を入れ、列方向にオートフィルでコピーすると
画像のような感じになります。

※　Sheet2の表には重複データはない！という前提です。
※　とりあえずSheet2の100行目までの範囲としています。m(_ _)m

補足

ご回答ありがとうございます。

やりたい事と大体合っていると思いますが、実際に関数を作ってみると上手く応用出来ません。
（COUNTIF、INDFX、SUMPRODUCTなど勉強不足で使用したことがありません。m(__)m）

もう少し教えてください。

sheet名：受注データ
......A...........B..................C................D
1..数量.....商品名......受注NO...受注日
2......10......りんご.........G001.......1月17日
3.........8......みかん.......G008.......1月18日
4.........5......りんご.........G004.......1月18日
5.........4......いちご.........G011.......1月19日
6.........6......ぶどう.........G016.......1月19日

...でセル毎に仕切ってあると思ってください。

例えば上のような元データ「受注データ」のsheetがあります。
※このシート配列は客先支給データの為、変えられません。

C2～6の「受注NO」を下の「受注品」というsheetのB2～6に入力した場合に
A:受注日、C：商品名、D数量が表示されるようにしたいです。
※受注NOは重複する事はありません。

sheet名：受注品
...........A...................B.......................C.......................D
1.....受注日........受注NO.........商品名.........数量
2.............................G001
3.............................G004
4.............................G011
5.............................G008
6.............................G016

完成希望の表が下の様な感じです。

sheet名：受注品
..........A....................B...................C.................D
1....受注日........受注NO........商品名.......数量
2...1月17日.........G001............りんご..........10
3...1月18日.........G004............りんご...........5
4...1月19日.........G011............いちご...........4
5...1月18日.........G008............みかん..........8
6...1月19日.........G016............ぶどう...........6

受注日： A2セルへ=VLOOKUP(B2,受注データ!$C$2:$D$6,2,FALSE)を
　　　　　入力してA3～6へコピーで表示可能
商品名：　教えてください。
数量　：　教えてください。

大変申し訳ありませんが、教えてください。m(__)m

回答No.2

CC_T

検索対象がＣ列のみかどうか不明ですが、重複が無いなら条件に一致する行をMATCH関数で取得すれば、それを使って左右の分を表示させる方法がとれるのではないでしょうか。

お礼

ご回答頂き有難うございます。
MATCH関数をよく勉強してみます！
今後ともよろしくお願いします。

回答No.1

kybo

MATCH、INDEXの組み合わせでどうでしょうか？

「お」の列、Ｅ列を検索
=INDEX(Sheet2!E1:E10,MATCH("う",Sheet2!C1:C10,0))

「あ」の列、Ａ列を検索
=INDEX(Sheet2!A1:A10,MATCH("う",Sheet2!C1:C10,0))

お礼

ご回答頂き有難うございます。
MATCH関数をよく勉強してみます！
今後ともよろしくお願いします。