hi ...plz help

given :

C
5 tea
6 tree
7 oil

my search string is D5.... also i put my formula in E5 :
=MATCH(D5;$C$5:$C$7;0)
this mean that if i type the word "tree" in cell D5...the result will be =2
which means the second criteria
NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF
THE WORD "TREE".....PLZ HELP


PS: IF THE GIVEN WOULD BE :

C
5 tea
6 tree
7 tree

HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ???

I assume by location you mean the cell address:

="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0))

As for the precedence, the formula will *always* find the first instance. Do
you want *all* the locations listed?

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
hi ...plz help

given :

C
5 tea
6 tree
7 oil

my search string is D5.... also i put my formula in E5 :
=MATCH(D5;$C$5:$C$7;0)
this mean that if i type the word "tree" in cell D5...the result will be
=2
which means the second criteria
NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF
THE WORD "TREE".....PLZ HELP


PS: IF THE GIVEN WOULD BE :

C
5 tea
6 tree
7 tree

HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ???

yes please i want *all* the locations listed...

One more little thing...suppose that i have changed the given and i moved
its location... i.e : from colomn C to column A (see below) :

C A
5 tea 5 tea
6 tree 6 tree
7 oil 7 oil

i have noticed that the the formula :
="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) is giving me C1
although it should be A1....do you have any tips for that ??


THANK YOU








"T. Valko" wrote:

I assume by location you mean the cell address:

="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0))

As for the precedence, the formula will *always* find the first instance. Do
you want *all* the locations listed?

--

hi ...plz help

given :

C
5 tea
6 tree
7 oil

my search string is D5.... also i put my formula in E5 :
=MATCH(D5;$C$5:$C$7;0)
this mean that if i type the word "tree" in cell D5...the result will be
=2
which means the second criteria
NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF
THE WORD "TREE".....PLZ HELP


PS: IF THE GIVEN WOULD BE :

C
5 tea
6 tree
7 tree

HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ???

Ok, if you're going to move things around the only way to get the formula to
work correctly is to give the range a defined name.

C5:C7 = defined named range = rng

D5 = lookup value

Enter this formula in E4:

=COUNTIF(rng,D5)

Enter this array formula** in E5:

=IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(rng=D$5,ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...

yes please i want *all* the locations listed...

One more little thing...suppose that i have changed the given and i moved
its location... i.e : from colomn C to column A (see below) :

C A
5 tea 5 tea
6 tree 6 tree
7 oil 7 oil

i have noticed that the the formula :
="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) is giving me C1
although it should be A1....do you have any tips for that ??


THANK YOU








"T. Valko" wrote:

I assume by location you mean the cell address:

="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0))

As for the precedence, the formula will *always* find the first instance.
Do
you want *all* the locations listed?

--

hi ...plz help

given :

C
5 tea
6 tree
7 oil

my search string is D5.... also i put my formula in E5 :
=MATCH(D5;$C$5:$C$7;0)
this mean that if i type the word "tree" in cell D5...the result will
be
=2
which means the second criteria
NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF
THE WORD "TREE".....PLZ HELP


PS: IF THE GIVEN WOULD BE :

C
5 tea
6 tree
7 tree

HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE
???

u are a real master.......
but please i need to know what follows :

in this formula that you gave me :
{=IF(ROWS(E$5:E5)<=E$4;ADDRESS(INDEX(ROW(rng);SMAL L(IF(rng=D$5;ROW(rng)-MIN(ROW(rng))+1);ROWS(E$5:E5)));COLUMN(rng);4);"") }

first question :
can we substitute : -MIN(ROW(rng))+1)
to the following : ROWS(rng) ??......AND HOW THE FORMULA WOULD BE
THEN ?? please show it to me i cant do it

second question :
if i would like to make the lookup value : D5 and D6....i think that the
formula will include TRANSPOSE like you taught me ....but how the formula
will look like then ??
please show it to me

third question:
what does the "4" stands for ?

