Function Problems

How to Find the Latest Date for a Matching Word Using Excel Formulas

Olivia Miller Oct 12, 2026 870 views

Question details

The user needs an Excel formula to retrieve the latest date from a specific column based on two conditions: matching a specific case number and containing a specific keyword.

How to Find the Latest Date for a Matching Word Using Excel Formulas

Product: Microsoft Excel
Device & OS: not provided
Scenario: Extracting the most recent date for a specific case ID when a corresponding column contains a particular string of text, such as the word 'internal'.
Observed behavior: Requires combining multiple criteria, including partial text matching with wildcards and date evaluation, to correctly return the maximum date value without resulting in errors.

Before you start

Ensure that your date column contains valid Excel serial dates rather than text strings, as mathematical functions like MAXIFS will ignore dates stored as text.

Solution 1Recommended

Use the MAXIFS Function with Wildcards

The MAXIFS function is the most efficient way to find the latest date based on multiple criteria, allowing the use of wildcards for partial text matches.

The MAXIFS function evaluates a range and returns the maximum value that meets all specified criteria. By combining it with the IFERROR function, you can ensure that the cell remains blank if no records match your conditions.

Select the target cell

Click on the cell where you want the latest date to be displayed.

Enter the MAXIFS formula

Input the following formula: =IFERROR(MAXIFS('Data Sheet'!$CH$6:$CH$50000, 'Data Sheet'!$A$6:$A$50000, A2, 'Data Sheet'!$DE$6:$DE$50000, "*internal*"), "")

Adjust the ranges and criteria

Modify the sheet name ('Data Sheet') and the cell ranges to match your exact dataset. Ensure A2 references the cell containing the case number you are searching for.

Format as a date

Press Enter to apply the formula. Right-click the cell, select 'Format Cells', and choose a Date format so the result displays correctly instead of showing a serial number.

Wildcard Usage: Placing asterisks (*) around your keyword (e.g., "*internal*") tells Excel to look for the word anywhere within the cell, regardless of surrounding text.

Solution 2

Troubleshoot Common Formula Errors

If the MAXIFS formula works in a test file but fails or returns blank in your original workbook, the issue is likely related to data formatting or referencing.

Solve Formula Problems Easily

Use WPS Spreadsheet to Handle Complex Formulas and Dates

WPS Spreadsheet provides robust support for advanced data analysis, including functions like MAXIFS. It makes filtering data, extracting dates, and calculating multiple conditions straightforward and error-free.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing your case numbers and date columns.
2. Select your destination cell: Click the cell where you wish to calculate the latest matching date.
3. Input the conditional formula: Type your MAXIFS formula, utilizing asterisks (*) for any partial text matches in your criteria.
4. Apply date formatting: Press Enter, then navigate to the Home tab and apply Date formatting from the number format dropdown.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Advanced function library including MAXIFS, IFERROR, and wildcard supportBuilt-in error checking and easy text-to-date conversion toolsFree, lightweight, and features a familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MAXIFS formula return 0 instead of a date?

The MAXIFS function returns 0 when no records match all your specified criteria. This can happen if the case number doesn't match exactly, the keyword is missing, or the dates in your target column are formatted as text rather than numerical date values.

How do wildcards work in spreadsheet formulas?

Wildcards are special characters used in criteria. The asterisk (*) represents any sequence of characters. For example, "*internal*" will match 'internal error', 'new internal case', or just 'internal'. A question mark (?) represents a single character.

What if I am using an older version of Excel that doesn't support MAXIFS?

If MAXIFS is unavailable, you can use an array formula combining MAX and IF. For example: =MAX(IF((Range1=Condition1)*(ISNUMBER(SEARCH("internal",Range2))), DateRange)). You must press Ctrl+Shift+Enter to evaluate this formula in older versions.

Excel & Data Problems