T
Mon May 12 01:11:09 CDT 2008
This is hard to *clearly* explain and even harder for someone to understand.
Let's see if I can do a good job of explaining it...
=INDEX(A$1:A$7,SMALL(IF(($A$1:$A$7=$C$1)+($A$1:$A$7=$C$2),ROW(A$1:A$7)-MIN(ROW(A$1:A$7))+1),ROWS(D$1:D1)))
The INDEX function holds an array of values. These values are in specific
positions relative to the array. In the above formula the indexed array is
the range A1:A7. Their positions within the indexed array are:
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" <pierre@discussions.microsoft.com> wrote in message
news:79D9B03D-B1C6-44B5-B1EA-4BB91FFDB340@microsoft.com...
> 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
>>
>>
>>