エクセルで同順位の値を表示する方法

質問

windowsXP　Excel2003です

sheet1に
　　A　　　B　　　C　　　　D　　　E
1　　　　10代　　20代　　30代　　40代　…70代以上
2　1位　みかん　なし　　りんご　もも
3　2位　りんご　もも　　もも　　みかん
4　3位　なし　　りんご　みかん　りんご
5　4位　もも　　みかん　なし　　なし

上記のように年代ごとに30位まで表示させたいと
思っています。

元になるsheet2は

　　　A　　　B　　　C　　D　　　E　　　　　　　　I
　　　　　　10代　20代　30代　40代　…70代以上
1　みかん 150　　50　　80 　180　　　　　　　　みかん
2　りんご 100　　70　　170　　100　　　　　　　　りんご
3　なし　　　50　　200　　30　　20　　　　　　　　なし
4　もも　　　50　　130　　170　250　　　　　　　　もも

以上のように年代別の列が10代から70代以上まであり、
A列の果物の種類は500行ほどあります。
(すべて違うものです)

=VLOOKUP(LARGE($B$9:$B$12,1),$B$9:$I$12,8,0)
を使ってそれぞれ、1位、2位、3位…の果物の名前を
表示させたのですが、上記の式だと、下記のように
表示されてしまいます。

　　　10代　　20代　　30代　　40代　…70代以上
1位　みかん　なし　　りんご　もも
2位　りんご　もも　　りんご　みかん
3位　なし　　りんご　みかん　りんご
4位　なし　　みかん　なし　　なし

この10代の3位と4位、30代の1位と2位のように同じ順位になるもので、
2番目にくる果物を正しく表示させたいのですが、どのような数式を
使ったらいいのか教えてください。
果物種類は500ほどあり、順位が同じになるものは5種類くらいに
なることがあります。
10代から70代以上のこの表が3つあるので、関数でどうにかしたいと
思っています。

初めての質問なので分かりにくいところもあるかと思いますが、
よろしくお願いいたします。

回答No.2

noname#204879

[回答番号：No.1この回答への補足]へのコメント、

》 sheet２の式に組み込みたいと思う…

理解されていないようですね。Sheet2 には式は一切ありません。
Sheet3 の式を Sheet1 の式に組み込むのは不可能と思いますが、例え可能であっても初心者にはお勧めしません。

お礼

sheet1とsheet2を間違えてしまいました。
すみません。
色々と質問させていただいた後も、試行錯誤を重ねた結果、

=INDEX(Sheet2!$A$2:$A$9,INDEX(MATCH(LARGE(Sheet2!B$2:B$9-ROW(Sheet2!B$2:B$9)/100,1),Sheet2!B$2:B$9-ROW(Sheet2!B$2:B$9)/100,0),))

の式で解決いたしました。

ご回答、本当にありがとうございました。

回答No.1

noname#204879

Sheet3 ← 作業シート
　　 A　　　　B　　　　C　　　　D　　　　E
1　　　　　10代　　 20代　　 30代　　 40代
2　みかん　150.002　 50.002　 80.002　180.002
3　りんご　100.003　 70.003　170.003　100.003
4　なし　　 50.004　200.004　 30.004　 20.004
5　もも　　 50.005　130.005　170.005　250.005

Sheet3!B2: =Sheet2!B2+ROW()/1000

Sheet1
　　A　　 B　　　 C　　　 D　　　 E
1　　　 10代　　20代　　30代　　40代
2　1位　みかん　なし　　もも　　もも
3　2位　りんご　もも　　りんご　みかん
4　3位　もも　　りんご　みかん　りんご
5　4位　なし　　みかん　なし　　なし

Sheet1!B2: =INDEX(Sheet3!$A$2:$A$5,MATCH(LARGE(Sheet3!B$2:B$5,$A2),Sheet3!B$2:B$5,0))

なお、Sheet1 の範囲 A2:A5 は、“ユーザー定義”で #"位" に書式設定して、上から 1、2、…、4 と入力しています。

補足

ご回答ありがとうございます。
量的に作業シートを使いたくないので、sheet3に入れていただいた式を
sheet２の式に組み込みたいと思うのですが、それは不可能なことでしょうか？