OK, I think I uderstand what you want.

I would put all the table data in a contiguous range like this:

...........E..........F..........G
1.......11.........T..........-
2.......12.........G........OK
3.......13.........R..........-
4.......14.........E..........-

Then:

Formula in H1:

=COUNTIF(G1:G10,"OK")

Then this array formula** in H2 and copied down:

=IF(ROWS(H$2:H2)H$1,"",INDEX(F$1:F$10,SMALL(IF(G$ 1:G$10="OK",ROW(F$1:F$10)),ROWS(H$2:H2))-ROW(F$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9b0fa83429dd4@uwe...
Dear T. Valko,
Thanks, your suggested function work very well. With your suggested
formula,
the "G" shown up in the of G2 when the formula detect "OK" in F2. OK, the
first step is ok already.

The second step is how to revise the formula in order to make the "G"
shown
up in the of G1 when the formula detect "OK" from F1 to F10. From my
previous experience you show me, I can add
"=IF(ROWS(G1:G$10)<=SUM(--(COUNTIF
(......." and then entered by "Shift + Control + Enter". I also dragged
the
function from G1 to G10. Of course, what I tried to do was failed. As a
result, I need your advice.

Many thanks for your time.
Wilchong




T. Valko wrote:
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),"-")

Dear T. Valko,
Thank, I know what is my problem. In order to make thing easy, I would
[quoted text clipped - 45 lines]
Thanks for your advice,
Wilchong

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