Function Problems

How to Fix INDEX and MATCH References to Another Excel Worksheet

Kushani Nimanthika Oct 22, 2026 869 views

Question details

The user needs to correct an INDEX and MATCH formula that retrieves data from a different worksheet, particularly when copying the formula down a column.

How to Fix INDEX and MATCH References to Another Excel Worksheet

Product: Excel
Device & OS: not provided
Scenario: Retrieving data values from a separate worksheet using INDEX and MATCH and filling the formula down across multiple rows.
Observed behavior: The formula returns errors or incorrect values because the lookup cell reference remains locked, or the source data ranges shift when the formula is dragged down.

Before you start

Before modifying your formulas, ensure that the source worksheet name doesn't contain typos and that the data types in both your lookup column and source column match exactly (e.g., both are formatted as Text or both as Numbers).

Solution 1Recommended

Adjust Absolute and Relative References in Your Formula

Correctly configuring your cell references ensures your INDEX and MATCH formula retrieves the correct data even when copied across multiple rows.

When retrieving data from another worksheet, it is vital to lock the source data arrays so they don't shift, while leaving the lookup value dynamic.

Select the formula cell

Click on the cell containing your current INDEX and MATCH formula to view it in the formula bar.

Change the lookup value to a relative reference

Locate the lookup value in your MATCH function. If it is currently locked as an absolute reference (e.g., $A$1), remove the dollar signs to make it a relative reference (e.g., A1).

Lock the source data arrays

Ensure your source data ranges for both the INDEX array and the MATCH lookup array are absolute. They should include dollar signs (e.g., PicturesList!$A$3:$A$250). You can highlight the range and press F4 to quickly add these.

Apply and fill down

Press Enter to save the corrected formula. Then, click the small square at the bottom right of the cell and drag it down to apply the formula to the remaining rows.

Adjust Absolute and Relative References in Your Formula

Understanding Reference Types: Using absolute references ($) locks the specific row or column so it won't change, while relative references adjust automatically based on the cell's new position when copied.

Solution 2

Verify Worksheet Names and Data Syntax

A common cause for INDEX and MATCH errors across sheets is a mismatch in the worksheet name or invalid syntax within the formula.

Efficient Spreadsheet Management

Use WPS Spreadsheet to Seamlessly Handle Advanced Formulas

WPS Spreadsheet fully supports advanced functions like INDEX and MATCH, allowing you to quickly retrieve data across multiple worksheets with accurate formula references and helpful error checking.

1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing the multiple sheets you need to link.
2. Start the INDEX function: Type =INDEX( in your target cell, switch to the source worksheet, select the data range you want to return, and press F4 to lock it as an absolute reference.
3. Nest the MATCH function: Type ,MATCH( and select your relative lookup cell (e.g., A1), then switch to the source worksheet to select the lookup array, pressing F4 to lock this range as well.
4. Finish the formula: Add ,0)) to find an exact match, press Enter, and drag the formula down the column to automatically adjust the relative references.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Built-in formula auditing tools to easily trace references and troubleshoot errors.Smart auto-complete suggestions for functions and worksheet references.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX MATCH formula return an #N/A error when referencing another sheet?

The #N/A error indicates that the lookup value cannot be found in the source array. This often happens if there are hidden spaces in the text, mismatching data types (e.g., numbers formatted as text), or if the lookup range shifted because absolute references ($) were not applied to the source arrays.

Do I need to put the worksheet name in single quotes in the formula?

You only need to enclose the worksheet name in single quotes if it contains spaces or special characters (for example, 'Sales Data'!$A$1:$A$10). If the sheet name is a single word without spaces, like PicturesList!, quotes are not required.

Can I use INDEX and MATCH to retrieve data from an entirely separate workbook?

Yes, you can reference ranges in a different workbook. The syntax will include the workbook name enclosed in square brackets, such as =[Data.xlsx]Sheet1!$A$1:$A$50. Keep in mind that for the most reliable performance, the source workbook should be open.

Excel & Data Problems