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

tlim

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

Paul Newcome

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 <> ""))

Georgie

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:

• INDEX Function
• COUNTIF Function
• Create efficient formulas with @cell and @row

Does that work for you?

Georgie

Paul Newcome

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 <> ""))

Georgie

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:

• INDEX Function
• COUNTIF Function
• Create efficient formulas with @cell and @row

Does that work for you?

Georgie

tlim

@Paul Newcome and @Georgie Thank you both! This solution will work for my issue!

Paul Newcome

Happy to help. 👍️