Help on {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 ))}
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 |
Help on {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 ))}
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 |
Help on {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 ))}
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 |
Help on {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 ))}
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 |
All times are GMT +1. The time now is 01:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com