Dear T. Valko,
Thanks, your suggested function work very well, it is exactly fit to what I
am think!

I have found out that your suggested function, IF(COUNTIF(INDEX(B$2:C$4,MATCH
(D$1,A$2:A$4,0),0),$E1),LOOKUP($E1,E$21:F$30),"-"), depends on two arguments,
one is the D1, another cell is E1.

Right now, I want to do something little bit complicated.

I want to make your suggested function with additional function, which is
only show the data from F21 to F30 from lowest value to greatest value (Based
on the value from the cell, E21 to E30). This function has to be done without
the argument from the cell, E1.

In order to achieve the objective, I tried to add "IF(ROWS(H21:H$21)<=SUM(--
(COUNTIF(A$2:A$4,$E$21:$F$30)=0)) in front of your suggested function. I also
add " ROWS(H$21:H21))),"") " at the back of your suggested formula.

The new function is located in the cell, H21, then entered by "Shift +
Control + Enter". I also dragged the function from H21 to H23.

If D1 = y, my desire outcome is to see "E" show in the cell H21, "C" show in
the cell H22 and "-" show in the cell H23.

Of course, up to this point, the new function doesn't work!

I am really doubt my revised function can be improved based on my requirement,
I need your advice.

Many thanks for your time.
Wilchong







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

Dear T. Valko,
Based on the source database, your suggested function
[quoted text clipped - 35 lines]
Thanks for your advice,
Wilchong

--
Message posted via http://www.officekb.com