How to Fix Excel INDEX and MATCH Returning the Wrong Row
Question details
The user needs to correct an INDEX and MATCH formula that is pulling data from the wrong row when referencing a mix of structured table columns and entire worksheet columns.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up and returning data using the INDEX and MATCH combination across regular cell ranges and structured tables.
- Observed behavior
- The formula returns a mismatched value because the starting row of a structured table column (which excludes headers) differs from a full column reference, causing a row offset.
Review your formula to identify exactly which ranges the INDEX and MATCH functions are referencing. Pay close attention to whether you are mixing full column references (like A:A) with structured table columns (like Table1[Email]).
Match the Starting Rows of Lookup and Return Ranges
Ensure that both your INDEX range and MATCH range begin at the exact same row number to prevent offset errors.
The most common cause of INDEX and MATCH returning the wrong row is mismatched array sizes. If your INDEX array starts at row 1 (e.g., Sheet1!S:S) but your MATCH array starts at row 2 (e.g., Table1[Email]), the matched position will be off by one row.
Click on the cell containing the incorrect INDEX and MATCH formula to view it in the formula bar.
Look at the first argument in the INDEX function (e.g., Sheet1!A2:A100). Note the starting row number.
Look at the second argument in the MATCH function (e.g., Sheet1!B2:B100 or Table1[Email]).
Adjust both ranges so they start and end at the same row number (e.g., both starting at row 2, or both referencing the entire column like A:A and B:B). Press Enter to apply.
Add an Offset to the MATCH Function
If you must mix a full column INDEX with a structured table MATCH, manually adjust the returned row number by adding the number of header rows.
Include the Header in the Structured Reference
Modify the structured table reference to include the header row so it perfectly aligns with an INDEX function starting at row 1.
Fix Formula Errors Easily with WPS Spreadsheet
WPS Office offers a highly compatible spreadsheet tool that seamlessly handles advanced formulas like INDEX and MATCH, structured tables, and complex lookups. It provides clear, color-coded formula syntax highlighting to help you spot range mismatches instantly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the formula error.
- 2. Inspect the formula: Double-click the cell containing your INDEX MATCH formula.
- 3. Check color highlights: Use the color-coded boxes that appear around your data ranges to visually verify if the INDEX array and MATCH array align.
- 4. Adjust easily: Drag the borders of the highlighted boxes to align the starting rows, then press Enter.
Frequently Asked Questions
Why does INDEX MATCH return #N/A even though the lookup value exists?
This usually happens due to mismatched data types (e.g., a number stored as text in one column and as a number in the other) or hidden trailing spaces in the cells. You can use the TRIM() function or ensure both columns have the exact same text/number formatting.
Can I use entire column references like A:A with structured tables?
Yes, but it is not recommended to mix them in the same formula. If your INDEX function uses A:A (starting at row 1) and your MATCH uses Table1[Column] (starting at row 2), the returned row numbers will be offset by the table's starting position.
Is XLOOKUP a better alternative to INDEX MATCH?
Yes, XLOOKUP is a modern, simpler alternative that combines the functionality of both functions without the risk of range offset errors. It requires you to select parallel arrays for lookup and return, naturally preventing the mismatched row issue.
How do I reference an entire structured table column including the header?
To include the header in a structured reference, use the syntax TableName[[#All],[ColumnName]]. This helps align the range if your INDEX function spans the entire column starting from row 1.
