CODEWITHSUNDEEP
excelexcel-formula
BuZz
BuZz

Reputation: 17425

In pure Excel, how to reference a column in a range (no VBA)

I have on a sheet a range of numbers, from that range, how can I reference a full column or row given by index ?

I am using 2007.. I looked into HLookup/Vlookup/Index without success

Upvotes: 6

Views: 68854

Answers (3)

stevepastelan
stevepastelan

Reputation: 1304

If you can convert your range to a table, then you can use structured references to refer to columns in your table.

Example

  • your table is called Table1 and has columns Column1, Column2, Column3
  • cell E1 has the name of a column (e.g. Column1)

In this case, you can calculate (for instance) the sum of the column specified by E1 with:

=SUM(INDIRECT("Table1["& E1 &"]"))

Upvotes: 5

Doug Glancy
Doug Glancy

Reputation: 27478

How about something like:

=SUM(INDEX(2:1048576,0,2))

This sums the entire 2nd column in the specified range, i.e., B2:B1048576. It works because a 0 in the row argument of the index function returns the entire column - within the range specified. The following would sum just B2:B3:

=SUM(INDEX(2:3,0,2))

... and this would sum C2:C3

=SUM(INDEX(B2:F3,0,2))

Upvotes: 2

Skytunnel
Skytunnel

Reputation: 1083

I'm guessing you mean referencing the whole column/row as a range/array in another formula?

If so, the only way i can think of would be to use OFFSET

=OFFSET($A:$A,,MyColIndex-1)
=OFFSET($1:$1,MyRowIndex-1,)

thats...

=OFFSET(reference,rows,cols,[height],[width])

However this won't work if you insert a column to the left of A, or a row above 1.

EDIT: just found a way around that little problem...

=OFFSET($A:$A,,MyColIndex-Column($A:$A))
=OFFSET($1:$1,MyRowIndex-Row($1:$1),)

Upvotes: 12

Related Questions