エクセルのＶＬＯＯＫＵＰ関数について教えてください。

質問

エクセルのＶＬＯＯＫＵＰ関数について教えてください。

まずは、添付画像を参照下さい。
元データ（B2:D6）に対し、C10でＶＬＯＯＫＵＰ関数を以下の通り使用しました。

=VLOOKUP(B10,B2:D6,2,FALSE)

この場合、新品番57010に対し、旧品番が3パターンありますが、ＶＬＯＯＫＵＰ
を使用すると、その3パターンのうちいずれか（数値の小さいもの？）を勝手に
選択してしまうため、困っています。

このような一つの対象に対し、複数の対象が一致するものについては、
エラーの出るようにしたいのです。（作業上のミスを防ぐため）
そういったことは可能でしょうか。
あるいは関数の特性上仕方のないことなのでしょうか。

説明下手ですみませんが、よろしくお願いします。

回答No.1

tom04 · ベストアンサー

こんばんは！
複数ある場合は全てを表示するのではなく、単に複数あることが判断できれば良いわけですよね？
一応そういうことだとして・・・一例です。

↓の画像で左側がSheet1でSheet2に表示するようにしてみました。

Sheet2のB2セルに
=IF($A2="","",IF(COUNTIF(Sheet1!$A:$A,$A2)=0,"該当なし",IF(COUNTIF(Sheet1!$A:$A,$A2)>1,"複数あり",VLOOKUP($A2,Sheet1!$A:$C,COLUMN(B1),0))))

という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

以上、参考になれば良いのですが・・・m(__)m

お礼

ありがとうございました。
大変わかりやすかったため、
ＢＡとさせていただきました。

回答No.3

KURUMITO

Ｃ10セルに入力した式は=VLOOKUP(B10,B2:D6,2,FALSE)とのことですね。
Ｃ10セルからＤ10セルにオートフィルドラッグして下方にもオートフィルドラッグしてその式を使用するのでしたらＣ10セルに入力する式は少なくとも次のようにすることが必要ですね。

=VLOOKUP($B10,$B$2:$D$6,COLUMN(B2),FALSE)

セル番地については一部で絶対参照の形になっています。ドラッグコピーしてもセル番地が変わらないようにするために$マークを使います。さらに引数2の部分はCOLUMN(B2)と入力して式がＤ10セルにドラッグコピーされれば引数が自動的にCOLUMN(C3)に変わり3に変わるようにしています。
また、Ｂ10セルから下行のセルが空の場合にはエラーが表示されますのでさらに次のような式にすることが必要ですね。

=IF($B10="","",VLOOKUP($B10,$B$2:$D$6,COLUMN(B2),FALSE))

さらに、Ｂ10セルから下方にセルにデータが入力されてもそのデータがＢ2セルからＢ6セルの間に無ければエラーが表示されますね。該当する数値が無い場合にはＣ10セルやＤ10セルを空の表示にさせるのでしたら次のような式にします。

=IF($B10="","",IF(COUNTIF($B$2:B$6,$B10)=0,"",VLOOKUP($B10,$B$2:$D$6,COLUMN(B2),FALSE)))

ところでこの式を使ってもＢ10セルの値がＢ2からＢ10セルに複数ある場合には上から最初に検出されたところの行が答えとして表示されることになります。
同じデータが複数ある場合ですが、複数あるかどうかはCOUNTIF関数を使って調べることができますので次のような式にしてはどうでしょう。

=IF($B10="","",IF(COUNTIF($B$2:B$6,$B10)=0,"",IF(COUNTIF($B$2:$B$6,$B10)>1,"複数",VLOOKUP($B10,$B$2:$D$6,COLUMN(B2),FALSE))))

同じデータが幾つあるかを表示させたい場合には例えば次のような式にします。

=IF($B10="","",IF(COUNTIF($B$2:B$6,$B10)=0,"",IF(COUNTIF($B$2:$B$6,$B10)>1,"複数:"&COUNTIF($B$2:$B$6,$B10),VLOOKUP($B10,$B$2:$D$6,COLUMN(B2),FALSE))))

お礼

回答ありがとうございました。
大変参考になりました。
ＢＡにするかどうか大変迷いました。

回答No.2

shinkami

>このような一つの対象に対し、複数の対象が一致するものについては

無理でしょう。VlookUp関数は参考書の索引みたいなもので
一つの単語に対して取り扱っているページが「,」で区切って書いてありますね
どのページを見るかは読者の勝手です。

最初に索引テーブル［B3：D6］を整理しておきましょう
・索引部分［B3：B6］はユニークに
・参照部分［C3：Ｃ6］及び［D3：D6］は複数のものを［,］で区切って列挙します
［443100,443101］

尚,ご存知とは思いますが　C10の式は次行へ　コピペ　するためにテーブル部分は
絶対アドレスに

=VLOOKUP(B10,$B$2:$D$6,2,FALSE)

お礼

ありがとうございました。
参考にさせていただきました。