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

  • Thread starter Thread starter Sandeep Jangra
  • Start date Start date
S

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
 
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.
 
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")
 
thank you, Phillips... Thanks alot

Bob Phillips said:
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
Click to expand...
 
Back
Top