If I understand what you want then maybe this:

D1 = x

=IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0), E1),LOOKUP(E1,E$21:F$30),"-")

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9ac5211fe38b0@uwe...
Dear T. Valko,
Based on the source database, your suggested function
=IF(COUNTIF(INDEX(B$2:C
$4,MATCH(D$1,A$2:A$4,0),0),E1),"OK","-") will show "OK" if it detects 12
within row 1 (the argument is "x") in the cell E2. Every things work very
perfect.

In order to make the analysis more intensive, I would like, based on the
data
(F1 to F10), I need a function to analyse the data based on another
database,
see below.

E21 = 11...... F21 = T
E22 = 12...... F22 = G
E23 = 13...... F23 = R
E24 = 14...... F24 = E
E25 = 15...... F25 = K
E26 = 16...... F26 = Q
E27 = 17...... F27 = L
E28 = 18...... F28 = C
E29 = 19...... F29 = Z
E30 = 20...... F30 = I

I tried to use function MODE plus IF to construct a formula, based on the
database above, to show "G" if the formula detect "OK" along "12", but
failed.
Can you advice me other option to do this!

Many thanks,
Wilchong







T. Valko wrote:
You're welcome. Thanks for the feedback!

Dear T.Valko,
The formular working very well.
[quoted text clipped - 27 lines]
Thanks for your advice,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200908/1