The original formula in F1:F10 returns either a dash "-" or "OK".

Are you saying that you want another formula that checks F1:F10 for "OK" and
if found do a lookup on the entries in E1:E10?

If that's what yo want try this:

=IF(F1="OK",LOOKUP(E1,E$21:F$30),"-")

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9ae88b0fb2b41@uwe...
Dear T. Valko,
Thank, I know what is my problem. In order to make thing easy, I would
like
to break the whole thing into stages and explain step by step.

Just before I talk about the complicate function, first of all, I would
like
to ask you one minor question regarding your previous function. Your
previous suggested function as follow:
=IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0), $E1),LOOKUP($E1,E$21:F$30),
"-")
I placed this formula in the cell of G1 and drag it from G1 to G10.
Because
the formula detects E2, so "G" will show in the cell of G2. "G" is come
the
database as follow:

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

What I want to do the first thing is to revise the above formula slightly.
The above formula will achieve the result based on the variable from E1 to
E10, NOT from F1 to F10 as show you just now. So my first question is
that
do you think the argument of above function can be changed to F1 rather
than
E1?

Many thanks for your time,
Wilchong




T. Valko wrote:
I'm not following you on this. Post some sample data and explain what
result
you expect.

Dear T. Valko,
Thanks, your suggested function work very well, it is exactly fit to
what
[quoted text clipped - 49 lines]
Thanks for your advice,
Wilchong

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