given :

A B
1 tree a 10
2 tree b 20
3 tree b 30

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(ISNU MBER(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...

now if enter in cell C1 the word "tree" i get what follows :
in D2= tree a
in D3 = tree b
in D4 = tree b

NOW WHAT FORMULA SHOULD I ENTER IN E1 IN ORDER TO GET THEIR CORRESPONDING
VALUES i.e:

10 for tree a
20 for tree b
30 for tree b

thanks

IF(ROWS(D$2:D4)<=D$1,INDEX(B$1:B$7,SMALL(IF(ISNUMB ER(SEARCH(TRANSPOSE(IF($B$1:$B$3<"",$B$1:$B$3)),B $1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E4))),"")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"pierre" wrote in message
...
given :

A B
1 tree a 10
2 tree b 20
3 tree b 30

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(ISNU MBER(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...

now if enter in cell C1 the word "tree" i get what follows :
in D2= tree a
in D3 = tree b
in D4 = tree b

NOW WHAT FORMULA SHOULD I ENTER IN E1 IN ORDER TO GET THEIR CORRESPONDING
VALUES i.e:

10 for tree a
20 for tree b
30 for tree b

thanks

there is something wrong...i got only 30 for tree a , what about the others ??
normally i should get :

D E
2 tree a 10
3 tree b 20
4 tree c 30

HELP


"Bob Phillips" wrote:

IF(ROWS(D$2:D4)<=D$1,INDEX(B$1:B$7,SMALL(IF(ISNUMB ER(SEARCH(TRANSPOSE(IF($B$1:$B$3<"",$B$1:$B$3)),B $1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E4))),"")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"pierre" wrote in message
...
given :

A B
1 tree a 10
2 tree b 20
3 tree b 30

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(ISNU MBER(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...

now if enter in cell C1 the word "tree" i get what follows :
in D2= tree a
in D3 = tree b
in D4 = tree b

NOW WHAT FORMULA SHOULD I ENTER IN E1 IN ORDER TO GET THEIR CORRESPONDING
VALUES i.e:

10 for tree a
20 for tree b
30 for tree b

thanks

It is an array formula also.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"pierre" wrote in message
...
there is something wrong...i got only 30 for tree a , what about the
others ??
normally i should get :

D E
2 tree a 10
3 tree b 20
4 tree c 30

HELP


"Bob Phillips" wrote:

IF(ROWS(D$2:D4)<=D$1,INDEX(B$1:B$7,SMALL(IF(ISNUMB ER(SEARCH(TRANSPOSE(IF($B$1:$B$3<"",$B$1:$B$3)),B $1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E4))),"")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"pierre" wrote in message
...
given :

A B
1 tree a 10
2 tree b 20
3 tree b 30

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(ISNU MBER(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...

now if enter in cell C1 the word "tree" i get what follows :
in D2= tree a
in D3 = tree b
in D4 = tree b

NOW WHAT FORMULA SHOULD I ENTER IN E1 IN ORDER TO GET THEIR
CORRESPONDING
VALUES i.e:

10 for tree a
20 for tree b
30 for tree b

thanks