How to Find Text Within a Cell and Match a Month Date in Excel
Question details
The user needs to retrieve a value from a specific column based on two conditions: another column must contain a date from January, and a third column must contain a specific word (e.g., "Discover") within its text.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for a specific record in a large dataset using multiple criteria, including extracting the month from a date and executing a partial text match.
- Observed behavior
- Requires an array formula that correctly filters for both the month of January and the presence of the word "Discover" anywhere in the targeted cell to return the corresponding value.
Ensure your dataset is organized in clear columns without merged cells, and verify that your date column is formatted as actual dates rather than plain text so the MONTH function can read them.
Use INDEX and MATCH with Multiple Criteria Array Formula
Combine INDEX, MATCH, MONTH, ISNUMBER, and SEARCH to look up a value based on a specific month and a partial text match.
This formula uses array logic to evaluate multiple conditions simultaneously. The MONTH function checks the date column for January (month 1), while SEARCH looks for the specific substring anywhere in the text column. ISNUMBER converts the SEARCH result into TRUE or FALSE so it can be multiplied with the date condition to find the matching row.
Click on the cell where you want the extracted value from column E to appear.
Type the following formula into the formula bar: =INDEX('My ✓ing'!E:E,MATCH(1,(MONTH('My ✓ing'!B:B)=1)*ISNUMBER(SEARCH("Discover",'My ✓ing'!C:C)),0))
If you are using Excel 365, Excel for the web, or WPS Spreadsheet, simply press Enter. If you are using Excel 2019 or earlier, you must press Ctrl + Shift + Enter to evaluate it as an array formula.
Easily Manage Advanced Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic arrays and advanced lookup functions like INDEX, MATCH, and SEARCH without requiring complicated Ctrl+Shift+Enter keystrokes. You can effortlessly apply this multiple criteria lookup in your daily workflow.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
- 2. Input the lookup formula: Click on your target cell and input: =INDEX(E2:E1000, MATCH(1, (MONTH(B2:B1000)=1) * ISNUMBER(SEARCH("Discover", C2:C1000)), 0))
- 3. Press Enter to calculate: Simply press Enter. WPS Spreadsheet will automatically calculate the dynamic array and return the correct value.
Frequently Asked Questions
Why does my array formula return a #VALUE! error?
This often occurs in older versions of Excel if you press Enter instead of Ctrl+Shift+Enter. It also happens if the date range contains text that the MONTH function cannot process. Ensure your date column only contains valid dates.
Can I use FIND instead of SEARCH for exact case matching?
Yes. The SEARCH function is case-insensitive, meaning it will find "discover" or "Discover". If you need a strict case-sensitive match, replace the SEARCH function with the FIND function in your formula.
Why is my formula returning an #N/A error?
The #N/A error means that the MATCH function couldn't find a single row where both conditions (January date and "Discover" in the text) are met. Double-check your data for trailing spaces or hidden characters that might prevent a match.
