Fix Excel INDEX MATCH Returning Values From the Wrong Column
Question details
The user needs to correct an INDEX MATCH formula that retrieves data from an unintended column.
- Product
- Microsoft Excel INDEX and MATCH functions
- Device & OS
- not provided
- Scenario
- A worksheet uses one MATCH function to locate a row and another MATCH function to locate a column header for an INDEX formula.
- Observed behavior
- The formula returns a value from the wrong column when the column lookup is not an exact match or when the lookup ranges do not align with the INDEX array.
Identify the cell containing the row lookup value and the cell containing the column-header lookup value. Check that the header labels are unique and that the INDEX array includes the same first and last columns as the header range.
Use exact matches and aligned ranges in the INDEX MATCH formula
Use this method when INDEX MATCH returns a valid result but pulls it from the wrong column.
Both MATCH functions should use 0 as their third argument to require an exact match. The row MATCH range must align with the INDEX array rows, and the column MATCH range must align with the INDEX array columns.
Click the cell that contains the incorrect INDEX MATCH formula, then press F2 or click in the formula bar to edit it.
Add 0 as the final argument in the row and column MATCH functions. For example, use MATCH(D62,Original!$A$2:$A$235,0) for the row and MATCH(A62,Original!$A$2:$L$2,0) for the column header.
Make sure the INDEX array covers the same rows and columns represented by the lookup ranges. For example, enter =INDEX(Original!$A$2:$L$235,MATCH(D62,Original!$A$2:$A$235,0),MATCH(A62,Original!$A$2:$L$2,0)).
Press Enter, then compare the result with the header named in A62 to verify that the formula is returning data from the intended column.
Check column headers and lookup values for hidden differences
Use this method if the formula still selects an unexpected column after the ranges and exact-match arguments are corrected.
Correct INDEX MATCH Column Lookups in WPS Spreadsheets
WPS Spreadsheets supports INDEX and MATCH formulas, so you can use exact-match arguments and aligned ranges to return data from the correct column. Its familiar spreadsheet interface makes it easy to edit formulas, inspect referenced ranges, and work with Excel files.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the INDEX MATCH formula.
- 2. Edit the formula cell: Select the result cell and click the formula bar to review the INDEX array and both MATCH functions.
- 3. Add exact-match arguments: Enter 0 as the third argument of each MATCH function, including the column-header MATCH.
- 4. Check referenced ranges: Click each colored range in the formula and confirm that the INDEX array, row lookup range, and header lookup range start and end consistently.
- 5. Recalculate and verify the result: Press Enter and compare the returned value with the selected row and column header.
Frequently Asked Questions
Why does INDEX MATCH return a value from the wrong column?
The column MATCH may be using approximate matching, or its header range may not line up with the columns in the INDEX array. Add 0 to the column MATCH and ensure both ranges cover the same columns.
Should I use 0 in both MATCH functions?
Yes. Use 0 in the row MATCH and the column MATCH when you need an exact row value and an exact column header match.
How do I align the INDEX range with the column MATCH range?
If INDEX uses Original!$A$2:$L$235, the column MATCH should search the corresponding headers across Original!$A$2:$L$2. Do not search a shorter, shifted, or different set of columns.
What happens if two column headers have the same name?
MATCH returns the position of the first exact matching header. Rename duplicate headers or use unique labels so the formula can identify the intended column.
Can WPS Spreadsheets open and calculate Excel INDEX MATCH formulas?
Yes. WPS Spreadsheets supports INDEX and MATCH formulas and can open, edit, and save compatible .xlsx and .xls workbooks.
