Formula Errors

How to Fix #REF! Errors in INDEX and MATCH Formulas

Partner Editor Sep 28, 2026 869 views

Question details

The user needs to resolve a #REF! error that appears when copying an INDEX and MATCH formula down multiple rows.

How to Fix #REF! Errors in INDEX and MATCH Formulas

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Transforming data from a vertical to a horizontal layout using INDEX and MATCH on source data that contains merged cells.
Observed behavior: The formula successfully retrieves data for the first three rows but returns a #REF! error when dragged down to subsequent rows.

Before you start

Verify your source data layout and make note of any merged cells, as they frequently disrupt the structural references required by advanced lookup formulas.

Solution 1Recommended

Unmerge Source Cells and Lock Formula Ranges

Merged cells disrupt row and column counting in INDEX and MATCH arrays. Unmerging them and locking your references is the most reliable way to prevent out-of-bounds #REF! errors.

When transforming vertical data to horizontal layouts, INDEX and MATCH require perfectly symmetrical and predictable arrays. Merged cells cause the spreadsheet program to lose track of exact cell references, leading to out-of-bounds #REF! errors when the formula shifts down the spreadsheet.

Select the source data

Highlight the entire source data range that contains the merged cells disrupting your lookup.

Unmerge all cells

Navigate to the Home tab and click 'Merge & Center' (or just 'Unmerge Cells') to separate all merged blocks.

Fill blank cells

Fill in the newly unmerged blank cells with the appropriate repeated values so that each row contains the correct lookup data.

Apply absolute references

In your INDEX and MATCH formula, highlight the array ranges and press the F4 key to lock them with dollar signs (e.g., $A$1:$B$100). This prevents the ranges from shifting downwards when you drag the formula.

Drag the formula

Click the fill handle at the bottom right of your formula cell and drag it down to apply the fixed formula to the remaining rows.

Unmerge Source Cells and Lock Formula Ranges

Tip: Using Absolute References: Absolute references (like $A$1) are crucial when copying formulas. Without them, your lookup array moves down row-by-row with your formula, eventually causing it to search outside the valid data range.

Solution 2

Test the Formula on a Clean, Smaller Dataset

Isolate the issue by testing your logic on a smaller, unmerged dataset in a new workbook to ensure the core formula structure is correct.

Resolve Formula Errors Seamlessly

Fix INDEX and MATCH Errors Easily with WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive environment for writing complex lookup formulas. Its intelligent error-tracing capabilities and robust formula evaluation make resolving #REF! errors fast and effortless.

1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing the #REF! errors.
2. Unmerge problematic data: Select the source range, go to the Home tab, and click to unmerge the cells to fix structural issues.
3. Use Formula Evaluation: Navigate to the Formulas tab and click the Evaluate Formula tool to step through your INDEX and MATCH calculation to see exactly where the reference breaks.
4. Lock and drag: Lock your data arrays with absolute references (F4) in the formula bar, then drag the fill handle to copy the formula without errors.

Seamless compatibility with Microsoft Excel (.xlsx) formatsBuilt-in Evaluate Formula tool to trace #REF! errors step-by-stepEasily identify and unmerge problematic cells with one clickFree, lightweight, and fast performance for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX MATCH formula say #REF! when dragged down?

A #REF! error occurs when a cell reference is invalid. When dragging down a formula, relative references automatically shift downwards. If the lookup array isn't locked with absolute references (e.g., $A$1:$B$10), the formula will eventually search outside the existing spreadsheet boundaries or attempt to reference deleted/merged cells.

Can I use INDEX and MATCH with merged cells?

It is highly discouraged. Merged cells disrupt the predictable row and column counts that the INDEX and MATCH functions rely on to locate data. It is always best practice to unmerge the cells and repeat the necessary data values in each individual cell.

How do I lock my formula ranges to prevent reference errors?

Highlight the range reference in your formula bar (such as A1:C100) and press the F4 key on your keyboard. This action adds dollar signs to the column letters and row numbers ($A$1:$C$100), creating an absolute reference that will not shift when you copy or drag the formula to other rows.

What is causing the formula to work for the first three rows only?

This usually indicates that the lookup range was not made absolute. For the first few rows, the shifting reference array might still happen to overlap with your source data. By the fourth row, the shifted array likely skips past the target lookup value or hits a structurally invalid merged cell, triggering the #REF! error.

Excel & Data Problems