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.2

kaisendon

tasyさんすみません、後学の為に教えてください。
なぜ、作業列は作れないのですか？

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)))
（Ctrl + Shift Enter で配列数式として確定 → 下にコピー）

この式って難しいですよね？
作業列を使わないで、一発で求める事ができる式が
必ずしも「いい式」だとは思えません。

この式は、作業列を使いたくないが為に、
無理やりまとめて肥大化した式のような気がしてならないのです。

求めたい通りの結果が返ってくればそれでOK、ならば、
それはそれでいいのですが、
後々のメンテナンス性とか、別の方に引き継ぐ時とかの事を考えると、
好ましい式とは言えないと思います。

誰かがSheet2のA列をうっかり触ってしまって編集状態になり、
慌ててEnterで確定したら、式の｛　　｝が外れてしまって、
配列数式の意味を成さなくなってしまう、とか往々にしてありそうで怖いです。

レイアウトの関係とか、見た目とかの問題で作業列を使いたくないのであれば、『作業シート』を作ってそれを利用するのも一つの手だと思います。

例えば、作業シートのA1に
=IF(Sheet1!A1=Sheet2!$A$1,ROW(),"")
同じくB1に
=SMALL(A:A,ROW())
それぞれ、下に必用なだけコピー

Sheet2のA2に
=IF(COUNT(作業シート!A:A)<ROW()-1,"",INDEX(Sheet1!$C$1:$C$15,作業シート!B1))

このようにしておけば、作業シートのA列、B列の式が、
どのような処理を行っているのか明確になります。
ROW関数の返り値だけ気をつけていけば、
さほど難しい処理をしているわけではありません。

このように、式の可読性を高めて、自分が、あるいは他の人が見た時に、
少しでもわかりやすい処理にしておいた方が、後々何かと楽なのではないかと個人的には思っています。

まあ、こういったQ&Aの場合、
質問者は、一発で求めたい結果が返る式であれば、
数式が多少長くても、配列数式でも、なんでもいい、という傾向があるのは否めないですし、
回答者は、一発でドーンと答えの出る配列数式の方が回答しやすいという感じもあります。
まあ、私もその回答者の一人なんですけど(^_^;)
（実際、作業列の式がどうだこうだ書くより、先に書いた配列数式だけ書いて終わり、の方がよっぽど楽ですしｗ）

ただ、
> １つ条件として、作業列は作れないということです。
これがどうにも引っかかって仕方がないのです。
もちろん社内で使うのであれば、何かしらの制限やら、ご事情やらがある事でしょうから、表の構成の自由度が低くなるのは仕方のない事なのでしょうけれども、
冒頭に書いたように、
『何故作業列が使えないのか？』
これがどうしてもわからないのです。
後学の為に、作業列が作れない訳を教えて頂けませんでしょうか？

お礼

kaisendonさん、ありがとうございます。

そうですね。作業列や作業シートを使用できればもっと明確なのかもしれません。

今回、それが出来ないのは、
データをMSQueryでMSAccessからひっぱってきており、
指定したsheet1にデータを出力させ、フォーマットとしてあるsheet2に式で反映させているのですが、
そのフォーマットを作成し、印刷後、次の分を作成する為にsheet1の全データを削除するようになっているからです。
これは仕様の為、修正できません。

シートを勝手に増やすことも出来ない決まりなのです。

フォーマットのsheet2の中に作業列を作るのも、禁止となっています。

これがどんなおかしな理由であっても、
どんなに正当性があっても動かせない決まりなので、
今回はどうすることも出来ないのです。

ですが、kaisendonさんの教えてくださった作業シート版は大変参考になります。
ありがとうございました。