location

[pierre]

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]

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?

 

[pierre]

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]

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)

 

[pierre]

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

 

[T. Valko]

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,D56))

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$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.

 

[pierre]

One last question :

regarding this formula
{=IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL(IF(ISNUMBER(MATCH(rng,D$5$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]

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

 

[pierre]

THANKS for your time , your patience , and for these clear and important
answers
THANK YOU SIR.......

 

[T. Valko]

You're welcome. Thanks for the feedback!