Dear All,
Plz help on this formula
{=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 ))}, how this works as I m
new excel user. what does is meant $A$1:$A$7=$A$10

Hi

As an array formula, it is testing each cell in the range A1 to A7 to see if
it has the same value as that in cell A10
and returning what is the first row number that the value occurs in the
range.

--
Regards
Roger Govier

"Sandeep Jangra" wrote in message
...
Dear All,
Plz help on this formula
{=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 ))}, how this works as I
m
new excel user. what does is meant $A$1:$A$7=$A$10

Expanding a little on Roger's reply.

This part

IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)

as Roger said, checks each value in A1:A7 against A10, and it builds an
array of values of that row number where it is the same else it returns
FALSE. The end result is an array of row numbers that match or FALSE where
they do not match.

The SMALL then grabs the lowest value from this array as the final result.
Serendipitously, SMALL ignores the FALSE, it doesn't choke on it, so it
ensures the first matching row number.

One problem is that if there is no match, it return #NUM. This can be
catered for with

=IF(COUNTIF($A$1:$A$7,$A$10),SMALL(IF($A$1:$A$7=$A $10,ROW($A$1:$A$7)),ROW(1:1)),"no
match")

--
__________________________________
HTH

Bob

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

As an array formula, it is testing each cell in the range A1 to A7 to see
if it has the same value as that in cell A10
and returning what is the first row number that the value occurs in the
range.

--
Regards
Roger Govier

"Sandeep Jangra" wrote in
message ...
Dear All,
Plz help on this formula
{=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 ))}, how this works as
I m
new excel user. what does is meant $A$1:$A$7=$A$10

thank you, Phillips... Thanks alot

"Bob Phillips" wrote:

Expanding a little on Roger's reply.

This part

IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)

as Roger said, checks each value in A1:A7 against A10, and it builds an
array of values of that row number where it is the same else it returns
FALSE. The end result is an array of row numbers that match or FALSE where
they do not match.

The SMALL then grabs the lowest value from this array as the final result.
Serendipitously, SMALL ignores the FALSE, it doesn't choke on it, so it
ensures the first matching row number.

One problem is that if there is no match, it return #NUM. This can be
catered for with

=IF(COUNTIF($A$1:$A$7,$A$10),SMALL(IF($A$1:$A$7=$A $10,ROW($A$1:$A$7)),ROW(1:1)),"no
match")

--
__________________________________
HTH

Bob

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

As an array formula, it is testing each cell in the range A1 to A7 to see
if it has the same value as that in cell A10
and returning what is the first row number that the value occurs in the
range.

--
Regards
Roger Govier

"Sandeep Jangra" wrote in
message ...
Dear All,
Plz help on this formula
{=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 ))}, how this works as
I m
new excel user. what does is meant $A$1:$A$7=$A$10