INDEX関数で2列からの参照は？

質問

INDEX関数で2列からの参照は？

表1 料金表

A列　B列　C列　D列　
　　　　　　　　　　　着地　　北海道　関東　関西　
発地　サイズ名　発／着地番号　　1　　　2　　3　
北海道　60S　　　1 500 1,200 1,500
北海道　80S　　　2 700 1,500 1,750
北海道　100S　　3 1,000 1,750 1,900
関東　　60S　　　4 750 500 800
関東　　80S　　　5 950 700 1,000
関東　　100S　　6 1,100 800 1,300
関西　　60S　　　7 1,500 800 500
関西　　80S　　　8 1,750 1,000 750
関西　　100S　　9 1,900 1,300 900

上の表を使って、下の表の料金を求めます。

表2
A列　　B列　　C列　　D列　　E列
氏名　発地　　着地　サイズ　料金
あ　　北海道　関西　60S
い　　関東　　北海道　80S
う　　関西　　関東　80S
え　　関東　　関西　100S

このような場合、料金を求めるには、INDEX関数でいいと思うのですが、2列を参照しないといけないので、どのように式を作ればいいでしょうか？
SUMPRODUCT関数を使えばいいですか？

どなたか教えて下さい。よろしくお願いします。

回答No.1

sige1701 · ベストアンサー

A B C D E F
1 着地 北海道 関東 関西
2 発地 サイズ名 発／着地番号 1 2 3
3 北海道 60S 1 500 1,200 1,500
4 北海道 80S 2 700 1,500 1,750
5 北海道 100S 3 1,000 1,750 1,900
6 関東 60S 4 750 500 800
7 関東 80S 5 950 700 1,000
8 関東 100S 6 1,100 800 1,300
9 関西 60S 7 1,500 800 500
10 関西 80S 8 1,750 1,000 750
11 関西 100S 9 1,900 1,300 900
12
・
・
19
20 氏名 発地 着地 サイズ 料金
21 あ 北海道 関西 60S 1500
22 い 関東 北海道 80S 950
23 う 関西 関東 80S 1000
24 え 関東 関西 100S 1300

E2=INDEX($D$3:$F$11,MATCH(B21&D21,INDEX($A$3:$A$11&$B$3:$B$11,),0),MATCH(C21,$D$1:$F$1,))
とか
E2=INDEX($D$3:$F$11,SUMPRODUCT(($A$3:$A$11=B21)*($B$3:$B$11=D21),$C$3:$C$11),MATCH(C21,$D$1:$F$1,))

お礼

できました！
発着地番号はなぜここに存在するのかがよく分からなかったのですが、
納得しました。
勉強になりました。
ありがとうございました。

回答No.6

imogasi

２列にそれぞれ該当するものを見つけ出すためには、MatchやVKOOUP関数を使うためには、
2列のデータを結合した、新しい列を作るのが考えやすいし、それしかない。式を複雑になるのを覚悟で、それでも良いという場合は、既出回答に出ているかもしれないので、この回答は当たらないが。
ーーー
例データ　A1:H10　－は空白セル。本当は見出しが入るのかも。
質問のデータの右列に８の３０００を追加。
第1行の「８」の列は、画面に収まるようにH列に持ってきたが、E列から８番目の列に本当は来る。
- - - - 1 2 3 8
北海道 60S 北海道60S 1 500 1,200 1,500 3000
北海道 80S 北海道80S 2 700 1,500 1,750
北海道 100S 北海道100S 3 1,000 1,750 1,900
関東 60S 関東　60S 4 750 500 800
関東 80S 関東　80S 5 950 700 1,000
関東 100S 関東　100S 6 1,100 800 1,300
関西 60S 関西　60S 7 1,500 800 500
関西 80S 関西　80S 8 1,750 1,000 750
関西 100S 関西　100S 9 1,900 1,300 900
仮に固定行第１５行に条件を入れるとする。A15:C15
北海道 関西 60S
北海道＋60s,関西＋60sの２つをそれぞれ定桁結合をするため
D15に　=REPLACE(REPLACE("　　　 ",1,LEN(A15),A15),4,LEN(C15),C15)
E15に　=REPLACE(REPLACE("　　　 ",1,LEN(B15),B15),4,LEN(C15),C15)
"　　　”部分だが北海道などが、最長３文字なら全角スペース３文字＋半角スペース適当数（100sの４桁が最長なら４スペース）結合したものを関数の第1引数に入れる。
本件ではストレートに文字列結合してもOKのような気がするが。
F15は　=MATCH($D$15,$C$1:$C$100,0)　は発地区の表上の行的位置
G15は　=MATCH($E$15,$C$1:$C$100,0)　は着地区の表上の行的位置
H1５は　=MATCH(G15,$E$1:$X$1,0)　着地区のコード（D列のコードを引いた）
X1のｘは実際には列の広がり具合で、適当に修正してください。
I15　ここで最終的に、表引きのINDEX関数を使える状態になって
=INDEX($E$1:$X$14,F15,MATCH(G15,$E$1:$X$1,0))
X14も実際にあわせて適当に変えてください。
ーーーー
結果は第１５行はA-I列は
北海道 関西 60S 北海道60S 関西　60S 2 8 4 3000
このように中間結果を可視化しておくのも（冗長ではあるが）いい点もあると思う。

