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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Thanks Dave - very helpful!

Dave said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
Excel formula, sum the value cell and if empty cell left blank 0
Vlookup and return sheet name also 2
Create an array of separate cells 1
MATCH to ignore blank cells in array 3
specify an ending row in an array formula 3
Storing an array in a cell 9
Array Reference 3

Back
Top