Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / May 2008

Tip: Looking for answers? Try searching our database.

location

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
pierre - 23 May 2008 21:15 GMT
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 ???
T. Valko - 23 May 2008 21:52 GMT
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?

Signature

Biff
Microsoft Excel MVP

> hi ...plz help
>
[quoted text clipped - 21 lines]
>
> HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ???
pierre - 24 May 2008 13:44 GMT
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

> 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
> >
[quoted text clipped - 21 lines]
> >
> > HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ???
T. Valko - 24 May 2008 19:09 GMT
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)

Signature

Biff
Microsoft Excel MVP

> yes please i want *all* the locations listed...
>
[quoted text clipped - 47 lines]
>> > HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE
>> > ???
pierre - 24 May 2008 19:51 GMT
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);SMALL(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

> 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.
[quoted text clipped - 67 lines]
> >> > HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE
> >> > ???
T. Valko - 25 May 2008 03:05 GMT
>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.

Signature

Biff
Microsoft Excel MVP

>u  are a real master.......
> but please i need to know what follows :
[quoted text clipped - 95 lines]
>> >> > USE
>> >> > ???
pierre - 25 May 2008 13:02 GMT
One last question :

regarding this formula
{=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),"")}

CAN WE WRITE IN ANOTHER WAY IN ORDER TO BE SHORTER ???
especially this part : ROW(rng)-MIN(ROW(rng))+1)

                                      THANKS AGAIN

> >can we substitute :  -MIN(ROW(rng))+1)
> >to the following : ROWS(rng) ??......
[quoted text clipped - 117 lines]
> >> >> > USE
> >> >> > ???
T. Valko - 25 May 2008 17:59 GMT
You seem to be overly concerned with:

ROW(rng)-MIN(ROW(rng))+1)

You could put this portion in a separate cell and then refer to that cell:

=MIN(ROW(rng))+1

You need to understand what this is doing.

When you index a range the values of that range are in specific positions.
The positions are numbered 1 through the total number of cells in the range.
Your range is C5:C7. So:

C5 = position 1
C6 = position 2
C7 = position 3

In order to get the correct result from the formula we need to tell the
INDEX function we want the value located at position n. We do that using
ROW(rng)-MIN(ROW(rng))+1).

We have to convert the actual row numbers of "rng" to correspond to the
position numbers of the indexed range.

The actual row numbers of "rng" are 5,6,7. We need to convert those to
1,2,3. Here's how we do that:

ROW(rng)-MIN(ROW(rng))+1)

ROW(C5)-MIN(ROW(C5))+1 = 1
ROW(C6)-MIN(ROW(C5))+1 = 2
ROW(C7)-MIN(ROW(C5))+1 = 3

The *only* time you could replace ROW(rng)-MIN(ROW(rng))+1) with just
ROW(rng) is *if* the actual indexed range started in row 1. For example, if
the actual indexed range was A1:A3. In this case the actual row numbers
naturally correspond to the position numbers on the indexed range.

A1 = row 1 = position 1
A2 = row 2 = position 2
A3 = row 3 = position 3

However, if the actual range was A1:A3 and you used just ROW(rng) and
inserted a new row 1 the formula could return incorrect results because now
the row numbers do not correspond to the position numbers of the indexed
range.

Using ROW(rng)-MIN(ROW(rng))+1) accounts for this. This is the most
fool-proof method to convert the actual row numbers to the correct position
numbers.

exp101
Signature

Biff
Microsoft Excel MVP

> One last question :
>
[quoted text clipped - 134 lines]
>> >> >> > USE
>> >> >> > ???
pierre - 25 May 2008 18:09 GMT
THANKS for your time , your patience , and for these clear and important
answers
THANK YOU SIR.......
T. Valko - 25 May 2008 19:32 GMT
You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> THANKS for your time , your patience , and for these clear and important
> answers
> THANK YOU SIR.......
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.