Formula Errors

Fix Excel Formula Returning Sheet Number Instead of Value

Maira Mehtab Sep 22, 2026 869 views

Question details

The user's IF formula is returning the worksheet's index number (e.g., 2) rather than the actual value from the referenced cell on that specific sheet.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Attempting to retrieve a data value from a secondary worksheet using a logical IF formula.
Observed behavior: The formula returns the numerical index of the referenced worksheet instead of the text or number contained within the target cell.

Before you start

Verify that the target worksheet contains the correct data in the referenced cell before adjusting your formula structure.

Solution 1Recommended

Replace the SHEET Function with a Direct Cell Reference

Remove the SHEET() function from your formula to pull the actual cell value instead of the worksheet's position index.

The SHEET function is specifically designed to return the position number of a referenced sheet within a workbook. When you use SHEET(Sheet2!A1), the program evaluates this as a request for the sheet's index. If Sheet2 is the second sheet in your file, the formula outputs 2.

To retrieve the data stored inside the cell, you must reference the cell directly without wrapping it in the SHEET function.

Select the formula cell

Click on the cell containing the IF formula that is returning the incorrect number.

Edit the formula

Click into the formula bar at the top of the workspace to edit the text.

Remove the SHEET function

Delete the 'SHEET(' text and the closing parenthesis ')', changing the segment from 'SHEET(Sheet2!A1)' to simply 'Sheet2!A1'.

Apply the new formula

Press Enter to execute the updated formula, which should now correctly look like: =IF(A1="TEST",Sheet2!A1,"MISSING MATCH").

Formula Output Corrected: Your cell will now display the text or value stored in Sheet2!A1 whenever the logical condition is met.

Seamless Spreadsheet Editing

Easily Manage and Debug Cross-Sheet Formulas in WPS Office

WPS Spreadsheet provides a robust, user-friendly environment for writing, evaluating, and troubleshooting complex formulas across multiple worksheets.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the workbook containing your cross-sheet formulas.
2. Locate the error: Select the cell returning the unexpected sheet index number.
3. Modify the reference: Use the formula bar to delete the SHEET wrapper so the formula points directly to the target cell (e.g., Sheet2!A1).
4. Save and verify: Press Enter to update the cell value instantly, then save your workbook.

100% compatibility with Microsoft Excel formulas, functions, and formattingIntuitive formula auditing features to trace precedents and dependents quicklyAuto-complete suggestions to prevent syntax errors while typing formulasLightweight application with rapid calculation speeds for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

What exactly does the SHEET function do in a spreadsheet?

The SHEET function returns the index number of a referenced worksheet. For example, if 'SalesData' is the third tab in your workbook, the formula =SHEET(SalesData!A1) will return 3.

How can I dynamically reference a sheet name from a cell value?

You can use the INDIRECT function to build a dynamic reference. For instance, if cell A1 contains the text 'Sheet2', using =INDIRECT("'" & A1 & "'!B1") will pull the value from cell B1 on Sheet2.

Why does my cross-sheet formula return a #REF! error?

The #REF! error occurs when a formula refers to a cell that is not valid. This usually happens if the referenced worksheet was deleted, renamed improperly, or if the specific rows/columns were removed.

How do I correctly reference a cell in a completely different workbook?

To reference a different file, include the workbook name in square brackets before the sheet name. For example: ='[Budget.xlsx]Sheet1'!A1. Note that if the source workbook is closed, you must include the full file path.

Excel & Data Problems