How to return the row # of an expression in specific array of cell

I have the following expression that does it, but don't quite get it.
{=SUM(($A1=$A$2:$C$10)*(ROW($A$2:$C$10)))}
(it looks up the value contained in cell A1 and returns the row number of
the cell in the array A2:C10 that has the same value as in A1)

When I try to retype the above "formula" in a similar (or even the same)
scenario, i get a #Value error, which leads me to believe that the formula
above is created by some Excel tool, rather that user defined.

Can anyone help me understand how it works. Thanks

Rado

The formula is a array formula. Type it without the {} and press
ctrl+shft+enter to commit. Excel with put in the {}. You will have to
press ctrl+shft+enter even if you modify it.

Hope this helps.

Dave

"Rado" <Rado@discussions.microsoft.com> wrote in message
news:0B18B786-01E4-4B9B-AC3E-285DC07FE4CB@microsoft.com...
>I have the following expression that does it, but don't quite get it.
> {=SUM(($A1=$A$2:$C$10)*(ROW($A$2:$C$10)))}
> (it looks up the value contained in cell A1 and returns the row number of
> the cell in the array A2:C10 that has the same value as in A1)
>
> When I try to retype the above "formula" in a similar (or even the same)
> scenario, i get a #Value error, which leads me to believe that the formula
> above is created by some Excel tool, rather that user defined.
>
> Can anyone help me understand how it works. Thanks
>

Thanks Dave - very helpful!

"Dave" wrote:

> Rado
>
> The formula is a array formula. Type it without the {} and press
> ctrl+shft+enter to commit. Excel with put in the {}. You will have to
> press ctrl+shft+enter even if you modify it.
>
> Hope this helps.
>
> Dave
>
> "Rado" <Rado@discussions.microsoft.com> wrote in message
> news:0B18B786-01E4-4B9B-AC3E-285DC07FE4CB@microsoft.com...
> >I have the following expression that does it, but don't quite get it.
> > {=SUM(($A1=$A$2:$C$10)*(ROW($A$2:$C$10)))}
> > (it looks up the value contained in cell A1 and returns the row number of
> > the cell in the array A2:C10 that has the same value as in A1)
> >
> > When I try to retype the above "formula" in a similar (or even the same)
> > scenario, i get a #Value error, which leads me to believe that the formula
> > above is created by some Excel tool, rather that user defined.
> >
> > Can anyone help me understand how it works. Thanks
> >
>
>
>