Help on {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}

[Sandeep Jangra]

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

 

[Roger Govier]

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.

 

[Bob Phillips]

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")

 

[Sandeep Jangra]

thank you, Phillips... Thanks alot

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