Excel Formula to Find Exact Text and Return a Specific Month
Question details
The user needs an Excel formula to search a specific range for an exact text string, evaluate the corresponding numeric value in another column, and output either "January" or "February" based on whether the number is greater than zero.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Categorizing data based on specific conditions where an exact text match dictates which numeric value is evaluated to determine a corresponding month output.
- Observed behavior
- The user aims to output "January" if the target value is strictly greater than zero, and "February" if the target value is zero, negative, or blank.
Ensure that your data columns are aligned properly (e.g., A1:A6 and C1:C6) and that your search text does not contain hidden trailing spaces, as these can cause exact-match formula functions to return errors.
Use INDEX, MATCH, and IF Functions Together
Combine the INDEX and MATCH functions to precisely locate your target data, and wrap them in an IF statement to return the appropriate month.
The MATCH function finds the row number where the exact text is located. The INDEX function then uses that row number to fetch the corresponding value from your target column. Finally, the IF function evaluates this fetched value to decide which month to return.
Click on cell F2 (or any other cell where you want the result to appear).
Type the following formula into the formula bar: =IF(INDEX(A1:C6,MATCH("ABC Co",A1:A6,0),3)>0,"January","February")
Press the Enter key. The cell will evaluate the corresponding value in column C and output the correct month name.
Use VLOOKUP and IF Functions
An alternative method utilizing the standard VLOOKUP function nested inside an IF statement to achieve the same result.
Master Complex Formulas Easily with WPS Spreadsheet
WPS Office offers a robust and user-friendly spreadsheet application that natively supports advanced data evaluation functions like INDEX, MATCH, and IF. You can efficiently manage complex datasets and execute precise conditional logic effortlessly.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open your data file containing the names and values.
- 2. Insert the combined formula: Select your destination cell, click on the Formula bar, and type your IF, INDEX, and MATCH formula.
- 3. Get instant results: Hit Enter. WPS Spreadsheet will instantly calculate the logic and display the correct month.
Frequently Asked Questions
What happens if the exact text is not found in the specified range?
If your target text (e.g., "ABC Co") is not found within the A1:A6 range, the MATCH or VLOOKUP function will return an #N/A error, causing the entire cell to display #N/A. To handle this, you can wrap your formula in an IFERROR function, like: =IFERROR(your_formula, "Not Found").
Why does the formula return 'February' if the cell in column C is completely blank?
In most spreadsheet software, including Excel and WPS Office, a blank cell referenced by INDEX or VLOOKUP is evaluated as a numeric zero (0). Because the IF statement specifically checks if the value is strictly greater than zero (>0), a zero defaults to the "false" condition, therefore outputting "February".
Can I expand this formula to check for multiple different months based on the value?
Yes. If you have more than two conditions, you can nest multiple IF statements together (e.g., =IF(value>10, "March", IF(value>0, "January", "February"))), or utilize the IFS function if you are using a newer version of the software.
