CODEWITHSUNDEEP
excelexcel-formula
toolshed
toolshed

Reputation: 2029

Is it possible to refer to an entire column by its number in an Excel formula?

Currently I am trying to set up a dynamic range using this formula structure:

=OFFSET($A$2,,,COUNTA($A:$A)-1)

I then modified the formula to find a keyword in the header row:

=OFFSET($A$2,,MATCH("THIS",$1:$1,0),COUNTA($A:$A)-1)

My issue is the COUNTA statement at this point. I want to type something like COUNTA(COLUMN(MATCH("THIS",$1:$1,0))) but it doesn't appear to be this simple.

Instead my workaround is something to the effect of:

COUNTA(INDIRECT(LEFT(ADDRESS(1,MATCH("THIS",$1:$1,0)),2)&":"&LEFT(ADDRESS(1,MATCH("THIS",$1:$1,0)),2)))-1

The entire formula becomes rather long and cumbersome, and I have a feeling that I might be missing something to simplify it. Is there an easier way to do this that would be similar to simply evaluating to COUNTA(Column(10)) or is my approach the most efficient method?

Upvotes: 0

Views: 151

Answers (1)

barry houdini
barry houdini

Reputation: 46371

Try this definition

=OFFSET($A$2,,MATCH("THIS",$1:$1,0)-1,COUNTA(OFFSET($A:$A,,MATCH("THIS",$1:$1,0)-1))-1)

The second OFFSET gets the whole column

Upvotes: 1

Related Questions