Function Problems

How to Return the Latest Numeric Balance from Another Excel Sheet

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to retrieve the most recently added numeric balance from a specific column located in a different worksheet.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Tracking financial or data balances over time across multiple sheets where new monthly values are continuously added to a column.
Observed behavior: The goal is to automatically extract the last numeric value in a continuously updated column without having to manually change the cell reference each time a new entry is made.

Before you start

Ensure that you know the exact name of your source worksheet and the specific column letter where your balance data is being entered.

Solution 1Recommended

Use the LOOKUP Function with a Large Number

You can force Excel to find the last numeric entry in a column by searching for an exceptionally large number using the LOOKUP function.

By searching for a number larger than any possible data entry (such as 9.99999999999999E+307), the LOOKUP function will scan the entire column. Since it cannot find an exact match for this massive number, it defaults to returning the last numeric value it encounters in that range.

Select the Target Cell

Click on the cell in your current sheet where you want the latest balance to be displayed.

Enter the LOOKUP Formula

Type `=LOOKUP(9.99999999999999E+307, Balance!C:C)` into the formula bar.

Adjust Sheet and Column References

Change the word `Balance` to match the exact name of your source sheet. Change `C:C` to match the column letter that contains your growing list of numeric balances.

Apply the Formula

Press Enter. The cell will now automatically display the last numeric value added to the specified column.

Understanding 9.99999999999999E+307: This specific scientific notation represents the largest number Excel and WPS Spreadsheet can process. Using it guarantees that the formula checks every single numeric value in the column and stops precisely at the last one.

Efficient Data Tracking in WPS Office

Use WPS Spreadsheet to Manage Your Financial Balances

WPS Spreadsheet fully supports advanced lookup functions, making it incredibly easy to track your latest balances, manage complex financial data, and sync your work across multiple devices.

1. Open WPS Spreadsheet: Launch WPS Office and open your financial workbook.
2. Apply the Formula: Select your summary cell and input the LOOKUP formula to fetch the last numeric value.
3. Update Seamlessly: Continue adding new monthly balances to your source sheet, and watch your target summary cell update automatically without any extra effort.

Fully compatible with Microsoft Excel formulas including LOOKUP, VLOOKUP, and XLOOKUP.Free, lightweight, and fast spreadsheet tool for everyday data management.Cross-platform support allows you to manage your finances on Windows, Mac, iOS, and Android.

microsoft office alternative - wps office

Frequently Asked Questions

Will this LOOKUP formula ignore text or blank cells?

Yes, because the lookup value is a number, the LOOKUP function ignores any text strings, errors, or blank cells in the column, returning only the final numeric entry.

How can I return the last value in a row instead of a column?

You can change the column reference to a row reference. For example, use `=LOOKUP(9.99999999999999E+307, Balance!2:2)` to find the last numeric value in row 2 of the 'Balance' sheet.

What if my source sheet name contains spaces?

If your worksheet name has spaces, you must enclose it in single quotation marks within the formula. For example: `=LOOKUP(9.99999999999999E+307, 'Monthly Balance'!C:C)`.

Does this formula work the same way in WPS Spreadsheet as it does in Excel?

Absolutely. WPS Spreadsheet is highly compatible with Excel's formula engine, so this exact LOOKUP formula functions identically in both programs.

Excel & Data Problems