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

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9adc3708900ee@uwe...
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