How to Find the Last Non-Empty Value in a Column?
If you're dealing with a column that keeps getting new data, this formula helps you quickly find the last non-empty entry.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Get the last non-empty value in a column that is continuously being populated with new data"
=LOOKUP(2,1/(A:A<>""),A:A)This formula retrieves the last non-empty value from a specified column in Excel.
Step-by-step breakdown
- The LOOKUP function searches for a value in a range.
- The expression 2,1/(A:A<>"") generates an array of 1s and errors for non-empty cells.
- LOOKUP finds the last 1 in this array, which corresponds to the last non-empty cell.
- The final argument A:A specifies the range from which to return the last value.
Edge cases & warnings
- The column is completely empty, resulting in an error.
- The last cell in the column is empty, causing the formula to return the previous non-empty cell.
- The column contains only formulas that return empty strings, which may lead to unexpected results.
Examples
"Column A: {"apple", "banana", "", "", "cherry"}"
"cherry""Column A: {"", "", "", "", "", "grape"}"
"grape"Frequently asked questions
What if my column has blank cells?
The formula will skip blank cells and return the last non-empty value.
Can I use this formula for multiple columns?
You need to adjust the range in the formula for each specific column.
What happens if the column is entirely empty?
The formula will return an error since there are no values to retrieve.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.