VLOOKUP関数を使用していて質問です。

質問

VLOOKUP関数を使用していて質問です。
A1からE10までサイズや商品コード商品名等が入っているとします。
A1のサイズに該当するものがあれば5列目（E列）の商品コードが出るようにしてあるのですが、A1のサイズが重なってきました。
つまり、A列には横幅、B列には高さ、C列には奥行きが入ってます。
今まで横幅100高さ100奥行き100しかなかったのですが、横幅100高さ100奥行き150のサイズが増えてきたというわけです。A1の100、B1の100、C1の100や、A2の100、A2の100、A3の150のように横・高さ・奥行きの三つがぴたりと合えばE列の商品コードが出るようにするにはどうしたらいいのでしょうか？
VLOOKUP関数ではないような気がするのですが・・・。
よろしくお願いします。

回答No.11

MackyNo1 · ベストアンサー

補足情報があるのを見落としていました。
＞＞「挿入」「名前」「定義」で例えば「A列」と名前を付け、参照範囲に以下の数式を入力します。
＞これはシート1のA1のセルでよろしいのですね？

最初に提示したSheet1のA１セルの絶対参照の数式ならどのセルで名前定義してもOKですが、相対参照にしてほかの行のデータにも反映させたい場合は、数式を入力するセル（D1セル）ですべて名前定義する必要があります。

実際のデータにあわせて数式を変更すると数式を間違える可能性が考えられるので、ひとまず新規ブックのA1セルにたとえば「１００」の数字を入力し、Sheet2のA列に「１００」B列とC列にSheet1のB1とC1セルの値があるデータを作って、すべての数式をコピー貼り付けして設定してみて、Sheet2のE列のデータがうまく引っ張ってこられるかどうか確認してください。

最終的にシート名を変更したようなブックで設定する場合は、シート名とA列のデータの一覧表を使って、それをセル参照するほうが間違いが少ないかもしれません。

お礼

ご丁寧にありがとうございました。
しばらくの間はNo.5の方法でやっていくことにしました。

どうもありがとうございました。

回答No.10

MackyNo1

補足情報です。

名前定義で数式に利用する場合、D1セルだけでなく（A1セルを参照するだけでなく）その下のセルにも同様の数式を入力するなら、名前定義の際にD1セルを選択した場合は以下の相対参照した数式を入力してください。

=LOOKUP(Sheet1!A1,{100,200,300},{"Sheet2","Sheet3","Sheet4"})&"!A1:A100"

回答No.9

KURUMITO

シート2のデータベースとなる表に様々な横、高さ、奥行きの違うデータを下の行に入力してそれに対する商品コード番号を入力ことで、それぞれのシートに商品コードとサイズの関係を記した表を作ることは必要ありません。シート2まとめておくことですべての他のシートで同じ式をコピーして使うことができます。

回答No.8

MackyNo1

＞シート1に先ほどの式を入れたら、横100高さ・・奥行き・・を入れたらシート2から探してくれる。また横400高さ・・奥行き・・と入れたらシート5から探してくれる。　というわけにはいかないのでしょうか？

もちろん、いろいろなアプローチで実行可能ですが、私の回答に対する補足がないようなのでExcel2007の場合で回答すると、以下のようなIFERROR関数で条件を分岐して追加するのが最も簡単な対応です。

