EXCEL2003で、Vlookupの検索で複数ヒットした場合全て抽出したいんですが、可能ですか?
<sheet1>
A列 B列
1 A1000 1234
2 A2000 2345
3 A3000 3456
4 A1000 7777
5 A4000 4567
6 A1000 8888
<sheet2>
B1に関数を入れて、
A列 B列
1 A1000 1234・7777・8888
のように表示させたいんです。
過去質問を確認すると、imogasi方式や、オートフィルタを使用するとありましたが、
imogasi方式はいまいち理解ができず(っというか、有効な手段を見つけることができませんでした)
オートフィルタは使用上、今回は有効な手段ではありません。
また、Vlookupには拘りません。他の関数や、複数の関数を組み合わせても大丈夫です。
関数を使ってできるようであればご教示お願いします。
(複雑な式になっても構いません)
よろしくお願いします。
No.3ベストアンサー
- 回答日時:
VLOOKUP関数でできないこともないでしょうがかなり複雑になりますね。
次のように作業列を作って対応します。
シート1のC1セルに次の式を入力して下方にオートフィルドラッグします。
=IF(A1="","",A1&"/"&COUNTIF(A$1:A1,A1))
次にシート2のB1セルには次の式を入力して下方にオートフィルドラッグします。
=IF(COUNTIF(Sheet1!C:C,A1&"/"&1)=0,"",INDEX(Sheet1!B:B,MATCH(A1&"/"&1,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&2)=0,"","・"&INDEX(Sheet1!B:B,MATCH(A1&"/"&2,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&3)=0,"","・"&INDEX(Sheet1!B:B,MATCH(A1&"/"&3,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&4)=0,"","・"&INDEX(Sheet1!B:B,MATCH(A1&"/"&4,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&5)=0,"","・"&INDEX(Sheet1!B,B,MATCH(A1&"/"&5,Sheet1!C:C,0)))
この式では複数のデータが5個まであるケースに対応しています。
No.4
- 回答日時:
該当データの出現個数が一定数であれば関数の組み合わせや作業セルを多用することで対処可能ですが、計算式はとて無複雑なものになります。
出現個数が未知数の場合は想定範囲外の個数データーがあった場合は正しい結果にはなりません。
VBAを利用した抽出方法が適していますが、マクロは利用可能な環境でしょうか?
回答遅くなり申し訳ございません。
確かにVBAであれば適切なのでしょうが、訳あってマクロ使用ができない状態でした。
ありがとうございました。
No.2
- 回答日時:
VLOOKUP関数だけで対応するなら以下のようなVLOOKUP数式で2つ目以降のデータを表示することができます(C1セルに検索値で。
2つ目のデータ
=VLOOKUP(C1,OFFSET($A$1,MATCH(C1,$A:$A,0),0,100,2),2,0)
3つ目のデータ
=VLOOKUP(C1,OFFSET(A3,MATCH(C1,A:A,0)+MATCH(C1,OFFSET(A1,MATCH(C1,A:A,0),0,100,1),0),0,100,2),2,0)
この数式をIF関数を使ってつなげれば1つのセルに検索結果を表示することが可能です。
配列数式を使うなら以下のような関数になります(A列のデータ範囲に「データ」と名前が付けてある場合)
=SUBSTITUTE(TRIM(VLOOKUP(C1,A:B,2,0)&" "&INDEX(B:B,SMALL(INDEX((データ<>C1)*1000+ROW(データ),),2))&" "&INDEX(B:B,SMALL(INDEX((データ<>C1)*1000+ROW(データ),),3))&" "&INDEX(B:B,SMALL(INDEX((データ<>C1)*1000+ROW(データ),),4)))," ","・")
上記の数式は配列を多用していますので、入力セルが多くなると動きが重くなるので、上のVLOOKUP式などでエラー処理をしない数式などと適宜使い分けてください。
No.1
- 回答日時:
こんばんは!
VLOOKUP関数で複数のデータは抽出できないと思います。
VLOOKUP関数とMATCH関数での検索は出来ますが、一番上のデータしかヒットしないはずです。
そしてヒットした複数データを一つのセルに表示させたいということですが
これも関数では無理だと思います。
ただし、一つの案として↓の画像のように列方向にヒットしたものを表示させることは可能です。
Sheet2のB2セルに
=IF($A2="","",IF(COUNTIF(Sheet1!$A$2:$A$1000,$A2)<COLUMN(A1),"",INDEX(Sheet1!$B$2:$B$1000,SMALL(IF(Sheet1!$A$2:$A$1000=$A2,ROW($A$1:$A$999)),COLUMN(A1)))))
(Sheet1の1000行目まで対応できる数式にしています。)
これは配列数式になってしまいますので、
この画面からSheet2のB2セルに貼り付け後、F2キーを押す、又はB2セルでダブルクリック、又は数式バー内で一度クリックします。
編集可能になりますので
Shift+Ctrlキーを押しながらEnterキーで確定してみてください。
数式の前後に{ }マークが入り配列数式になります。
これを列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
どうしもて一つのセルに納めたいのであれば、
これを CONCATENATE関数か & でまとめていくくらいしか思い浮かびません。
以上、参考になればよいのですが
的外れなら読み流してくださいね。m(__)m
図解説明までして頂き、大変感謝します!
このような回答方法であれば、分かりやすくて助かります。
内容としては、求めていたものとは違い残念ですが、
とても勉強になりました。ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) ExcelのVLOOKUP関数 7 2022/08/23 06:46
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- Excel(エクセル) エクセル関数の変わった使い方 3 2022/05/13 17:12
- Excel(エクセル) Excelの関数についての質問です。(vlookup関数) A列 B列. C 1 大阪 50. 検索 6 2023/08/11 13:35
- Excel(エクセル) Excelの関数について教えてください。 5 2023/07/28 11:27
- Excel(エクセル) 【再度】Excelの関数について教えてください。 4 2023/07/28 13:06
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Excel(エクセル) Indirect関数について、Formulatextで抽出した数式を参照したい。 1 2022/12/15 11:16
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【Microsoft Forms】回答を削除...
-
【スプレッドシート】指定の日...
-
マイクロソフト 一時使用コード...
-
VLOOKUP関数について
-
Microsoft Formsの「個人情報や...
-
英数字のみ全角から半角に変換
-
Microsoft Formsで「応答」から...
-
1つのPCに「Excel 2010」「Exc...
-
vb.net オブジェクト指向につい...
-
会社におけるOfficeライセンス...
-
Office 2021 Professional Plus...
-
エクセルで英文字に入れた下線...
-
マクロ自動コピペ 貼り付ける場...
-
会社PCのメールが更新されない
-
Outlookでの時間指定送信機能に...
-
エクセルで串刺ししたシートの...
-
マイクロソフト オフィスについて
-
Excel テーブル内の空白行の削除
-
エクセルのシフト表を簡単にGoo...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報