HLOOKUPでもVLOOKUPでもなくて、それに似た関数はありますか

Question

HLOOKUPでもVLOOKUPでもなくて、それに似た関数はありますか？

やりたいことは、
同じファイルにシート１とシート２があり、
シート１には
a列   b列       c列         d列
１　ミッキー　サッカー　チョコレート
２　ミニー　　野球　　　クッキー
３　ドナルド　テニス　　ビスケット
４　デイジー　水泳　　　ポテトチップス
・
・
・

という、10,000行ほどのエクセル表があり、
シート２には、

野球
水泳
・
・

などという風に、シート１のc列の文字列が全部ではなく、複数ある1列のみのエクセル表があります。

そして、このシート２のセルをそれぞれシート１で検索して、それを含む一行全部を表示した
エクセル表を新たにシート３に作成したいのですが、関数かVBAかで可能でしょうか？

例えば、この場合、シート３には

a列   b列       c列         d列
２　ミニー　　野球　　　クッキー
４　デイジー　水泳　　　ポテトチップス
・
・
・

というように表示したいのです。
HLOOKUPやVLOOKUPは検索される文字列は端っこしかダメなんですよね？

教えて下さい！！

kagakusuki · Accepted Answer

回答番号:ANo.3です。

>シート２で出た検索結果の行数によっては、シート３に関数をコピーした行数と合ってないと、
ゼロという結果が表示されてしまいますよね？

この解消法はあるのでしょうか？

　参照先が空欄の場合には、0を表示させたくない場合には、一般的には、次の様な操作を行います。

メニューの[ツール]をクリック
　　↓
現れた選択肢の中にある[オプション]をクリック
　　↓
現れた「オプション」ウィンドウの[表示]タグをクリック
　　↓
「ウィンドウ オプション」欄の[ゼロ値]と記されている箇所をクリックして、チェックを外す　　↓
「オプション」ウィンドウの[OK]ボタンをクリック

　但し、この方法では、関数の計算結果が0の場合も、表示されなくなってしまいます。

　関数の数式のみで対応する場合には、次の様な数式になります。


=IF(COUNTIF(Sheet1!$C:$C,Sheet2!$A1)=0,"",IF(INDEX(Sheet1!A:A,MATCH(Sheet2!$A1,Sheet1!$C:$C,0))="","",INDEX(Sheet1!A:A,MATCH(Sheet2!$A1,Sheet1!$C:$C,0))))

この関数を日本語で説明しますと、

「もし、Sheet1のC列の中に、Sheet2のA1と同じ値を持つセルの個数が0である場合には、空欄とし、そうではない場合において、もし、Sheet1のA列中の、上から数えて『Sheet1のC列中にあるSheetSheet2のA1セルと完全に一致する値を持つセルが存在する位置を、上から数えた際の数』番目にあるセルの値が、空欄である場合には、空欄とし、そうではない場合には、Sheet1のA列中の、上から数えて『Sheet1のC列中にあるSheetSheet2のA1セルと完全に一致する値を持つセルが存在する位置を、上から数えた際の数』番目にあるセルの値とする」

になります。

rukuku · Answer

＃７の訂正です
×　シート１のA2に
○　シート１のE2に

rukuku · Answer

オートフィルタを使う方法です。

E列を作業用に使います。
オートフィルタでは１行目がタイトル行として使われますので、
以下のようなタイトルをシート１の1行目に追加してください
A　　　　B　　　　　C　　　　D　　　　　E
NO．　　項目1　　項目2　　項目3　　作業列

そして、シート１のA2に以下の数式を入れ、データのある行分だけ
コピー（オートフィル）します。
=COUNTIF(Sheet2!A:A,Sheet1!C2)

シート２のリストに重複がなければ、E列の条件で「1」を選びます。
シート２のリストに重複がある場合には、「オプション-1以上」の設定をします。

rukuku · Answer

>VBAまったくの超初心者で、まったく何もわからず、図々しいお願いですが、
>そのVBAをちょこっと載せて頂けたら…

