エクセル縦横検索

質問

エクセルの縦横検索で関数がわからず何日も悩んでいます。。

<Sheet1> は商品コード・ランクごとの単価表です。
　　Ａ列　　　　Ｂ列　　　　　Ｃ列　　　Ｄ列 　　 Ｅ列　　Ｆ列
1　　商品コード　商品名　 Ｓランク Ａランク ＢランクＣランク　
2　　00010001　ＡＡ-1111　10.00　11.00 　13.00　14.00
3　　00100022　ＡＢ-222　 11.00　12.00 　15.00　16.00
4　　00200033　ＢＤＢ-123 20.00　21.00 　22.00　23.00
5　　00300033　ＣＡ-111　　9.00　10.00 　 11.00　12.00
…　　・・・
10 　01000033　ＯＰ-100 　 30.00　31.00　 32.00　33.00
11　 02000123　ＴＤ-358　　10.00 11.00 　12.00　13.00
　 以下5000行ほど続きます。

<Sheet2>は得意先ごとのランク表です。
商品群は商品コードの頭２桁目と３桁目（00、01等）でランクが決まっている為、1行目に２桁の数字を入れています。

　 Ａ列　 　　　 　　Ｂ列　　　　　　 　　 　　 Ｃ列 　　Ｄ列 　　Ｅ列 …
1　　　　　　 　　 　　　　　　　　　　　 　　　 　 00　　 01　　　 02　　　 10　　20　
2 　得意先コード　得意先名／商品群　　AA　 AB　　 BDB　　 OP　TD　
3 　11111　　　　あああ　　　　　　　　　　　 Ｓ 　　 Ａ　　　 　Ｃ 　　 Ｂ 　Ｃ
4　 11222　　　　いいい　　　 　　　　　　　　Ａ　　 Ｃ　 　　　Ｂ 　　　Ｓ 　Ａ
5 　12333　　　　ううう 　　　　 　　　　　　　 Ｃ 　　 Ｓ 　 　 　Ｓ 　　 Ａ 　 Ｂ
6 　12444　　　　えええ　　　　　　　　　　　 Ａ　 　Ａ 　　 　 Ｂ　 　 Ｃ 　 Ｃ
7　 12555　　　　おおお　　 　　　　　　　　　Ｂ　　 Ｂ 　 　　Ｃ 　　 Ｓ　　Ｂ
… 　・・・
　 横に50列ほど続きます。

<Sheet3>は入力画面です。

　　 Ａ列　　　　　　　Ｂ列　　　　　　Ｃ列　　　Ｄ列
1 　 得意先コード　商品コード　　 ランク　　単価
2 　 11222　　　　　01000033　　　

得意先コードと商品コードを打ち込むとＣ２にランク（例だとS）が出るようにしたいのですが、
VLOOKUP($Ａ$2,Sheet2!,A1:Z100,MATCH(Ｃ2,Sheet1!A1:A5000,C1:F1))　とすると、アルファベットは出るのですが、ランク表どおりに出ません。01と10が同じ数字として認識されてしまいますし、２桁目と３桁目を認識する関数がわからないので、ここで止まっています。
ランクが出たら、隣の単価を求める関数もついでに教えていただけたらさいわいです。
よろしくお願い致します！

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.1

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

00、01や01000033などは数字(文字列)とします
C2セルに
=INDEX(Sheet2!$C$3:$Z$100,
MATCH($A2,Sheet2!$A$3:$A$100,0),
MATCH(MID(B2,2,2),Sheet2!$C$1:$Z$1,0))
としてはどうでしょうか？
また、単価がどこに書かれているかわかりませんが
=INDEX(Sheet1!$C$2:$F$5000,
MATCH(B2,Sheet1!$A$2:$A$5000,0),
MATCH(C2&"ランク",Sheet1!$C$1:$F$1,0))
のことでしょうか

お礼