お礼

そうですよね。
作業列を作るのがわかりやすいのですが、今回は使えなかったので・・・

今回はA No.1さんの回答を使わせていただきました。
今後の参考にさせていただきます。
本当にありがとうございました。

回答No.5

KURUMITO

回答Ｎｏ4です。
作業列を作らない場合には別のシートのＥ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTA(B2:D2)<>3,"",INDEX(Sheet1!D:F,SUMPRODUCT((Sheet1!A1:A1000=B2)*(Sheet1!B1:B1000=D2)*ROW(A1:A1000)),MATCH(C2,Sheet1!D$1:F$1,0)))

回答No.4

KURUMITO

表1がシート1に有るとして、発地の北海道はＡ3セル、６０ＳはＢ3セル、Ｃ3セルは1、Ｄ1セルには北海道、Ｅ1セルには関東、Ｆ1セルには関西などと入力されているとします。
北海道と60Ｓのある行を選んでから着地の関東などを選ぶことになりますので、作業列を作って対応することが最もわかりやすく処理速度も速いですね。
例えばＨ3セルには次の式を入力して下方にオートフィルドラッグします。

=A3&B3

表2は別のシートに作るとしてＡ1セルには氏名、Ｂ1セルには発地、Ｃ1セルには着地、Ｄ1セルにはサイズ、Ｅ1セルには料金の項目名があるとします。
Ｅ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTA(B2:D2)<>3,"",INDEX(Sheet1!$D:$F,MATCH(B2&D2,Sheet1!$H:$H,0),MATCH(C2,Sheet1!$D$1:$F$1,0)))

お礼

そうですよね。作業列が使えれば少しは分かりやすいと思うのですが、
作業列は使えなかったので・・・

今回はA No.1さんの回答を使わせていただきました。
今後の参考にさせていただきます。
本当にありがとうございました。

回答No.3

keithin

どこでもいいですが例えば
　B20に発地
　C20に着地
　D20にサイズ
がそれぞれあるのを検索してみるとして。

たとえばINDEX関数を使うなら，判りやすく分けて説明すると

縦の発地番号は60s,80s,100sの規則正しい繰り返しですから，
　縦番号　=MATCH(B20,A:A,0)+MATCH(D20,$B$3:$B$5,0)-3

横の着地番号は北海道関東関西の検索値ですから
　横番号　=MATCH(C20,$D$1:$F$1,0)

するとINDEX(範囲，縦，横）ですから
=IF(COUNTA(B20:D20)=3,INDEX($D$3:$F$11,MATCH(B20,A:A,0)+MATCH(D20,$B$3:$B$5,0)-3,MATCH(C20,$D$1:$F$1,0)),"")
など。

もしSUMPRODUCT関数を使ってみたいなら
=IF(COUNTA(B20:D20)=3,SUMPRODUCT(($A$3:$A$11=B20)*($B$3:$B$11=D20)*$D$3:$F$11*($D$1:$F$1=C20)),"")
などでも，この程度の数表なら問題なく計算できます。

あるいは60s,80s,100sの３行×横列を１組と考えてINDEXやVLOOKUP/HLOOKUPを使い，何番目の組を検索すればいいのかをOFFSET関数などで切り替えて与えるような方法もありますね。
=VLOOKUP(D20, OFFSET($B$3:$F$5,MATCH(B20,A:A,0)-3,0), MATCH(C20,$B$2:$F$2,0))

お礼

今回はA No.1さんの回答を使わせていただきました。
今後の参考にさせていただきます。
本当にありがとうございました。

回答No.2

tom04

こんばんは！
外していたらごめんなさい。
料金の列だけで良いのですかね？
もしそうであれば、↓の画像のA～C列は手入力もしくは他の入力方法になるという前提です。

↓の画像でSheet2のE2セルに
=IF(COUNTBLANK(A2:D2),"",INDEX(Sheet1!$D$3:$F$11,SUMPRODUCT((Sheet1!$A$3:$A$11=B2)*(Sheet1!$B$3:$B$11=D2)*ROW($A$1:$A$9)),MATCH(C2,Sheet1!$D$1:$F$1,0)))

という数式を入れ、オートフィルで下へコピーしています。

Sheet1の「発/着地番号」の意味が良く判らないので
的外れなら読み流してくださいね。m(__)m

お礼

今回はA No.1さんの回答を使わせていただきました。
今後の参考にさせていただきます。
本当にありがとうございました。