シート１
1ミッキーサッカーチョコレート
2ミニー野球クッキー
3ドナルドテニスビスケット
4デイジー水泳ポテトチップス
5ミッキーサッカーチョコレート
6ミニー野球クッキー
7ドナルドテニスビスケット
8デイジー水泳ポテトチップス

シート２
A列に
野球
水泳

として、以下のプログラムを試してみてください。

Sub　sumple1()
　　'変数の定義
　　Dim　Line1　　As　Long
　　Dim　Line2　　As　Long
　　Dim　Line3　　As　Long
　　Dim　LastLine1　　As　Long
　　Dim　LastLine2　　As　Long
　　
　　'sheet3のデータを消去
　　Worksheets("Sheet3").Cells.Clear

'最終行の取得
　　LastLine1　=　Worksheets("Sheet1").Range("A65536").End(xlUp).Row
　　LastLine2　=　Worksheets("Sheet2").Range("A65536").End(xlUp).Row

'条件に合うものをコピー
　　For　Line2　=　1　To　LastLine2
　　　　For　Line1　=　1　To　LastLine1
　　　　　　If　Worksheets("Sheet1").Cells(Line1,　3)　=　Worksheets("Sheet2").Cells(Line2,　1)　Then
　　　　　　　　Line3　=　Line3　+　1
　　　　　　　　Worksheets("Sheet3").Cells(Line3,　1)　=　Worksheets("Sheet1").Cells(Line1,　1)
　　　　　　　　Worksheets("Sheet3").Cells(Line3,　2)　=　Worksheets("Sheet1").Cells(Line1,　2)
　　　　　　　　Worksheets("Sheet3").Cells(Line3,　3)　=　Worksheets("Sheet1").Cells(Line1,　3)
　　　　　　End　If
　　　　Next　Line1
　　Next　Line2
End　Sub

並び順が期待通りでないと思ったら、上記のプログラムに続いて、以下のプログラムを実行してください。

Sub　sumple1a()
　　Worksheets("Sheet3").Cells.Sort　Key1:=Range("A1"),　Header:=xlNo
End　Sub

------------------------------------------------------------------
>オートフィルタですと、シート２にある文字列を一つずつフィルタを
>かけなくてはいけなくて
マクロを使って、「シート２にある文字列でフィルタを掛けて、
結果をシート３にコピーする」ということを順次行うことも出来ます。
という意味で回答しました。

⇒オートフィルタと関数を使えばもう少しうまく出来そうな方法を
　思いつきましたので別途回答します。

KURUMITO · Answer

回答Ｎｏ4です。
大変失礼をしました。肝心のシート3のＡ2セルへの入力の式は次のように訂正してください。なお、Ａ1セルへは文字列の入力が無くてもかまいません。

=IF(ROW(A1)>MAX(Sheet2!$C:$C),"",INDEX(Sheet1!$A:$C,MATCH(INDIRECT("Sheet2!A"&MATCH(ROW(A1)-1,Sheet2!$C:$C,1)+1)&ROW(A1)-INDIRECT("Sheet2!C"&MATCH(ROW(A1)-1,Sheet2!$C:$C,1)),Sheet1!$D:$D,0),COLUMN(A1)))

KURUMITO · Answer

重複のデータがたくさん含まれていても対応できます。
シート1やシート2、シート3ではいずれも2行まから下方にデータがあるとします。
シート1ではＤ列を作業列としてＤ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",B2&COUNTIF(B$2:B2,B2))

シート2でも作業列を作ります。
Ｂ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",COUNTIF(Sheet1!B:B,A2))

さらに、Ｃ1セルには0を入力して、Ｃ2セルには次の式を入力して下方いオートフィルドラッグします。

=IF(B2="","",SUM(B$2:B2))

