How to Fix Excel INDEX and MATCH When the Lookup Value Is Not Found
Question details
The user's INDEX and MATCH formula fails to return the expected result when looking up calculated text values like 'Current' or 'NA' in an unsorted dataset.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using an INDEX and MATCH formula combination to retrieve a date based on a text criteria within a lookup column.
- Observed behavior
- The formula does not return the expected result or appears broken unless the lookup value is manually typed into the cell as plain text.
Ensure that your lookup values do not contain hidden leading or trailing spaces, as these will cause exact match formulas to fail.
Use Exact Match (0) in the MATCH Function
Because the source data is unsorted, you must force MATCH to look for an exact match to prevent incorrect results or errors.
By default, if you do not specify a match type, the MATCH function assumes your data is sorted in ascending order and looks for an approximate match. For unsorted text data, this behavior will often return wrong values or fail entirely.
Click on the cell displaying the error and look at the formula bar to find the MATCH segment of your formula (e.g., MATCH(D2, Weeks!A2:A18)).
Add a comma and the number 0 at the very end of the MATCH function's parenthesis to specify an exact match. It should look like this: MATCH(D2, Weeks!A2:A18, 0).
If your INDEX array is only one column wide, you do not need a second MATCH function to identify the column number. Your final corrected formula should look similar to: =INDEX(Weeks!B2:B18, MATCH(D2, Weeks!A2:A18, 0)).
Clean Calculated Text Formatting
Formulas might fail if calculated text values contain invisible spaces or formatting discrepancies compared to the source column.
Fix Advanced Formulas Easily with WPS Spreadsheet
WPS Spreadsheet provides a powerful, user-friendly environment for working with advanced lookup formulas like INDEX and MATCH, offering flawless compatibility with Microsoft Excel files.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx or .xls file containing the broken INDEX and MATCH formulas.
- 2. Use Formula Auditing tools: Navigate to the Formulas tab and select 'Evaluate Formula'. This tool lets you step through your INDEX and MATCH logic to spot exactly where the lookup fails.
- 3. Apply the exact match fix: Edit the formula directly in the formula bar, append the ', 0' argument to your MATCH function, and press Enter to calculate the correct date.
Frequently Asked Questions
Why does my MATCH function return #N/A when the value is clearly in the list?
This usually happens due to formatting differences between the lookup value and the source array. Hidden spaces, trailing spaces, non-breaking spaces, or numbers formatted as text are common culprits. Using the TRIM() function or ensuring exact formatting can resolve this.
When should I use 1, 0, or -1 in the MATCH function?
Use 0 for an exact match, which is essential when looking up text strings or unsorted data. Use 1 for the largest value less than or equal to the lookup value (requires data sorted in ascending order). Use -1 for the smallest value greater than or equal to the lookup value (requires data sorted in descending order).
Can I use XLOOKUP instead of INDEX and MATCH?
Yes, if your spreadsheet software supports it. XLOOKUP is a modern alternative that defaults to an exact match automatically and handles unsorted arrays perfectly, making it much easier to write and troubleshoot than INDEX and MATCH combinations.
