0

I've seen this formula in random places but I can't remember it now, nor can I find it.

If I do something like A2:G, it will get all the rows, including empty ones.

I have seen some formula that, I think, starts like A2:INDEX... that can be used to reference all the rows column A has data in it.

I want to use this formula to reference A2:G but stop at the last row where column A has data.

Editing question to support reopening it as not a duplicate:

This question was closed as a duplicate of ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?. While PART of ONE of the answers in that does answer my question, the core question in my post is very different from the core question in the other post. It creates confusion to say they are duplicates. My question asks a very specific question, with a very specific answer. If someone searches StackOverflow for for the answer to a question like mine, they will expect to see it worded how I did. They will not expect it to be PART of the answer to ONE of the answers in a question that is worded differently. Words matter, and the words in my question speak to a very different question than the other one. Please re-open my question. If someone replies to my question with what was mentioned (as a sub-mention) in the other question, then I will accept it as an answer.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89
  • While I agree partly with the reasoning for reopening, the banner clearly says [``This question has answers here``](https://i.stack.imgur.com/Qf67e.png) and not that ``This question is a exact duplicate``(**Words matter!**). Users may be better directed to related question with a better answer. – TheMaster Apr 11 '23 at 05:17

2 Answers2

1

You may try:

=A2:index(G:G,match(2,1/(A:A<>"")))
rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • This answer does seem to work but it's the other one I was really looking for. Thank you! – IMTheNachoMan Apr 12 '23 at 18:42
  • 1
    Well both should work perfect when you have full length data across all the rows (no complete blank rows in b/w). however this `index+match` variant excels in picking up the last row even if there's an odd ball scenario like when data has blank rows in the midst. this sample data screenshot should help in visualizing what I am referring to (https://i.imgur.com/dtUWd9F.png) – rockinfreakshow Apr 12 '23 at 19:24
  • I see. Let me see if I need this for my use-case. – IMTheNachoMan Apr 15 '23 at 15:39
1
=A1:INDEX(G:G,COUNTA(G:G))

The "hack" works because INDEX returns a value as well as a cell reference. The cell reference returned can be joined using the range operator(:) with an another cell reference to create a range.

  • COUNTA(G:G): Counts the number of cells in G. If it's 5, it returns 5

  • INDEX(G:G,COUNTA(G:G)) returns G5

  • A1:INDEX(G:G,COUNTA(G:G)) returns A1:G5

See more insights in this answer

TheMaster
  • 45,448
  • 6
  • 62
  • 85