こんな場合の配列表から答えを導く関数式とは？

質問

下記のような配列で球団名、選手名、ポジションが書かれているとします。（現役と元選手が混在、今の選手を知らないので）
　　Ａ　　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　　Ｆ　　　　
１球団　　巨人　　　阪神　　横浜　　中日　　広島
２監督　　原　　　　岡田　　大矢　　落合　　古葉
３投手　　上原　　　藤川　　三浦　　川上　　池谷
４捕手　　阿倍　　　矢野　　相川　　谷繁　　達川
５１塁　　王　　　　バース　田代　　谷沢　　衣笠
６２塁　　土井　　　真弓　　山下　　高木　　東出
７３塁　　長嶋　　　掛布　　松原　　井端　　新井
↓　↓

　上記の配列で、重複はないとした場合で、上記配列表の行、列を入替えたり、全く、いじることなく
　　データーベースが、チーム、選手名と並んでいて、この２項目から上記参照表のポジション名を取得したいのです。

　　　　Ａ　　　Ｂ　　　　　Ｃ
１　　巨人　原　　　　は監督と表示したい
２　　巨人　上原　　　は投手と表示したい
３　　巨人　阿倍　　　は捕手と表示したい
４　　巨人　王　　　　は１塁と表示したい
５　　巨人　土井　　　は２塁と表示したい
６　　巨人　長嶋　　　は３塁と表示したい

ようにするにはどのような関数式を立てればよいでしょうか?これがどうも分からず、いつも配列表の行列を入替えて、ポジションＡ列をＢ～Ｅ各列に挿入して、都度、VLOOKUP関数で列を取得するようなやり方をしています。（分からないので）
　多分、INDEX,OFFSET,MATCHなどの関数を使うのかな？と思っているのですが、上手く、式を立てられません。
　上記パターンの場合の簡単な式を教えていただけないでしょうか?

回答No.3

grumpy_the_dwarf · ベストアンサー

元の表全体を「表」、一行目を「球団」、A列を「ポジション」と定義します。

C1: =INDEX(ポジション,MATCH(B1,INDEX(表,,MATCH(A1,球団,0)),0))

お礼

回答、有難うございます。使い方で色んなパターンがあるのは知っているのですが、式を解読できても、組み立てることが中々、出来ません。
　先に、色んな回答を戴いておりますが、こちらの数式も活用させていただきたいと思います。有難うございました。

回答No.4

imogasi

式が少し複雑になるので、VBAでユーザー関数を定義してみました。
VBEの標準モジュールの画面に
Function aa(a, b)
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
z = Application.WorksheetFunction.Match(a, sh1.Range("a1:Z1"), 0)
u = Application.WorksheetFunction.Match(b, Range(sh1.Cells(1, z), sh1.Cells(100, z)), 0)
aa = sh1.Cells(u, 1)
End Function
やっていることは既回答の関数式を分解してVBAに直したものです。
自分にあわせてコードを修正できるなら、使ってみる手もある。
ーー
例データとして、質問のデータだSheet1にあり
Sheet2のA1:B2に
巨人 原
阪神 真弓
Sheet2のC1に
=aa(A1,B1)
と入れて下方向に式を複写する。
結果　C1:C2
監督
２塁

お礼

imogasiさん、ご無沙汰しています。いつもながら、明解な回答有難うございます。
　解説いただいたコードで再現できるようにトライしてみます。課題ができると、こちらの掲示板を利用しますが、今までどれほど、助かったか分かりません。質問する側でなく、imogasiさんのように、回答できる側の人間でありたいと思います。有難うございました。

回答No.2

suekun

Ａ　　　Ｂ　　　　　Ｃ
11　　巨人　原　　　　は監督と表示したい
12　　巨人　上原　　　は投手と表示したい
13　　巨人　阿倍　　　は捕手と表示したい
14　　巨人　王　　　　は１塁と表示したい
15　　巨人　土井　　　は２塁と表示したい
16　　巨人　長嶋　　　は３塁と表示したい

同じアドレスではわかり難いので行は変えました。（元表はそのまま）
以下の数式をＣ11に入力して、そのまま下にフィルコピーして下さい

ちなみに、エクセルが今使えない環境なので、
検証してません。＾＾；

不具合があれば再度その内容を書いて下さい。

=OFFSET($A$1,MATCH($B11,OFFSET($A$1,1,MATCH($A11,$B$1:$G$1,0),10,1)),0)

お礼

有難うございます。いつも簡単なVLOOKUP関数でやろうとするので、中々、複雑な関数はためらいがちです。参考にして、課題を克服したいと思います。有難うございました。

回答No.1

zap35

＞INDEX,OFFSET,MATCHなどの関数を使うのかな？
いい線行ってます。

データベースがSheet1にあるとします。別シートのA1に「巨人」、B1に「原」と入力したとき、C1セルの式は以下になります
=INDEX(Sheet1!A:A,MATCH(B1,OFFSET(Sheet1!$A$1,0,MATCH(A1,Sheet1!$A$1:$F$1,0)-1,20,1),0))

式中後半に出てくる数値の　20　はデータベースの最大行数です。実際のシートに合わせて変更してください

なお「は監督と表示したい」は「監督」だけで良いものと解釈しましたが、別の文字を連結したければ
　="は"&INDEX(Sheet1!A:A,MATCH(B1,OFFSET(Sheet1!$A$1,0,MATCH(A1,Sheet1!$A$1:$F$1,0)-1,20,1),0))&"と表示したい"
にします

お礼

有難うございます。配列表は会社の仕事に関係するものをプロ野球に置換しています。
　私は、特にoffset関数を苦手にしているもので、助かります。早速明日、会社で試してみます。