SOLVED

How to Reference the Last Cell in a Column in a Table?

Brass Contributor

Sheet One with TableSheet One with TableSheet Two Referencing Prior TableSheet Two Referencing Prior Table

 

I have a table where I add data at the end of every day. I would like a formula to reference the last value in a column. Is there an easy way to do that? In the photos above, I would like B5 on Sheet2 to reference the value in E14 on Sheet1, which is the last value in KeyThree column.

 

I am not well versed in commands for tables. 

 

Aside from specific table commands, by knowing the count, I could use index or offset to reference the appropriate cell. But I am curious if there is a more elegant solution where I don't have to keep track of the count.

 

I have uploaded this simple spreadsheet.

 

I look forward to your comments. Thank you.

24 Replies

@KStecyk 

 

I believe this is one option that should work:

=LOOKUP(2,1/(KeyTable[KeyThree]<>""),KeyTable[KeyThree])

@KStecyk As a variant:

 

=INDEX(KeyTable[KeyThree],MATCH(99^99,KeyTable[KeyThree]))

 

Thank you, JMB17, for your reply. It helped me to create my own formula, which I will show in the next reply.
best response confirmed by KStecyk (Brass Contributor)
Solution
Thank you for your reply. I looked at your and JMB17's reply to create me own.

Initially, I mentioned about the count. I did not want to have to manually keep track of the count, but I can use the count function to help me.

I am not sure how to show code, but my formula is as follows:

` =INDEX(KeyTable[KeyThree], COUNT(KeyTable[KeyThree]))

It's really just a hybrid of the two prior ideas.

Thank you for helping me with this small problem.

@KStecyk 

As variant

=INDEX(KeyTable[KeyThree],XMATCH(,KeyTable[KeyThree])-1)
I'm using Power Query on Excel for Mac. I save my "iqy" file to a Queries Folder, but then when I go to to Excel and go to Data/Get External Data/Run Web Query, my "iqy" files are all greyed out, except for the sample files from MS which are not greyed out. Does anyone know why my files would be greyed out and how to change that.

@Deleted

Rather than hijacking this thread with your different topic, you'll likely get more success with your own separate thread. That way, people can see and focus on your specific question. If they read my question, they might not even bother to read further.

Good luck.

@KStecyk 

Like the INDEX/MATCH this will return a single-cell range reference

= LET(
  field, KeyTable[KeyThree],
  XLOOKUP(1, SIGN(field), field,,,-1) )

The LOOKUP formula would do fine to return the value.

@Sergei Baklan 

 

Can you, please, explain your formula? When I look at the XMATCH function, I notice that the first argument is required. Yet you seemed to have skipped the first argument. I'd appreciate knowing how your formula works.

 

=INDEX(KeyTable[KeyThree],XMATCH(,KeyTable[KeyThree])-1)

 

Below is a link to the XMATCH function.

 

https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312

@Peter Bartholomew 

 

Thank you, Peter, for your response. While I am confident that your solution works mighty fine, it looks complicated to me. I am okay with the Let function, but the XLOOKUP with all the different arguments seems complicated. Even so, it is good to see different alternatives. Sometimes, though, it is good to do things differently. So I appreciate your help.

@Sergei Baklan 

 

I tried the following formula in an attempt to understand what you did:

 

=XMATCH(,KeyTable[KeyThree])

 

I got an answer of 13, which is one more than the number of filled rows. 

 

I also tried the following:

=XMATCH("*",KeyTable[KeyThree])

 

I got #N/A for an answer. 

 

I am guessing that when you skip the first argument, XLOOKUP looks for any value, similar to a wildcard. But I got a value of 1 greater than the number of data rows. So I am puzzled. I am also puzzled why a wildcard failed. 

 

Can you, please, elaborate?

 

Kevin

 

@KStecyk 

I hadn't realised that you could simply omit the lookup value but it appears to work as a search for the first blank cell, hence the index returned is 1 larger than the last occupied cell.

 

To make your search for the last cell work, several changes are needed

= INDEX( KeyTable[KeyThree], 
  XMATCH( "*", KeyTable[KeyThree], 2, -1) )

Firstly, the table would need to be text and not numeric.  By default, XMATCH performs an exact match without wildcards so the parameter value 2 is required.  Finally, you require the last match, not the first, so the -1 is used.

 

My use of SIGN within XLOOKUP causes it to return the last positive number; text, zeros and even negative numbers would be ignored.

 

I agree that my using LET is unnecessarily verbose, especially since the structured references to the table already convey meaning.  I suspect that it is also a matter of what one gets used to.  If all the formulae in the workbook use LET to provide a level of self-documentation, then it would no longer be seen as complicated.  

 

 

@KStecyk 

A 'novelty' workbook loosely based on your question.image.png

@Peter Bartholomew 


I hadn't realised that you could simply omit the lookup value but it appears to work as a search for the first blank cell, hence the index returned is 1 larger than the last occupied cell.

 

I had to remove the rest of your post from the quote because I system wouldn't post it. There was a hiccup with html or something.

 

Thank you, Peter, for your excellent explanations. Now, I understand why XMATCH works with the first argument missing. And thank you for the explanation on using the wildcard search. I understand your comments.

@Peter Bartholomew 

I very much appreciate the time you've taken to create a novelty spreadsheet. I am learning from you.

 

I found your formulas break if the last value is negative. That is, the formula gives the wrong answer. I tried to create a formula to address this weakness, but my formula doesn't work. I am hoping you can spot my error and fix.

 

PeterSpreadsheetPic.jpg

 

I have included a version 2 of your spreadsheet. 

 

I look forward to your response. 

 

Kevin

@Peter Bartholomew 

 

I solved my own problem. I had a typo because I copied the wrong formula. If you open the enclosed spreadsheets, you'll see.

 

PeterSpreadsheetPic2.jpg

 

I will also enclose version 2a of the spreadsheet. 

 

Thank you!

 

Kevin

@KStecyk 

What you are doing is perfectly correct.  From the original example, I was guessing how you wanted to treat text, zeros, negative numbers, Booleans, Errors.

I would also note that the formula at the foot of the table could be simplified by taking advantage of relative structured referencing, but that does not apply to the lookup from sheet 2 or the conditional formatting.

= LET(
  header, KeyTable[#Headers],
  field,  KeyTable[KeyTwo],
  XLOOKUP(TRUE, ISNUMBER(field), field,"empty",,-1))

 

@KStecyk 

Hi there,

I hope the following formula will help you to find last cell value in a Column in a Table.

 

=LOOKUP(3, 1/(KeyTable[KeyOne]<>""),KeyTable[KeyOne])

@KStecyk , both XMATCH and XLOOKUP accept blank as value for the lookup. You may use reference on any blank cell or skip first argument, that is interpreted as blank value. Result will be the same.

1 best response

Accepted Solutions
best response confirmed by KStecyk (Brass Contributor)
Solution
Thank you for your reply. I looked at your and JMB17's reply to create me own.

Initially, I mentioned about the count. I did not want to have to manually keep track of the count, but I can use the count function to help me.

I am not sure how to show code, but my formula is as follows:

` =INDEX(KeyTable[KeyThree], COUNT(KeyTable[KeyThree]))

It's really just a hybrid of the two prior ideas.

Thank you for helping me with this small problem.

View solution in original post