Formula Errors

Excel Formula to Return the Last Non-Empty Item in a Column

Ayan Masood Sep 29, 2026 868 views

Question details

The user needs to retrieve the value of the last cell containing data in a specific Excel column.

How to Return the Last Non-Empty Item in an Excel Column

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Performing data extraction or analysis where the column length varies and may contain blank cells mixed within the dataset.
Observed behavior: Requires a dynamic formula that can accurately identify and return the last populated cell in a column without manual scrolling.

Before you start

Before applying these formulas, ensure you know whether your column contains text, numbers, or a mix of both, as well as whether there are blank cells scattered within your data range.

Solution 1Recommended

Use the LOOKUP Formula (Best for Mixed Data with Blanks)

This is the most robust method, as it works universally for text, numbers, and columns containing empty cells in between data.

The LOOKUP function can be manipulated with an array operation to easily pinpoint the last non-blank cell in any column, ignoring empty strings and gaps in the data.

Select the destination cell

Click on the cell where you want the final extracted value to appear.

Input the LOOKUP formula

Type the formula =LOOKUP(2,1/(A:A<>""),A:A) assuming your target data is in column A. If your data is in column B, replace A:A with B:B.

Apply the formula

Press the Enter key. The cell will now display the last non-empty value from the specified column.

Use the LOOKUP Formula (Best for Mixed Data with Blanks)

How it works: The expression (A:A<>"") evaluates the column and returns TRUE or FALSE. Dividing 1 by this array creates an array of 1s and #DIV/0! errors. LOOKUP(2, ...) searches for 2, doesn't find it, and falls back on the last numeric value (the last 1), thereby returning the corresponding cell's value.

Solution 2

Use INDEX and COUNTA (For Contiguous Data)

If your column is completely filled from top to bottom with absolutely no blank cells in between, this method is a simpler alternative.

Solution 3

Use INDEX and XMATCH (For Modern Excel Versions)

If you are using Microsoft 365 or newer spreadsheet versions, XMATCH provides an efficient, built-in way to search from the bottom up.

Efficient Spreadsheet Software

Easily Manage Advanced Formulas with WPS Spreadsheet

WPS Office Spreadsheet provides comprehensive support for array formulas like LOOKUP, INDEX, and MATCH. It is a powerful, free alternative that perfectly handles complex data extraction tasks.

1. Download WPS Office: Download and install WPS Office for free from the official website.
2. Open your dataset: Launch WPS Spreadsheet and open your existing Excel workbook.
3. Apply the formula: Select a cell and input the =LOOKUP(2,1/(A:A<>""),A:A) formula to instantly find the last data entry in your column.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Lightweight software that loads large datasets quickly.Built-in advanced data analysis tools completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the LOOKUP formula returning a #DIV/0! or #N/A error?

This typically occurs if the entire referenced column is completely blank without a single data entry. Ensure that there is at least one non-empty cell in the specified range for the formula to evaluate successfully.

Can I find the last non-empty cell in a row instead of a column?

Yes, you can apply the exact same LOOKUP formula but adjust the reference range to a row. For example, use =LOOKUP(2,1/(1:1<>""),1:1) to find the last populated cell in row 1.

Does the INDEX and COUNTA method work with formulas that return an empty string?

No. The COUNTA function counts cells containing formulas even if those formulas return an empty string (""). If your column contains formula-generated blanks, you should use the LOOKUP method instead to prevent inaccurate results.

Excel & Data Problems