Function Problems

How to Fix Incorrect INDEX and MATCH Results in Excel

Maira Mehtab Sep 22, 2026 870 views

Question details

The user needs to correct a complex formula combining INDEX, MATCH, and VLOOKUP that is returning mismatched or incorrect values.

Product: Excel
Device & OS: not provided
Scenario: Extracting and concatenating data across sheets using multi-criteria lookup formulas.
Observed behavior: The formula returns results that do not match the expected outcomes in the target column because of misaligned array ranges in the INDEX and MATCH functions.

Before you start

Verify that your workbook calculation options are set to 'Automatic' and ensure that any multi-criteria array formulas are entered correctly, utilizing Ctrl+Shift+Enter if you are using an older version of Excel.

Solution 1Recommended

Align the Range Dimensions in INDEX and MATCH Functions

Ensure the row dimensions in your INDEX return array perfectly match the row dimensions in your MATCH lookup arrays to prevent offset errors.

When using INDEX and MATCH together, particularly with multiple criteria evaluated as an array, the starting and ending rows of the ranges must be identical. If the INDEX range starts at row 2 but the MATCH range starts at row 1, the result will be offset and return the wrong value.

Identify the return range in your INDEX function

Select the cell containing your formula and check the first argument of the INDEX function. For example, note the exact rows in Backup!$S$2:$S$1000.

Verify the lookup arrays in your MATCH function

Look inside the MATCH function, which evaluates the criteria arrays (e.g., (F2=Backup!$P$2:$P$1000)). Ensure the row numbers (2 to 1000) exactly match the rows used in the INDEX range.

Correct the multi-criteria syntax

Rewrite the lookup array properly using the multiplication syntax for multiple criteria: MATCH(1, (Criteria1=Range1)*(Criteria2=Range2)*(Criteria3=Range3), 0).

Combine and execute the updated formula

Assemble the full string using IFERROR and concatenations as required, such as: =IFERROR(VLOOKUP(B2&H2,Backup!$A:$D,4,FALSE)&"-"&VLOOKUP($C2,Backup!$F:$G,2,FALSE)&"-"&INDEX(Backup!$S$2:$S$1000,MATCH(1,(F2=Backup!$P$2:$P$1000)*(E2=Backup!$Q$2:$Q$1000)*(G2=Backup!$R$2:$R$1000),0))&"-T13-I002",""). Press Enter (or Ctrl+Shift+Enter) to apply.

Tip for Array Formulas: Using exact absolute references (like $S$2:$S$1000) prevents the ranges from shifting when you drag the formula down to other cells in column I.

Easily Process Complex Array Formulas with WPS Spreadsheet

WPS Spreadsheet features a powerful calculation engine that flawlessly supports advanced array functions, including multi-criteria INDEX and MATCH combinations, making data extraction faster and easier.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your problematic lookups.
2. Select the target cell: Click on the cell in the column where you want the combined lookup result to appear.
3. Enter the aligned formula: Paste your corrected formula, ensuring that the INDEX and MATCH ranges share the exact same row boundaries.
4. Apply to the entire column: Press Enter, then double-click the fill handle at the bottom right of the cell to drag the correct formula down the list.

100% compatible with Microsoft Excel formulas, functions, and file formats (.xlsx).Built-in formula auditing tools to quickly identify and fix mismatched range errors.Lightweight application that smoothly handles massive datasets with complex lookups.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX MATCH formula return #N/A?

The #N/A error usually occurs when the exact lookup value does not exist in the lookup array, or there is a data type mismatch, such as comparing a number formatted as text against an actual numerical value.

Do the INDEX and MATCH arrays need to be the same size?

Yes. If your INDEX range spans 999 rows (e.g., row 2 to 1000), your MATCH lookup range must also span exactly 999 rows. If they differ, the formula may return incorrect offset values or throw a #REF! error.

How do I use multiple criteria with INDEX MATCH?

You can evaluate multiple conditions by structuring your formula as an array calculation: INDEX(return_range, MATCH(1, (criteria1=range1)*(criteria2=range2), 0)). The multiplication acts as an 'AND' operator, returning 1 only when all conditions are met.

Excel & Data Problems