THANKS FOR YOUR HELP REALLY
APPRECIATED





"T. Valko" wrote:

Ok, if you're going to move things around the only way to get the formula to
work correctly is to give the range a defined name.

C5:C7 = defined named range = rng

D5 = lookup value

Enter this formula in E4:

=COUNTIF(rng,D5)

Enter this array formula** in E5:

=IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(rng=D$5,ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...

yes please i want *all* the locations listed...

One more little thing...suppose that i have changed the given and i moved
its location... i.e : from colomn C to column A (see below) :

C A
5 tea 5 tea
6 tree 6 tree
7 oil 7 oil

i have noticed that the the formula :
="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) is giving me C1
although it should be A1....do you have any tips for that ??


THANK YOU








"T. Valko" wrote:

I assume by location you mean the cell address:

="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0))

As for the precedence, the formula will *always* find the first instance.
Do
you want *all* the locations listed?

--

hi ...plz help

given :

C
5 tea
6 tree
7 oil

my search string is D5.... also i put my formula in E5 :
=MATCH(D5;$C$5:$C$7;0)
this mean that if i type the word "tree" in cell D5...the result will
be
=2
which means the second criteria
NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF
THE WORD "TREE".....PLZ HELP


PS: IF THE GIVEN WOULD BE :

C
5 tea
6 tree
7 tree

HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE
???

can we substitute : -MIN(ROW(rng))+1)
to the following : ROWS(rng) ??......

No, that won't work.

second question :

Change the formula in E4 to:

=SUMPRODUCT(COUNTIF(rng,D5:D6))

Change the array formula** in D5 to:

=IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(ISNUMBER(MATCH(rng,D$5:D$6,0)),ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"")

third question:
what does the "4" stands for ?

That's the argument that determines what reference style the result will be
in. 4 means relative so the result is returned without $ signs. It's easier
to read without the $ signs.


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
u are a real master.......
but please i need to know what follows :

in this formula that you gave me :
{=IF(ROWS(E$5:E5)<=E$4;ADDRESS(INDEX(ROW(rng);SMAL L(IF(rng=D$5;ROW(rng)-MIN(ROW(rng))+1);ROWS(E$5:E5)));COLUMN(rng);4);"") }

first question :
can we substitute : -MIN(ROW(rng))+1)
to the following : ROWS(rng) ??......AND HOW THE FORMULA WOULD BE
THEN ?? please show it to me i cant do it

second question :
if i would like to make the lookup value : D5 and D6....i think that the
formula will include TRANSPOSE like you taught me ....but how the formula
will look like then ??
please show it to me

third question:
what does the "4" stands for ?

THANKS FOR YOUR HELP REALLY
APPRECIATED





"T. Valko" wrote:

Ok, if you're going to move things around the only way to get the formula
to
work correctly is to give the range a defined name.

C5:C7 = defined named range = rng

D5 = lookup value

Enter this formula in E4:

=COUNTIF(rng,D5)

Enter this array formula** in E5:

=IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(rng=D$5,ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...

yes please i want *all* the locations listed...

One more little thing...suppose that i have changed the given and i
moved
its location... i.e : from colomn C to column A (see below) :

C A
5 tea 5 tea
6 tree 6 tree
7 oil 7 oil

i have noticed that the the formula :
="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) is giving me C1
although it should be A1....do you have any tips for that ??


THANK YOU








"T. Valko" wrote:

I assume by location you mean the cell address:

="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0))

As for the precedence, the formula will *always* find the first
instance.
Do
you want *all* the locations listed?

--

hi ...plz help

given :

C
5 tea
6 tree
7 oil

my search string is D5.... also i put my formula in E5 :
=MATCH(D5;$C$5:$C$7;0)
this mean that if i type the word "tree" in cell D5...the result
will
be
=2
which means the second criteria
NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF
THE WORD "TREE".....PLZ HELP


PS: IF THE GIVEN WOULD BE :

C
5 tea
6 tree
7 tree

HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO
USE
???