Turning a column number into a cell reference

[Andrew Steen]

I use the MATCH function to figure out what column in a list of numbers
contains the number I'm looking for. I would like to turn that column
number into a cell reference, so that I could write a formula that does
something like:
"add up all the numbers in this column, until you get to the number 19"

In other words, I would like to write a formula like this:
=SUM(A1:A[MATCH(19,A1:A100)])

However, I don't know how the value that MATCH returns into the column part
of a cell reference.

Any help would be greatly appreciated.

Thanks,
Drew Steen
(e-mail address removed)

 

[Anders S]

Drew,

It looks like you need the INDIRECT() function.

=SUM(INDIRECT("A1:A"&MATCH(19,A1:A25,0)))

HTH
Anders Silvén