I need a formula to return the last, non-blank value in a row

My formula is:
=JOIN(COLLECT([Week 1]2:[Week 52]2, NOT(ISBLANK([Week 1]2:[Week 52]2))), "LAST")
This formula returns an "Incorrect argument set" error.
What am I missing?
Thanks,
Tony
Best Answers
-
You would need this instead:
=INDEX(COLLECT([Week 1]@row:[Week 52]@row, [Week 1]@row:[Week 52]@row, @cell <> ""), COUNTIFS([Week 1]@row:[Week 52]@row, @cell <> ""))
-
Hi @tlim,
Take a look at this related thread: How to look at multiple columns in a row and return the last "Not Blank" cells in the current cell. Based on the formula @Kelly Moore provided there, try the below:
- =INDEX([Week 1]2:[Week 52]2 , 1, COUNTIF([Week 1]2:[Week 52]2, <>””))
If you want to use the same formula in each row, you could swap the cell references for “@row”, so your formula would be:
- =INDEX([Week 1]@row:[Week 52]@row , 1, COUNTIF([Week 1]@row:[Week 52]@row, <>””))
For more information, check out the following resources:
Does that work for you?
Georgie
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
You would need this instead:
=INDEX(COLLECT([Week 1]@row:[Week 52]@row, [Week 1]@row:[Week 52]@row, @cell <> ""), COUNTIFS([Week 1]@row:[Week 52]@row, @cell <> ""))
-
Hi @tlim,
Take a look at this related thread: How to look at multiple columns in a row and return the last "Not Blank" cells in the current cell. Based on the formula @Kelly Moore provided there, try the below:
- =INDEX([Week 1]2:[Week 52]2 , 1, COUNTIF([Week 1]2:[Week 52]2, <>””))
If you want to use the same formula in each row, you could swap the cell references for “@row”, so your formula would be:
- =INDEX([Week 1]@row:[Week 52]@row , 1, COUNTIF([Week 1]@row:[Week 52]@row, <>””))
For more information, check out the following resources:
Does that work for you?
Georgie
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Paul Newcome and @Georgie Thank you both! This solution will work for my issue!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!