Excel VLOOKUPでは複数該当する場合

質問

以下のような表があるとします。

　　　A　　　　B　　　　　C
１　　　　　　番号　　　氏名
２　　　　　　　１　　　 青木
３　　○　　　 ２　　　　井上
４　　　　　　　３　　　　上田
５　　○　　　 ４　　　　江口
６　　○　 　 ５　　　　太田

関数を使って、○が付いている人の氏名を表示させたいと考えています。
○が付いている人が一人しかいないならば、=VLOOKUP("○",A2$C6,3,FALSE)
でよいですが、複数名が該当する場合には使えません。
上表のように、複数が該当する場合において、例えば、○が付いている中で、「番号」が2番めに若い者の「氏名」を、関数で表示させる方法があればご教授願います。

回答No.1

keithin · ベストアンサー

とりあえずご質問の直接の回答としては
=IF(COUNTIF(A:A,"○")>1,INDEX(C:C,SMALL(IF($A$1:$A$100="○",ROW($A$1:$A$100)),2)),"")
と記入し、必ずコントロールキーとシフトキーを押してＥｎｔｅｒで入力します。
＃この手の計算式は、初心者さんは喜んで飛びつきますが、こんなイミフメイの計算式を使うのは愚策です。

＃もっとずっと簡単な方法
　　Ａ　Ｂ　Ｃ　Ｄ
１　　　　　　　氏名
２　　　　　　　青木
３　１　○　　　井上
４　　　　　　　上田
５　２　○　　　江口
６　３　○　　　太田

Ａ２：
=IF(B2="○",COUNTIF($B$2:B2,B2),"")
以下コピー

のようにして該当行に番号を計算で出して置けば、２をＶＬＯＯＫＵＰするだけで簡単にデータを引っ張ってこれます。

＃参考
　Ａ　Ｂ　Ｃ　Ｘ
１
２
３○　　　　　３
４
５○　　　　　５
６○　　　　　６

何かの理由でどーしてもＡ列を使いたくなければ、どこかてきとーなＸ列(別に同じシートじゃなくても構わない）に
Ｘ２：
=IF(A2="○",ROW(),"")
以下コピー

名前は
=INDEX(C:C,SMALL(X:X,2))
などのようにしても持ってこれます。

COUNTIFで１，２，３を並べる手と、ROW()で３，５，６を並べる手と、どちらでも好きな方法を使います。

お礼

御回答有難うございます。
もっとずっと簡単な方法を使わせて頂きました。
他の回答者の皆様もありがとうございました。

回答No.3

MarcoRossiItaly

No.1 さんの「もっとずっと簡単な方法」以下に書かれている方法をお勧めします。ベストアンサーは辞退します。

なお次式は、添付図のとおり、別表に結果を求めます。Ctrl+Shift+Enter の操作は不要です。

E2　1
F2　=index(C$2:C$1000,small(index(row(A$2:A$1000)-row(A$2)+1+9^9*(A$2:A$1000<>"○"),),e2))

該当がない（○がない）場合は、添付図のとおり、エラー値を表示してくれます。エラー値を表示したくないといったときは、上式を更に IFERROR 関数でテキトーに処理するなどします。

回答No.2

kagakusuki

>例えば、○が付いている中で、「番号」が2番めに若い者の「氏名」を、関数で表示させる

　まず、適当な列（ここでは仮に、H列とします）を作業列として使う事にして、B列において最初に番号が振られている行と、同じ行のH列のセルであるH2セルに、次の関数を入力して下さい。

=IF($A2="○",$B2,"")

　次に、H2セルをコピーして、H3以下に貼り付けて下さい。
　次に、「○が付いている中で、『番号』が2番めに若い者の『氏名』を表示させたいセル」に次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT($H:$H),"",INDEX($C:$C,MATCH(SMALL($H:$H,ROWS($2:2)),$H:$H,0)))

　以上です。

　因みに、「○が付いている中で、『番号』が2番めに若い者の『氏名』」だけではなく、「○が付いている全員の『番号』と『氏名』」を、「番号」が若い順に表示させる場合には、以下の様にします。
　今仮に、「○が付いている者のデータ」の内、「番号」をE列に、「氏名」をF列に表示させるものとします。

　まず、先程と同様に、H2セルに、次の関数を入力して下さい。

=IF($A2="○",$B2,"")

　次に、E1セルに次の関数を入力して下さい。

=$B$1

　次に、F1セルに次の関数を入力して下さい。

=$C$1

　次に、E2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT($H:$H),"",INDEX($A:$C,MATCH(SMALL($H:$H,ROWS($2:2)),$H:$H,0),MATCH(E$1,$A$1:$C$1,0)))

　次に、E2セルをコピーして、F2セルに貼り付けて下さい。
　次に、E2～F2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。

　尚、B列に入力されている「番号」に、同じものが複数回現れる様な事が無い場合で、「『番号』が若い順」ではなく、「B列、C列に入力されている行が上のものから順番に表示する」のでも宜しければ、作業列を用いずに済ませる方法もあります。
　但し、上の行から順番にしか表示出来ませんから、「○が付いているものの中の、上から2番目のデータのみを表示させる」という様な事は出来ません。

　まず、E1セルに次の関数を入力して下さい。

=$B$1

　次に、F1セルに次の関数を入力して下さい。

=$C$1

　次に、E2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($A:$A,"○"),"",VLOOKUP("○",INDEX($A:$A,MATCH($E1,$B:$B,0)+1):INDEX($C:$C,ROWS(E:E)),MATCH(E$1,$A$1:$C$1,0),FALSE))

　次に、E2セルをコピーして、F2セルに貼り付けて下さい。
　次に、E2～F2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。