=IFERROR(IFERROR(Sheet2を検索する数式,Sheet3を検索する数式）,Sheet4を検索する数式）

検索する数式が多い場合や、古いバージョンのエクセルを使用している場合は、例えば以下のような名前を定義します。

「挿入」「名前」「定義」で例えば「A列」と名前を付け、参照範囲に以下の数式を入力します。

=LOOKUP(Sheet1!$A$1,{100,200,300,400},{"Sheet2","Sheet3","Sheet4","Sheet5"})&"!A1:A100"

上記の,{100,200,300,400}や{"Sheet2","Sheet3","Sheet4","Sheet5"}の部分は一覧表を作成してセル参照することもできます。

同様に「B」列」と名前をつけA1:A100の部分をB1:B100に変更した数式を入力します（コピーしてAをBに変更）。

同様に「C列」「E列」の名前を定義しておきます。

このように名前定義しておけば、Sheet1のA１、B1、C1セルに3つの数字が入力してある場合、D1セルに以下の数式を入力すれば該当するシートのE列のデータを表示することができます。

=INDEX(INDIRECT(E列),MATCH(A1&B1&C1,INDEX(INDIRECT(A列)&INDIRECT(B列)&INDIRECT(C列),),))&""

補足

ご丁寧にありがとうございます。
チャレンジしてみましたがうまくいきません。

＞「挿入」「名前」「定義」で例えば「A列」と名前を付け、参照範囲に以下の数式を入力します。
これはシート1のA1のセルでよろしいのですね？B1のセルにはB列C1のセルにはC列と名前・定義・参照範囲を行いました。シート1のD1に最後に書いてくださった数式をそのまま貼り付けましたが、D1には「#N/A」と出ます。

また、上記の,{100,200,300,400}や・・は、便利そうなのはわかりますが今は、シンプルにこの数式を理解していきたいので、後回しにします。

遅くなりましたがエクセルは2003です。該当するサイズのものがない場合はエラーが出るほうがよいのです。よろしくお願いします。

回答No.7

KURUMITO

多数のシートで商品コードを表示させることが必要な場合にはお示ししたように例えばシート2にデータベースを用意しておき、それを他のシートで利用すればよいでしょう。
同じシートの中にデータベースとなるものを取り入れて使うのはあまり推奨できません。
例えばシート1で入力した式は他のシート3やシート5でもそのまま使用することができます。

補足

ありがとうございました。
私の説明不足のようでした。
シート2に横幅が100で高さと奥行きがさまざまなものが入ってます。
シート3には横幅が200で高さと奥行きがさまざまなものが入ってます。同様にシート4には横幅300のものシート5には横幅400のものがあるとします。
シート1に先ほどの式を入れたら、横100高さ・・奥行き・・を入れたらシート2から探してくれる。また横400高さ・・奥行き・・と入れたらシート5から探してくれる。　というわけにはいかないのでしょうか？

回答No.6

MackyNo1

参考までに。

該当データがない場合にエラー表示させないなら、条件部分に元の数式をISNA関数などで判定する必要がありますが、同じ配列を繰り返し用いると数式が重くなるので、間違えたデータの入力（#N/Aエラー）には対応する数式を提示しませんでした。

ご使用のエクセルのバージョンが明記されていないのですが、エクセル2007ならIFERROR関数で簡単にエラー処理をすることができます。

=IFERROR(元の式,"")

＃バージョンによって使用できる機能や操作方法が異なりますので、質問の際にはご使用のバージョンなどを明記するようにしましょう。

ちなみに、No4の回答者のIF関数の「COUNTIF(Sheet2!A:A,A1)=0」などの部分ですが、この条件では各列の値は存在するが、該当するデータの組み合わせのない場合にはエラー表示されることになりますので注意してください。

回答No.5

MackyNo1

A列からE列に元データがあり、G2、H2、I2セルに横・高さ・奥行きのデータが入力されている場合、以下のような配列数式の方がいく分計算負荷が少ないかもしれません。

=INDEX(E:E,MATCH(G2&H2&I2,INDEX($A$1:$A$100&$B$1:$B$100&$C$1:$C$100,),))&""

回答No.4

KURUMITO

例えばシート2にはデータベースの表があるとして、Ａ1セルから以降には横幅、Ｂ1セルから以降には高さ、Ｃ1セルから以降には奥行きの数値がそれぞれあり、Ｅ1セルから以降には相当する商品コードがあるとします。
そこでシート1の作業でＡ1セル以降には横幅、Ｂ1セル以降には高さ、Ｃ1セル以降には奥行きのデータをそれぞれ入力するとして、Ｄ1セル以降に商品コードを表示させるとしたらＤ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(COUNT(A1:C1)<>3,COUNTIF(Sheet2!A:A,A1)=0,COUNTIF(Sheet2!B:B,B1)=0,COUNTIF(Sheet2!C:C,C1)=0),"",INDEX(Sheet2!E:E,SUMPRODUCT((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100=B1)*(Sheet2!C$1:C$100=C1)*ROW(Sheet2!A$1:A$100))))

補足

できました。ありがとうございました。
ところで、シート1とシート2にまたがらないといけないのでしょうか？シート1にデーターがあり、その中で探すことはできないのでしょうか？一枚のシートではムリなのでしょうか？

また、逆になるのですが、シート2シート3シート4などと多数のシートにデーターがある場合はどうしたらよいのでしょうか？
よろしくお願いします。

回答No.3

aokii

横幅、高さ、奥行きの文字をつなげて、
F2セル=A2&"-"&B2&"-"&C2
G2セル=E2
として、
VLOOKUP関数で、F列に該当するものがあれば、G列の商品コードが出るようにしてはいかがでしょう。

補足

ありがとうございます。
実は、たくさんのデーターが入ってましてF列に100-100-100とか100-100-150とか入れる作業なしで進めたいのです。
無理なのでしょうか？

回答No.2

seiryu2989

こんにちわ
私なら
Ｆ列でも新しい列にＡ列＆Ｂ列＆Ｃ列をいれますね
ただ数字の羅列だと
他の列との違いが出なくなるので
Ｆ列＝”Ｗ”＆Ａ列＆”Ｈ”＆Ｂ列＆”Ｌ”＆Ｃ列
という風な工夫をしますね

後は今までのＶＬＯＯＫＵＰ関数でできると思います

補足

ありがとうございます。
実は、たくさんのデーターが入ってましてF列に100-100-100とか100-100-150とか入れる作業なしで進めたいのです。
無理なのでしょうか？

回答No.1

某HN クロメート(Chromate)（@CoalTar）

とりあえず数式で２案、エラー処理なし

1. 検索系
F2セルに =A2&"-"&B2&"-"&C2 下へオートフィル
J2セル =INDEX(D:D,MATCH($G2&"-"&$H2&"-"&$I2,$F:$F,0))
右へ下へオートフィル

2. データベース関数
=DGET($A$1:$E$10,H4,$G$1:$I$2)
右へオートフィル

添付図参照