help..

[pierre]

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

 

[Mike H]

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]

it worked ,THANK YOU SIR, but can you tell me more about this formula
especially this part : ROW($B$1:$B$10)-ROW($B$1)+1),ROW(A1)))

AGAIN THANK YOU

 

[pierre]

SUPPOSE now , i have the following data :

A B

suppose i want to have the results of "asih" and " BB"...how to edit the
formula :

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

PLEASE HELP

 

[T. Valko]

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

Try this:

C1 = ASIH
C2 = BB

Array entered** in D1:

=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$11)))

Copy across to E1 then down until you get #NUM! errors meaning all the
relative data has been extracted.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[pierre]

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 ?

 

[GG]

MIN :
Returns the smallest number in a set of values.
This portion is looking into the smallest of the array, it will store the
reference in the variable "ROW"+ 1
Search the help file for MIN.

 

[T. Valko]

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$11)))

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.

 

[pierre]

THANK YOU SO MUCH....now i have learned something new, thanks to you ...and i
added it to my EXCEL arsenal




indexed array are:

 

[T. Valko]

You're welcome. Thanks for the feedback!