Excel2000 VLOOKUPで検査値は一緒で、列内容が違うものの全てをひっぱりたい

質問

初心者です。
似たような質問はいくつか見つかるのですが、理解力不足で自分に当てはめることが出来ません。
どうか、宜しくお願い致します。

sheet1
A　　　　　B　　　　D　　（←列番号）
No.1　　　　田中　　資格あ　　
No.1　　　　田中　　資格い
No.1　　　　田中　　資格う
No.2　　　　佐藤　　資格あ
No.2　　　　佐藤　　資格い
No.3　　　　鈴木　　資格あ
No.3　　　　鈴木　　資格い
No.3　　　　鈴木　　資格う
No.3　　　　鈴木　　資格え
No.3　　　　鈴木　　資格お
No.5　　　　西野　　資格あ

sheet1に上記のデータがあるとして、
ｓｈｅｅｔ2に検査値（No.）を入力し、その値と同じ人の資格名（C列）をひっぱりたいのです。

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
sheet2
A　　　　　B　　　　D　　（←列番号）
No.3（検査値）
資格あ
資格い
資格う
資格え
資格お
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

A1：=vlookup(A1,sheet1!A2:D6,3,)とすると、
1行目の「資格あ」だけは出てきます。
A2：資格い
A3：資格う・・・・と表示させたいのですが、
A2以降はどのような式を入れればよいのでしょうか。
どなたか教えて下さい。

１つ条件として、作業列は作れないということです。

どうか、宜しくお願い致します。

回答No.6

kaisendon

tasyさんありがとうございます。
作業列を使用できない理由を答えて頂き、感謝しています。
すっきりしました。

No1さんのお礼に所に『式の解説を…』と書いてあったので、
作業列を使えない理由を教えて頂いたお礼の意味を含めて、
私の式でよければ私の式の意味を解説してみたいと思います。

=IF(COUNTIF(Sheet1!$A$1:$A$15,$A$1)<ROW()-1,"",INDEX(Sheet1!$C$1:$C$15,SMALL(IF(Sheet1!$A$1:$A$15=$A$1,ROW(A$1:A$15)),ROW()-1)))

ポイントとしては、配列の範囲の行数を揃えるという事。
私の式では１５行分の配列を式に入れています。
No.4さんの式では、A2～A100とA1～A99で９９行分の配列です。

No4さんの式の場合、
Sheet1　A2　　　ROW(A1）
　　　　　　：　　　　　：
同　　　A100　　　ROW（A99）
各配列同士で、それぞれの行はこのように対応しています。

もう一つのポイントは、
この式はROW関数が３箇所に使われています。
このROW関数で作られる連番の数字をどう使っているのかを理解することが、この式を応用する為の早道だと思います。

質問の表で、列番号　D　が　C　だとして、
式を分解してみます。

COUNTIF(Sheet1!$A$1:$A$15,$A$1)
（Sheet2の）A1セルに入力された『検索値』が
Sheet1のA1～A15にいくつあるか数えます。
この数がSheet2でデータを表示させるのに必要な行数です。

IF(COUNTIF(Sheet1!$A$1:$A$15,$A$1)<ROW()-1,"",
先に数えた数を、表示される列の行番号を比較します。
この式は（Sheet2の）A2セルに入れたので、
A2セル　＝　表示させたいセルの一行目　＝　ROW()-1　＝　1
A3セル　＝　表示させたいセルの二行目　＝　ROW()-1　＝　2
と、変化していきます。
（後で出てくるSMALL関数の第二引数[順位]も、同じように）

質問文にあるデータでNo.5だと
A2セルでは　IF(1<{1}　→　{FALSE}　→　[偽の場合]のINDEX関数へ
A3セルでは　IF(1<{2}　→　{TRUE}　→　[真の場合]で　""に。

ここまでは、「エラー処理」と「表示されるのに必要な行を確保」をしているだけです。

INDEX(Sheet1!$C$1:$C$15,SMALL(IF(Sheet1!$A$1:$A$15=$A$1,ROW(A$1:A$15)),ROW()-1)))

INDEX(Sheet1!$C$1:$C$15
抽出したいSheet1のC1～C15のデータ範囲を配列として指定しています。

IF(Sheet1!$A$1:$A$15=$A$1,ROW(A$1:A$15)
私が作業シートのA列に入れた式
=IF(Sheet1!A1=Sheet2!$A$1,ROW(),"")　と同じです。
便宜上、『存在しない架空の作業列』とでも言えばいいでしょうか。
（Sheet2の）A1セルに入力された値と等しいものがSheet1のA1～A15にあった場合、ROW(A$1:A$15)で、その行に対応する行番号を『架空の作業列』に求め出しています。
（このIF関数の[偽の場合]は、不要なので省略しています）

元データが２行目からなら、
INDEX(Sheet1!$C$2:$C$16,SMALL(IF(Sheet1!$A$2:$A$16=$A$1,ROW(A$1:A$15))
３行目からなら、
INDEX(Sheet1!$C$3:$C$17,SMALL(IF(Sheet1!$A$3:$A$17=$A$1,ROW(A$1:A$15))
とかにすればよいでしょう。
ここのROW関数の配列は、元データの一行目を　１　としたいので、
数式を修正させる場合でも変化させません。
この場合、それぞれの１５行分の配列が、
先に書いた『No4さんの式の場合』のように、対応する配列になっています。

SMALL(……,ROW()-1)
作業シートのB列　=SMALL(A:A,ROW())　と同じです。
先ほど『架空の作業列』として求めた行の番号を小さい順上から詰めて並べるものです。
作業シートのB列の結果がどのようになっていたのかを見ると判りやすいかと思います。

結果を表示するセル（Sheet2のA2）が２行目なので、
SMALL関数の第二引数[順位]を、ROW()-1 として、
一行目は　SMALL(……,1)　二行目は　SMALL(……,2)　になるように調整しています。
（結果を表示するセルが３行目からならば、SMALL(……,ROW()-2)にするとか）
これで、数式を下にコピーする事により『架空の作業列』の値も、作業シートのB列のように並び変わります。

あとは、INDEX(Sheet1!$C$1:$C$15,行番号
これで、必用なデータを抽出して終わりです。
作業シートB列の値を見ながら、Sheet2のA2以下のセルで、
INDEX(Sheet1!$C$1:$C$15,作業シート!B1)
これを使い抽出していたのと同じです。

まあまあ、長々となってしまいましたが、
ざっくりと言えば、

=IF(COUNTIF(Sheet1!$A$1:$A$15,$A$1)<ROW()-1,"",INDEX(Sheet1!$C$1:$C$15,SMALL(IF(Sheet1!$A$1:$A$15=$A$1,ROW(A$1:A$15)),ROW()-1)))

（Sheet2の）A1と同じものが、Sheet1のA1～A15にいくつあるか数えて、
表示させるのに不必要な分は非表示に、
（Sheet2の）A1に入力された値と同じ値がSheet1のA1～A15にあれば、
それが、配列の何行目なのか、行番号を求めて、
その数字（行番号）を小さい順に（フィルコピーで）上から詰めるように並べる。
小さい順に並べられた数字が、INDEX関数で指定した配列（C1～C15）の行番号に相当するので、それを抽出する。

以上です。
お疲れ様でした(^_^;)