how,,
 

PLEASE guys
i have 2 questions

first issue)

given :

A
1 Z
2 ZZ
3 ZZ

i am entering the following formula : =COUNTIF($A$1:$A$3;"*"&C1&"*")
i mean that if i should type the "z" letter in search string C1 i should
get =1
but this formula is giving me =3 .what to do ?


2nd issue)

given :

A
1 pen a
2 pen b
3 pen c
4 table a
5 table B

my goal here is to type in search string B1 the world "pen" in order to get
all results related to " pen " like (pen a , pen b , pen c)
so i am doing the following :

in C1 : =COUNTIF(A1:A5;"*"&C1&"*")

in D1 :
{=IF(ROWS(D$2:D2)<=D$1;INDEX(A$1:A$5;SMALL(IF(ISNU MBER(SEARCH(C1;A$1:A$5));ROW(A$1:A$5));ROWS(D$2:D2 )));"")}....AND I DRAG DOWN

NOW MY QUESTION HERE :

WHAT SHOULD I DO IN ORDER TO TYPE THE WORD "PEN" IN SEARCH STRING CELL C1
AND THE WORD "TABLE" IN SEARCH STRING CELL C2 IN ORDER TO GET ALL THEIR
CORESPONDENT RESULTS ????

PLEASE HELP

how,,
 

For your 1st question, the 2nd argument should be simply C1, and not *& C1 &*
I think.

"pierre" wrote:

PLEASE guys
i have 2 questions

first issue)

given :

A
1 Z
2 ZZ
3 ZZ

i am entering the following formula : =COUNTIF($A$1:$A$3;"*"&C1&"*")
i mean that if i should type the "z" letter in search string C1 i should
get =1
but this formula is giving me =3 .what to do ?


2nd issue)

given :

A
1 pen a
2 pen b
3 pen c
4 table a
5 table B

my goal here is to type in search string B1 the world "pen" in order to get
all results related to " pen " like (pen a , pen b , pen c)
so i am doing the following :

in C1 : =COUNTIF(A1:A5;"*"&C1&"*")

in D1 :
{=IF(ROWS(D$2:D2)<=D$1;INDEX(A$1:A$5;SMALL(IF(ISNU MBER(SEARCH(C1;A$1:A$5));ROW(A$1:A$5));ROWS(D$2:D2 )));"")}....AND I DRAG DOWN

NOW MY QUESTION HERE :

WHAT SHOULD I DO IN ORDER TO TYPE THE WORD "PEN" IN SEARCH STRING CELL C1
AND THE WORD "TABLE" IN SEARCH STRING CELL C2 IN ORDER TO GET ALL THEIR
CORESPONDENT RESULTS ????

PLEASE HELP

how,,
 

1. =COUNTIF($A$1:$A$3;C1)
2. =COUNTIF(A1:A5;"*"&C1&"*")+=COUNTIF(A1:A5;"*"&C2&" *")

Regards
Fred

"pierre" wrote in message
...
PLEASE guys
i have 2 questions

first issue)

given :

A
1 Z
2 ZZ
3 ZZ

i am entering the following formula : =COUNTIF($A$1:$A$3;"*"&C1&"*")
i mean that if i should type the "z" letter in search string C1 i should
get =1
but this formula is giving me =3 .what to do ?


2nd issue)

given :

A
1 pen a
2 pen b
3 pen c
4 table a
5 table B

my goal here is to type in search string B1 the world "pen" in order to
get
all results related to " pen " like (pen a , pen b , pen c)
so i am doing the following :

in C1 : =COUNTIF(A1:A5;"*"&C1&"*")

in D1 :
{=IF(ROWS(D$2:D2)<=D$1;INDEX(A$1:A$5;SMALL(IF(ISNU MBER(SEARCH(C1;A$1:A$5));ROW(A$1:A$5));ROWS(D$2:D2 )));"")}....AND
I DRAG DOWN

NOW MY QUESTION HERE :

WHAT SHOULD I DO IN ORDER TO TYPE THE WORD "PEN" IN SEARCH STRING CELL
C1
AND THE WORD "TABLE" IN SEARCH STRING CELL C2 IN ORDER TO GET ALL THEIR
CORESPONDENT RESULTS ????

PLEASE HELP