Fix Excel INDEX and MATCH Returning Incorrect Values for Text Searches
Question details
The user needs a formula to find specific text within another table and return a corresponding value, or leave it blank if no match is found, but the current formula is returning errors.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for partial or exact text contained within cells in a different table using lookup formulas.
- Observed behavior
- The combination of SEARCH, INDEX, and MATCH formulas returns an #N/A error or incorrect results depending on the match type.
Ensure your lookup table and source data do not contain hidden trailing spaces, as these can cause exact-match formulas to fail unexpectedly.
Use an Array-Based SEARCH, INDEX, and MATCH Formula
Combine SEARCH with an array-based INDEX and MATCH pattern to properly identify if a cell contains specific text, wrapped in IFERROR to handle missing matches.
When standard lookup formulas fail to find text strings contained inside other cells, an array formula using ISNUMBER and SEARCH is required. The SEARCH function locates the substring, ISNUMBER converts it to TRUE/FALSE, and MATCH finds the correct row.
Click on the cell where you want the lookup result to be displayed.
Type the formula structure: =IFERROR(INDEX(ReturnRange, MATCH(TRUE, ISNUMBER(SEARCH(SearchRange, TargetCell)), 0)), "") and replace the placeholder ranges with your actual data ranges.
If you are using Excel 2016 or earlier, press Ctrl+Shift+Enter to evaluate it as an array formula. Curly braces {} will appear around the formula if done correctly.
Use Wildcards with INDEX and MATCH
If you are looking up a single text string within a range, you can use asterisk wildcards in the MATCH function instead of an array formula.
Master Complex Lookup Formulas Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup formulas, including dynamic arrays and complex INDEX/MATCH combinations. You can seamlessly perform text searches, handle errors, and analyze data without compatibility issues.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your lookup tables.
- 2. Enter the formula: Select the target cell and type your combined =IFERROR, =INDEX, and =MATCH formula.
- 3. Apply and drag: Press Enter to apply the formula, then drag the fill handle to apply it to the rest of your column. WPS automatically handles the array calculations.
Frequently Asked Questions
Why does my INDEX MATCH formula return #N/A?
An #N/A error means the MATCH function could not find the lookup value. This often happens due to trailing spaces, incorrect match types (ensure the last argument in MATCH is 0 for exact match), or searching for partial text without using wildcards or the SEARCH function.
How do I return a blank cell instead of an error in Excel?
Wrap your lookup formula in the IFERROR function. For example, using =IFERROR(your_formula, "") will return an empty string instead of an error code like #N/A or #VALUE!.
Can I use XLOOKUP instead of INDEX and MATCH for text searches?
Yes. If you are using a newer version of Excel or WPS Spreadsheet, XLOOKUP provides a built-in wildcard match mode (setting match_mode to 2). This simplifies partial text searches significantly without needing combining SEARCH or ISNUMBER functions.
