TO mr t.valko...

[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$22)<=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$22))),"")}
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(D212,"*"))
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

 

[pierre]

i managed to reach to this formula :

{=INDEX(B$1:B$7;SMALL(IF($A$1:$A$7=TRANSPOSE(IF($D$22<>"";$D$22));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. 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$22)<=$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$22))),"")

Select both D2 and E2 then copy down as needed.

 

[pierre]

i get empty cells........there is something wrong

 

[T. Valko]

The formula does work.

 

[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 ??

 

[T. Valko]

Well, you've stumped me on that one! There's no way to do that using
formulas.

 

[pierre]

okay ....THANKS anyway

Well, you've stumped me on that one! There's no way to do that using
formulas.