単価というのは、sheet1のランクの下の10.00とか11.00とかの50000行続く数字です。
言葉足らずで申し訳ないです(汗）
さらに初心者で申し訳ないのですが、上の式にはSheet1は検索対象ではないのでしょうか？
とてもわかりやすい式なのですが、#N/Aが出るので・・・
もう少し考えてみます、ありがとうございました！

回答No.5

noname#204879

[回答番号：No.2この回答へのお礼]へのコメント、

》 実際の表はアルファベットのあとに‐があって、という法則じゃなく、
》 ‐がないものやカナから始まる商品名もあるので出来ません

規則性がないものは誰も「出来ません」。

》 それも説明しておくべきでしたね

仰るとおりで、その説明があれば私も無駄な時間を費やすこともありませんでした。

》 この式を応用できる技術がありません

例え貴方に技術があっても、規則性がないものには応用できません。

》 Ｄ２の式はＡとかＢとかがどの列にあるかわかってないといけないと
》 いうことでしょうか？

そうではありません。

》 得意先コードと商品コードから自動でランクが求められる式はありませ
》 んでしょうか？

ランクの位置は商品名の一部（AA、AB、BDB、OP、TD）と関係している限り、そして、その「商品名の一部」が商品名と規則性がないなら、ご希望の式はありません。

お礼

度々ありがとうございました。
説明不足ですみません。 そのような求め方があるとは知りませんでしたので。もっと勉強したいと思います。
貴重な時間をありがとうございましたm(_ _)m

回答No.4

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

＞単価というのは、sheet1のランクの下の10.00とか11.00とかの50000行続く数字です。
了解です。というか、
＞<Sheet1> は商品コード・ランクごとの単価表です。
を見落としてましたm(_ _)m

>とてもわかりやすい式なのですが、#N/Aが出るので・・・
数字(文字列)と数値(そのまま計算できる値)を比較したとき、一致しませんので、検索で見つからない#N/Aエラーとなることがあります。
配置の横位置が標準のときは、文字列は左揃え、数値は右揃えとなります。

ひとつのシートにサンプルを書き出し、試してみるのがよいと思います。
また
=MATCH($A2,Sheet2!$A$3:$A$100,0)
=MATCH(MID(B2,2,2),Sheet2!$C$1:$Z$1,0)
単価
=MATCH(B2,Sheet1!$A$2:$A$5000,0)
=MATCH(C2&"ランク",Sheet1!$C$1:$F$1,0)
どれで#N/Aエラーが出るか検討しましょう

お礼

度々ありがとうございます！
数字と文字列のちがいなんて、まるでわかっておりませんでした（汗）
そのとおりやってみたら、出ました！
出ましたが、、最初の０はカウントされず、０１と１０は１０のランクで認識してしまいますね。
単価は左端の単価しか出ないので、Sheetの表の方を何か加工しないといけないのかな？とも思いました。
今まではランクを自力で調べて、列番号の３と入れたら、Ｓランクの単価が出るような関数を使っていたので。
どうもありがとうございました！

回答No.3

tom04

こんばんは！
単価の件は無視しての回答になります。

↓の画像のようにSheet1のC列を作業列とさせてもらい
C2セルに
=IF(B2="","",LEFT(B2,FIND("-",B2)-1))
としてオートフィルハンドルの（＋）マークでダブルクリックします。

そして、Sheet3の
C2列に
=IF(COUNTBLANK(A2:B2)>0,"",INDEX(Sheet2!$C$3:$G$5000,MATCH(A2,Sheet2!$A$3:$A$5000,0),MATCH(VLOOKUP(B2,Sheet1!$A$2:$C$5000,3,0),Sheet2!$C$2:$G$2,0)))
という数式をいれてオートフィルで下へコピーします。

これで何とか希望に近い形にならないでしょうか？

尚、データは一応5000行まで対応できるようにしています。

以上、参考になれば幸いですが。
的外れなら読み流してくださいね。m(__)m

お礼

こんばんは。
ありがとうございます。
前にも書いたのですが、例題どおりではなく実際は"-"がないものもあり、
この式では無理らしいのです（汗）
ただこういうやり方もあるということはわかりましたので、
参考にさせていただきます。
ありがとうございました＾＾

回答No.2

noname#204879

Sheet3!C2: =VLOOKUP(A2,Sheet2!A$1:BZ$1000,MATCH(LEFT(VLOOKUP(B2,Sheet1!A$1:F$5000,2,FALSE),FIND("-",VLOOKUP(B2,Sheet1!A$1:F$5000,2,FALSE))-1),Sheet2!C$2:BZ$2,0)+2,FALSE)
Sheet3!D2: =VLOOKUP(B2,Sheet1!A$1:F$5000,MATCH(C2,Sheet1!C$1:F$1,0)+2,FALSE)

お礼

わ～！例題の表でやるとランクが出ました！
ありがとうございました。

ただ・・・実際の表はアルファベットのあとに‐があって、という法則じゃなく、‐がないものやカナから始まる商品名もあるので出来ません（汗）
それも説明しておくべきでしたね。
この式を応用できる技術がありません。
商品コードから検索するやり方がいいかと思うのですが・・・
あと、Ｄ２の式はＡとかＢとかがどの列にあるかわかってないといけないということでしょうか？
得意先コードと商品コードから自動でランクが求められる式はありませんでしょうか？