[[20180523181418]]　『検索でn番目にひっかかったセルの値を取りたい』（あみな）　ページの最後に飛ぶ

『検索でn番目にひっかかったセルの値を取りたい』（あみな）

特定の範囲の中で
条件１が○かつｎ番目（数値で指定）の条件２の値を取得したいです。

行　条件１　条件２
1　　○　　AAAAA
2　　　　　BBBBB
3　　○　　CCCCC
4　　　　　DDDDD
5　　○　　EEEEE

番号指定セル　ｎ

番号指定のセルに「3」と入力された場合「EEEEE」を取得

マクロは使わず関数だけで解決したいです。

よろしくお願いいたします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 配列数式での一例です
 (CtrlキーとShiftキーを押しながらEnterキーを押して数式を確定します)

 =IF(番号指定セル,INDEX(B:B,SMALL(IF(A1:A5="○",ROW(A1:A5)),番号指定セル)))

（白茶） 2018/05/23(水) 18:56

参考例を書いている間に、白茶さんがもっとよいものを提示されてますが、打っちゃったので投稿しておきます。

行　条件１　条件２作業列　　　（F列）
1　　○　　AAAAA　　 ★　　　　n番目か入力
2　　　　　BBBBB　　　　　　　【結果出力】
3　　○　　CCCCC
4　　　　　DDDDD
5　　○　　EEEEE

↑のようにC列を作業列として使用。

【手順１】
★のところに

 =IF(A1="〇",COUNTIF($A$1:A1,"〇"),"")

といれて　データがある最終行までコピー（オートフィル）

【手順２】
「F2」セルに以下の数式を入力

 =INDEX(B:B,MATCH(F1,C:C,0))

これでも出来る・・・・（とおもいます）

（もこな２） 2018/05/23(水) 19:05

 番号指定セルがC1セルだとして。
 =IFERROR(VLOOKUP(C1,IF({1,0},COUNTIF(INDIRECT("A1:A"&ROW(1:5)),"○"),B1:B5),2,FALSE),"")
 これはShift+Ctrl+Enterで式を確定。
 =IFERROR(INDEX(B1:B8,MATCH(C1,INDEX(COUNTIF(INDIRECT("A1:A"&ROW(1:5)),"○"),0),0)),"")
 こちらは通常通りEnterで式を確定。

（ねむねむ） 2018/05/24(木) 09:51

 白茶さん、
 偽の場合の処理が抜けているので番号指定セルが空白の場合、FALSEになってしまっている。
 =IF(番号指定セル,INDEX(B:B,SMALL(IF(A1:A5="○",ROW(A1:A5)),番号指定セル)),"")

（ねむねむ） 2018/05/24(木) 09:55

 ねむねむさんご指摘ありがとうございます。

 >偽の場合の処理が抜けている
 はい。そのつもりで書きました^^;
 FALSEとか#NUM!とかは、そのまま返ってきてる方が好きなもので。
 (この感覚そのものはご理解頂けるかと...)

 確かに質問者の方に対しては不親切でしたね^^;

（白茶） 2018/05/24(木) 10:49

 ＞これでも出来る・・・・（とおもいます） 
 ＞（もこな２） 2018/05/23(水) 19:05

 試しましたか？
 その作業列の式では出来ません･･･（断言）
（笑） 2018/05/24(木) 11:27

＞その作業列の式では出来ません･･･（断言）
うーん。なんかコピペ等ミスってますかね？

とりあえず、
A列、B列は手打ち
C1（その後、C5までフィル）、F2は、投稿内容から数式をコピペ

というのを、Win７/Excel2013で試してみましたが、ちゃんと「EEEEE」が返って来ているように見えるんですが・・・

私も完璧だなんてとても言えないので、おかしな点はご指摘いただくのはありがたいのですが、強い言葉で否定だけされるんじゃなくて、～～がおかしいですよと併せて問題点をご教授いただけると、お互い気持ちよく掲示板を利用できると思うのですがいかがでしょうか？
（もこな２） 2018/05/24(木) 12:44

 ○と〇では別文字だということかと。
 例に上がっている表をコピーしたものではもこな２さんの式をコピーした場合、上記のように文字が違っているので抜き出されない。

（ねむねむ） 2018/05/24(木) 12:49

＞ねむねむさんへ
なるほど。
表を手打ちしちゃったから気づかなかったんですね。
フォローありがとうございます。
（もこな２） 2018/05/24(木) 12:52

 あともこな2さんの式で作業列のほう、
 =COUNTIF($A$1:A1,"○")
 でも構わない。
 MATCH関数で検索の型が0の場合、上から順に検索して一番初めに見つかった位置を返すため。
（ねむねむ） 2018/05/24(木) 14:56

＞試しましたか？
＞その作業列の式では出来ません･･･（断言）
＞（笑） 2018/05/24(木) 11:27さん

どのような訳で断言されたのですか。

セル内の(○)をコピーし数式に貼り付けて
検証してみましたが何ら問題ありませんよ。

＞ ○と〇では別文字だということかと。
＞（ねむねむ） 2018/05/24(木) 12:49さん

記号と漢数字の違いがあります。
（H.O） 2018/05/24(木) 16:29

 ＞どのような訳で断言されたのですか。

 ＞=IF(A1="〇",COUNTIF($A$1:A1,"〇"),"")

 ↑ の式ではできないと言ってます。

 ＞セル内の(○)をコピーし数式に貼り付けて 
 ＞検証してみましたが何ら問題ありませんよ。

 数式を修正すればそりゃできるでしょ。
（笑） 2018/05/24(木) 17:24

コメント返信：

[ 一覧(最新更新順) ]

[[20180523181418]] 『検索でn番目にひっかかったセルの値を取りたい』（あみな） ページの最後に飛ぶ

[[20180523181418]]　『検索でn番目にひっかかったセルの値を取りたい』（あみな）　ページの最後に飛ぶ