W
wilchong via OfficeKB.com
Dear sir,
There is 1 data set is assumed running in A1 down, viz.:
In A1 down is: 1,2,3,4,5,6,7,8,9, and 10
In F1 down is: 20, 7, 1, 12 and 8
And I have a excel forumula in the cell M1 as below:
{=IF(ROWS(M$1:M1)<=SUM(--(COUNTIF($F$1:$F$22,$A$1:$A$10)>0)),INDEX($A$1:$A$10,
SMALL(IF(COUNTIF($F$1:$F$22,$A$1:$A$10)>0,ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10))
+1),ROWS(M$1:M1))),"")}
And then I drag the formula from M1 to M5. Therefore, the formula will give
me the result "1" in cell M1, "7" in cell M2 and "8" in cell M3. The formula
is extracted the value from F column according to the database in A column.
The formula also display the value from lowest value to highest value.
My question is that how can I modify the existing formula if I want to
display value in this order: 7,1 and 8. This order is exactly the order which
find in F column.
Many thanks,
Wilchong
There is 1 data set is assumed running in A1 down, viz.:
In A1 down is: 1,2,3,4,5,6,7,8,9, and 10
In F1 down is: 20, 7, 1, 12 and 8
And I have a excel forumula in the cell M1 as below:
{=IF(ROWS(M$1:M1)<=SUM(--(COUNTIF($F$1:$F$22,$A$1:$A$10)>0)),INDEX($A$1:$A$10,
SMALL(IF(COUNTIF($F$1:$F$22,$A$1:$A$10)>0,ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10))
+1),ROWS(M$1:M1))),"")}
And then I drag the formula from M1 to M5. Therefore, the formula will give
me the result "1" in cell M1, "7" in cell M2 and "8" in cell M3. The formula
is extracted the value from F column according to the database in A column.
The formula also display the value from lowest value to highest value.
My question is that how can I modify the existing formula if I want to
display value in this order: 7,1 and 8. This order is exactly the order which
find in F column.
Many thanks,
Wilchong