I have a spreadsheet where I need a reference to a whole column. The catch is that I don't know which column ahead of the time, so I use MATCH function to find that out. Once I find the right column I need to reference cells in it.

Two problems stop me from using something simple like CHOOSE function instead:

  • I have couple hundreds of columns that could be referenced
  • I need to reference whole column simply, because the amount of rows is varying as well

Originally I was thinking about using MATCH, ADDRESS , INDIRECT, but ADDRESS requires me to specify rows as well. Then I thought I could use MATCH and CODE, but my formula for columns with multiple letters was buggy.

How do I achieve this? (Reference whole column based on a lookup/match.)

  • 1
    What is the criteria to match the new column? Are you looking for a column by it's header? A certain value? – Dave Sep 30 '14 at 14:30
  • If you can match the header for the correct column, you can use that cell's column. We need the entire function to be able to figure out what you're trying to do. – Raystafarian Sep 30 '14 at 14:30

Sorry for the vague way of asking the question.

I figured out the answer though here is a simple formula that will convert number to column reference: substitute(address(1,YOURNUMBER),"$1","")

As I said earlier I am using match to find my column number so at YOURNUMBER I am using result of the match.


Number of cells in a column is a constant, it is 1048576 since Excel 2007 and it was 65535 if in earlier versions I recall correctly. If you open xls (old format) files it falls back to the old rows count, so you might want to account for that.

So you might reference the range that starts with row 1 and ends with row 104576 like A1:A1048576, but using whatever formulas you use.

However, referencing whole columns might make your spreadsheet very slow. Usually it's a good idea to count rows (using COUNTROWS once or anything else) and reference the range exactly.


Simple and eloquent

  • It's only simple and eloquent if someone understands what's going on here. Your answer lacks necessary contextual information. What is Offset and what are the arguments you're following it with? How can this be applied to a situation where you're looking for Column A? Please use the EDIT button to add helpful and explanatory information to help people apply it to their situations. – music2myear Feb 21 '19 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.