You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
THANK YOU SO MUCH....now i have learned something new, thanks to you
...and i
added it to my EXCEL arsenal :)




indexed array a

A1 = position 1
A2 = position 2
A3 = position 3
A4 = position 4
...
A7 = position 7

The important thing to understand about these positions is that they're
relative to the referenced range. If the indexed range was G6:G12 the
relative positions would still be the same:

G6 = position 1
G7 = position 2
G8 = position 3
G9 = position 4
...
G12 = position 7

In the above formula the logical test of the IF function returns an array
of
1s and 0s. The 1s are evaluated as TRUE and the 0s are evaluated as
FALSE.
Where the logical test evaluates to TRUE (1s), the corresponding ROW
number
is passed to the SMALL function. We use the row number to tell the INDEX
function which position of the indexed array to return as our result.

Since the relative positions start at 1 and go to 7 (in this example) we
have to make sure that the row numbers being passed to INDEX are the same
as
the position numbers. We do that using this expression:

ROW(A$1:A$7)-MIN(ROW(A$1:A$7))+1

In this example the row numbers already correspond to the position
numbers
by virtue of the fact that our referenced range is A1:A7 and we use
ROW(A1:A7). So:

A1 = position 1 = ROW(A1)
A2 = position 2 = ROW(A2)
A3 = position 3 = ROW(A3)
...
A7 = position 7 = ROW(A7)

But, if the referenced range was G6:G12 and we used ROW(G6:G12) then the
row
numbers would not correspond to the position numbers:

G6 = position 1 = ROW(G6)
G7 = position 2 = ROW(G7)
G8 = position 3 = ROW(G8)
...
G12 = position 7 = ROW(G12)

What we need to do is convert the row numbers 6:12 (a total of 7) to the
row
numbers 1:7 so that they correspond to the position numbers of the
indexed
array. Here's how we do that:

ROW(G$6:G$12)-MIN(ROW(G$6:G$12))+1

We subtract the MIN row number from the array of row numbers then add 1.
This is how that looks:

ROW(G6) - ROW(G6) = 0 + 1 = 1
ROW(G7) - ROW(G6) = 1 + 1 = 2
ROW(G8) - ROW(G6) = 2 + 1 = 3
ROW(G9) - ROW(G6) = 3 + 1 = 4
...
ROW(G12) - ROW(G6) = 6 + 1 = 7

Now we have row numbers 1:7 that correspond to the position numbers 1:7
of
the indexed array.


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
THANK YOU SIR , I REALLY APPRECIATE IT ... IT WORKED NICE.....but could
you
tell me more about : MIN(ROW(A$1:A$7))+1) ...what does this mean ?








"Mike H" wrote:

Hi,

With your search string in C1 Try this in D1 and drag down 3 rows

=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1)))

It's an array so commit with CTRL+Shift+Enter

Mike

"pierre" wrote:

i have the following given :

A B
1 asih 234
2 aa 334
3 bb 434
4 asih 534
5 cc 634
6 dd 734
7 asih 834

i managed to do the following :
{INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)}
and i dragged it to cell C3.
NOW.....
my purpose for this is to be able TO enter the word "asih" in C1
and
C2
and
C3 in order to be able its coresponding data which are : 234 ,
534 ,
834
MY QUESTION NOW IS :
what should i do to insert the following formula :
index(....),match(....) )
WITHIN THE PRECEDENT FORMULA:
{INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)}
AND THAT IN ORDER TO GET FOR EXAMPLE (734) IF I HAD TO TYPE
(DD).????

THANKS FOR YOUR HELP