エクセルの関数（抽出）について

質問

1　　2　　　3　　　4　　5
A　　　あ　　　ko 　 ▲　■　
B　　　か　　　sa
C　　　もへ　　we
D　　　あ　　　ty
E　　　てろ　　　lii
F　　　ぎ　　　loer
G　　　か　　　ttg
H　　　あ　　　rer

上のような表があったときに、A4のセル（▲）に「あ」と入れると、
A5のセル（■）に『rer』と入るような関数（又はそれに代わる方法）を
ご教示ください。

やりたいこと：
２列目の中に、▲と完全一致する値（又は文字列。以下同じ。）が存在するとき、
同じ列の3列目の値を返したい。
ただし、２列目には完全一致する値が何度も登場する可能性があり、
２列目に▲と完全一致する値が複数存在する場合は、その内一番下の行
のものを選び、同じ行の３列目の値を■に返したい。

条件：
・可能であれば、２列目をキーにした並び替えなどは行いたくない。
・現状のデータは、３０００行程度のデータ量であるが、今後じょじょに増えていく。

以上です。
説明が明瞭でなく、イメージがお伝えできていないかも知れませんが、
どうかご教示頂きたく、なにとぞよろしくお願い致します。

回答No.1

mt2008 · ベストアンサー

行と列が何だか判りにくいですが、添付の図ではD4セルに↓の式を入れて居ます。
=IF(C1="","",OFFSET(B$1,SUMPRODUCT(MAX((A$1:A$3000=C1)*ROW(A$1:A$3000)))-1,0))

ただ、3000行もやると遅くなるかもしれません。

お礼

ものすごく早い回答、ありがとうございました。
大変参考になりました。

回答No.4

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

もう一案
=IF(D1="","",INDEX(C1:C8,LOOKUP(2^20,ROW(B1:B8)*(B1:B8=D1))))

回答No.3

KURUMITO

複雑な式を使うと計算が重くなります。分かり易く簡単な方法は作業列を作って対応することでしょう。
表がNo1さんのような表になっているとしてC1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",A1&COUNTIF(A$1:A1,A1))

D1セルから下方には求めたいA列にある文字列を入力します。例えばD1セルに「あ」と入力します。
E1セルには次の式を入力して下方にオートフィルドラッグすればよいでしょう。

=IF(D1="","",INDEX(B:B,MATCH(D1&COUNTIF(A:A,D1),C:C,0)))

お礼

ありがとうございました。
大変参考になりました。
ファイルの重さにまで配慮頂き、助かりました。

回答No.2

mu2011

一例です。
=IF(COUNTIF(B:B,D1),INDEX(C:C,MAX(INDEX((B:B=D1)*ROW(B:B),))),"")

お礼

ありがとうございました。
大変参考になりました。