Sep 28 2020 07:23 PM
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.
Sep 28 2020 09:01 PM
I believe this is one option that should work:
=LOOKUP(2,1/(KeyTable[KeyThree]<>""),KeyTable[KeyThree])
Sep 28 2020 09:33 PM - edited Sep 28 2020 10:03 PM
Sep 29 2020 01:05 PM
Sep 29 2020 01:09 PM
SolutionSep 29 2020 02:02 PM
Sep 29 2020 04:02 PM
Sep 29 2020 04:05 PM - edited Sep 29 2020 04:17 PM
@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.
Sep 29 2020 04:05 PM
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.
Sep 29 2020 04:10 PM
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
Sep 29 2020 04:16 PM
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.
Sep 29 2020 04:27 PM
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
Sep 29 2020 10:47 PM
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.
Sep 30 2020 12:00 AM - edited Sep 30 2020 01:46 AM
A 'novelty' workbook loosely based on your question.
Sep 30 2020 07:42 PM
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.
Sep 30 2020 08:20 PM
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.
I have included a version 2 of your spreadsheet.
I look forward to your response.
Kevin
Sep 30 2020 08:34 PM
I solved my own problem. I had a typo because I copied the wrong formula. If you open the enclosed spreadsheets, you'll see.
I will also enclose version 2a of the spreadsheet.
Thank you!
Kevin
Sep 30 2020 11:32 PM
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))
Oct 01 2020 12:42 AM
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])
Oct 01 2020 12:44 PM
@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.
Sep 29 2020 01:09 PM
Solution