Excelで2つのキーと合致するデータを取得するには？

質問

Excelで以下の実現方法にどん詰まりしています…

１つのシート内にＡとＢの表があります。
＜表Ａ＞
コード1　コード2
100　　　8
100　　　2
200　　　3

＜表Ｂ＞
コード1　コード2　金額
100　　　1　　　　1000
100　　　2　　　　2000
100　　　5　　　　4000
100　　　8　　　　6000
150　　　1　　　　1500
200　　　3　　　　3200

表Ａのコード1、コード2と表Ｂのコード1、コード2が合致する
表Ｂの金額を取得したいのですが、2つのキーに合致させるという方法が
イマイチ実現できません。一度にできるものなのでしょうか？

MATCHでコード1の存在の有無と行番号を取得し、取得した行番号から
コード2をMATCHして…でも、コード2を検索するには範囲がわからないとダメだよな…
…などなど、ぐるぐる中です…

よろしくお願いします…

※コード1、2ともにソートされています。

回答No.1

noname#52504 · ベストアンサー

例えば、
「コード1が100で、コード2が2の場合　⇒　2000を返す」
ということですよね。

コード1とコード2を繋いだ値について検索するのが手っ取り早いと思います。

例えば、＜表Ａ＞がA,B列，＜表Ｂ＞がE,F,G列にあるとして、
A列とB列の値を結合した値(ex.100_8)を、
E列とF列の値を結合した配列(ex.{100_1,100_2,100_5,…,200_3}から探して、
ヒットした行のG列の値を返す。

イ案　=INDEX($G$1:$G$99,MATCH(A1&"_"&B1,INDEX($E$1:$E$99&"_"&$F$1:$F$99,),0))

Excel2003で動作確認済。

----------------------------------------------------------------------------
蛇足

この手の数式はいろんな書き方ができます。
基本的には趣味や慣れの問題で、どれが良いということは特にありませんが、
一応ご参考まで。

ロ案　=INDEX($G$1:$G$99,INDEX(MATCH(A1&"_"&B1,$E$1:$E$99&"_"&$F$1:$F$99,0),))
　※配列処理をさせるためのダミー関数INDEX(～,)はMATCHの外でもいい。

ハ案　=INDEX($G$1:$G$99,SUMPRODUCT(MATCH(A1&"_"&B1,$E$1:$E$99&"_"&$F$1:$F$99,0)))
　※INDEXよりSUMPRODUCTの方が好きという人も多いみたい。

二案　=INDEX($G$1:$G$99,MATCH(A1&"_"&B1,$E$1:$E$99&"_"&$F$1:$F$99,0))
　※Ctrl+Shift+Enterで配列数式として入力する場合はダミーの関数はいらない

ホ案　=OFFSET($G$1,SUMPRODUCT(MATCH(1,(A1=$E$1:$E$99)*(B1=$F$1:$F$99),0))-1,0)
　※別案：A列とE列,B列とF列を直接比較して論理演算を行う
　※INDEXよりOFFSETの方が好きという人も結構いるみたい。

などなど。

お礼

お礼、遅れてすみません！
VBAに逃げようかと思っていたところです。
イ案でうまく行きました。

上の例の書き方が悪かったのですが、
「表Ａ」のコード１とコード２を結合して表Ｂを参照しようにも
実際の「表Ｂ」のコード１とコード２の間に１列挟まっていて
VLOOKUPを使ったやり方でうまく参照できませんでした。

普段、Excelの計算式は"SUM"など、ごく標準的な計算しか使わないもので…
ありがとうございました(´∀｀)