vlookupの応用編を教えてください

質問

こんにちわ。
vlookupを使用していますが思うように結果が出せなくて困ってます。
お分かりの方いらっしゃれば教えてください。

ファイルにはsheet1とsheet2があります。
sheet2のB列には『=VLOOKUP($A$1,Sheet1!A1:B6,2,FALSE)』と入れてます。
すると、画像のように全ての行にA棟と出てしまいます。(トーゼンなのかもしれませんが・・・)
狙いはsheet２のB列にはA棟・C棟・E棟が出て欲しいわけです。

vlookupとしては「sheet２のA1セルと同じ文字列をsheet１から探しだし、その2列目の文字列を
表示させよ」という事なので、A棟ばかりを引っ張ってしまうことになってしまってます。
一度引っ張った文字列は除外して次にヒットした文字列(今回でいうC棟)を表示させていく術は
ありますか？

わかりづらくすみませんがよろしくお願い致します。

回答No.3

MackyNo1 · ベストアンサー

以下の数式のほうが、セル番地に関係なくB列の対象文字列を表示できるので紛れが少ないかもしれません（そのまま入力でOK）。

=INDEX(B列全体,SMALL(INDEX((A列のデータ範囲<>$A$1)*1000+ROW(データ範囲の行),),ROW(A1)))&""

例示のレイアウトに当てはめると以下の式になります。

=INDEX(Sheet1!B:B,SMALL(INDEX((Sheet1!$A$1:$A$100<>$A$1)*1000+ROW($1:$100),),ROW(A1)))&""

お礼

ありがとうございました。
狙い通りのものになりました。
数式、、難しいです。。

回答No.2

MAMORUM

元の表をソートして並び変えでは、だめなんですか。
ソート後であれば、希望の表示になると思いますが、今の表のままだと隙間があきます。

VLOOKUP($A$1,Sheet1!A1:$B$6,2,FALSE)
IF(Sheet2!$A$1=Sheet1!A3,VLOOKUP($A$1,Sheet1!A3:$B$6,2,FALSE),"")
IF(Sheet2!$A$1=Sheet1!A4,VLOOKUP($A$1,Sheet1!A4:$B$6,2,FALSE),"")
IF(Sheet2!$A$1=Sheet1!A5,VLOOKUP($A$1,Sheet1!A5:$B$6,2,FALSE),"")
IF(Sheet2!$A$1=Sheet1!A6,VLOOKUP($A$1,Sheet1!A6:$B$6,2,FALSE),"")
IF(Sheet2!$A$1=Sheet1!A7,VLOOKUP($A$1,Sheet1!A$6:$B7,2,FALSE),"")
一段ごとに適用の表を上から減らしていっています。）
参考までに

お礼

ありがとうございました。
この方法は試していたのですが範囲を固定してどうにかならないかなと思ってました。ただ教えて頂いたものでも結果は同じ（狙い通り）ですので方法としては間違っていなかったことがわかり助かりました。

回答No.1

tom04

こんにちは！
VLOOKUP関数のFALSE型は完全一致したものを返しますので、
最初にヒットした物だけが表示されてしまいますね！

そこで一例です。
お示しの配置通りとして・・・

Sheet2のB1セルに
=IF(COUNTIF(Sheet1!$A$1:$A$100,$A$1)<ROW(A1),"",INDEX(Sheet1!$B$1:$B$100,SMALL(IF(Sheet1!$A$1:$A$100=$A$1,ROW($A$1:$A$100)),ROW(A1))))

これは配列数式になってしまいますので、Shift＋Ctrlキー＋Enterキーで確定します。
この画面からコピー＆ペーストする場合はB1セルに貼り付け後
数式バー内で一度クリック！編集可能になりますので、
Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

これをオートフィルで下へコピーしてみてください。

参考になりますかね？m(__)m

お礼

ありがとうございました。
何回か試しましたが空白になってしまいます。
恐らくわたくしの入力に誤りがあると思いますが（間違いなく！）。。。
なんとかがんばって参考にさせていただきます。
ありがとうございました。