location
One last question :
regarding this formula :
{=IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMAL L(IF(ISNUMBER(MATCH(rng,D$5:D$6,0)),ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"") }
CAN WE WRITE IN ANOTHER WAY IN ORDER TO BE SHORTER ???
especially this part : ROW(rng)-MIN(ROW(rng))+1)
THANKS AGAIN
"T. Valko" wrote:
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
???
|