TO mr t.valko...

P

pierre

given :

A B
1 tree a 10
2 tree b 20
3 tree b 30
4 axe a 40
5 axe b 50
6 axe c 60

my search string cells are : c1 : c3

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<>"",C1:C3,"skip")&"*"))

in cell D2 i entered :
{=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$7;SMALL(IF(ISNUMBER(SEARCH(TRANSPOSE(IF(C$1:C$3<>"",C$1:C$3)),A$1:A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")}
and i dragged down...


my problem is : WHAT FORMULA SHOULD I ENTER in cell E2 IN ORDER TO GET THE
CORRESPONDING VALUES of "tree" and "axe" ???

i tried the following :

i enterd in E1 :SUMPRODUCT(COUNTIF(D2:D12,"*"))
and i entered in E1:
{=IF(ROWS(E$2:E2)<=E$1,INDEX(B$1:B$7,SMALL(IF(ISNUMBER(SEARCH(TRANSPOSE(IF($B$1:$B$7<>"",$B$1:$B$7)),B$1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E2))),"")}
and i dragged down

now if i enter the word "axe" in cell C1 i get the following WRONG
results

10 for axe a
20 for axe b
30 for axe c

HELP
 
P

pierre

i managed to reach to this formula :

{=INDEX(B$1:B$7;SMALL(IF($A$1:$A$7=TRANSPOSE(IF($D$2:D2<>"";$D$2:D2));ROW(B$1:B$7));ROWS(B$1:B1)))}

but my problem is now that if i enter "tree" in C1 i get duplicate numbers
like follows :

tree a 10 (good answer)
tree b 20 (good answer)
tree b 20 ----> wrong answer : IT SHOULD BE 30

ANY SUGGESTIONS ?????
 
T

T. Valko

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<>"",C1:C3,"skip")&"*"))

That formula needs to be array entered.

Enter this array formula in D2 and copy across to E2:

=IF(ROWS(D$2:D2)<=$D$1,INDEX(A$1:A$7,SMALL(IF(ISNUMBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<>"",$C$1:$C$3)),$A$1:$A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")

Select both D2 and E2 then copy down as needed.
 
P

pierre

yes it worked ....THANK YOU

i have another issue.....please:

A B C D
1 a 10 2 c 40
2 b 20 3 d 50
3 b 30 4 e 60

I would like to use index(…(match)) function in cell B10 and that in order
to be able to enter : a,b,…e,f in search string cell A10 ,So as to get
their corresponding results
how to do that ?? Or do you have other suggestions ??
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top