シート3はお求めの表になりますがＡ1セルには何かの表題などを入力してください。Ａ1セルには必ず文字列が入力されていることが必要です。その上でＡ2セルには次の式を入力してＣ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet2!$C:$C),"",INDEX(Sheet1!$A:$C,MATCH(INDIRECT("Sheet2!A"&MATCH(ROW(A1)-1,Sheet2!$C:$C,1)+1)&ROW(A1)-COUNTA($A$1:$A1)+1,Sheet1!$D:$D,0),COLUMN(A1)))

これでシート2のＡ列に入力されているデータについて、シート1のＢ列で一致する行がすべて表示されることになります。

kagakusuki · Answer

Sheet1のC列に重複した値が無い場合には、INDEX関数とMATCH関数を組み合わせて使います。
　まず、Sheet3のA1に、次の数式を入力して下さい。

=IF(COUNTIF(Sheet1!$C:$C,Sheet2!$A1)=0,"",INDEX(Sheet1!A:A,MATCH(Sheet2!$A1,Sheet1!$C:$C,0)))

　続いて、Sheet3のA1セルをコピーして、Sheet3のリストの範囲に貼り付けて下さい。

　以上です。

rukuku · Answer

こんばんは

「関数で」というと分かりませんがは、VBAで実現することは出来ます。
全体の流れだけ、回答します。

一番単純な考え方では、
１．シート２の「野球」を取得する
　　↓
２．シート１のC列を上から順にチェックし、「野球」ならば、その行をシート３の一番下にコピーする
　　↓
３．シート１最終行まで来たら、シート２の次の項目である「水泳」を取得する
　　↓
４．シート１のC列を上から順にチェックし、「水泳」ならば、その行をシート３の一番下にコピーする
…以下、３～４を同様の繰り返しで出来ます。

もう少し気の利いた方法として、オートフィルタを使うことも考えられますが、1万行くらいなら上記の
方法でもそこそこのスピードで出来ると思います。

＞HLOOKUPやVLOOKUPは検索される文字列は端っこしかダメなんですよね？
HLOOKUPやVLOOKUPは「端っこ限定」ですが、VBAをうまく使えば「端っこ」でなくてもOKです。

キーワードは
・For To ～ Next　…繰り返し処理
・IF Then　…条件分岐
・End(xlUp).Row　…最終行の所得
などです。

一つ一つは基本的なことですが、うまく組み合わせて使うのは、「慣れ」が必要です。
分からない部分が出てきたら、またこのサイトで質問してみてください。
p(^^)q

imogasi · Answer

VLOOKUP関数で出来ると思います（Ｈｌｏｏｋｕｐ関数は本件では不適か）。Ｍａｔｃｈ関数もあります。ＶＢＡでも出来ます。
しかしデータを採ってくるのは関数では列単位（フィールド、項目単位）です。
あとは式の複写で対処します。
何かこの点についての誤解や未習得があるのでは（質問する理由が判らない）。
ーー
質問が読みにくく、わかりにくい。
実例を挙げて質問してください。
データ
引っ張ってくるデータ
完成形
をシートのデータの体裁系で記述してください。
わたしなぞ、エクセル関数の質問は、ほとんど例を挙げて回答してます。
ーーー
なおＧｏｏｇｌｅででも照会すれば、ＶＬＯＯＫＵＰ関数の説明、使用実例は腐るほどあります。

HLOOKUPでもVLOOKUPでもなくて、それに似た関数はありますか

回答番号:ANo.3です。

＃７の訂正です

オートフィルタを使う方法です。

>VBAまったくの超初心者で、まったく何もわからず、図々しいお願いですが、

この回答への補足

回答Ｎｏ4です。

この回答への補足

重複のデータがたくさん含まれていても対応できます。

Sheet1のC列に重複した値が無い場合には、INDEX関数とMATCH関数を組み合わせて使います。

この回答への補足

こんばんは

この回答への補足

VLOOKUP関数で出来ると思います（Ｈｌｏｏｋｕｐ関数は本件では不適か）。

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

　回答番号:ANo.3です。

　Sheet1のC列に重複した値が無い場合には、INDEX関数とMATCH関数を組み合わせて使います。