Excel Formula for Partial Text Matching Across Worksheets
Question details
Use part of a department name on one worksheet to return a corresponding category on another worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Department names are entered on an INPUT worksheet, and results are needed on a DATA worksheet.
- Observed behavior
- The desired result is a category based on whether the department name contains EMERGENCY, OB, or INTERNAL, with a default result for other names.
Confirm that the source sheet is named INPUT and that the department name is in cell C5. Adjust the sheet name and cell references in the formula if your workbook uses different locations.
Use nested IF and SEARCH to match department names
Use this formula when you have a small set of partial-text rules and want a result on another worksheet.
SEARCH looks for text without distinguishing uppercase from lowercase. ISNUMBER returns TRUE when SEARCH finds a match.
The formula checks the rules in order and returns the first match. Because OB matches those letters anywhere in the text, it can also match an unintended word.
Open the DATA worksheet and select cell A5, or select the cell where you want the result.
In the formula bar, enter =IF(ISNUMBER(SEARCH("EMERGENCY",INPUT!C5)),"BH ED MLP",IF(ISNUMBER(SEARCH("OB",INPUT!C5)),"BH CLIN OB PROVIDER",IF(ISNUMBER(SEARCH("INTERNAL",INPUT!C5)),"BH CLIN SRVCS","ON-SITE EMPLOYEE"))) and press Enter.
Drag the fill handle from DATA!A5 down the column. The relative reference to INPUT!C5 changes for each row.
Enter or review department names in INPUT!C5 and the following rows, then verify their results on DATA. Names without a match, including blank cells, return ON-SITE EMPLOYEE.
Match department names across sheets in WPS Spreadsheets
Use the same nested IF and SEARCH formula in WPS Spreadsheets to classify partial department names from another worksheet.
- 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
- 2. Check the source: Select the INPUT sheet tab and confirm that the department name is in C5.
- 3. Enter the formula: Select DATA!A5 and enter =IF(ISNUMBER(SEARCH("EMERGENCY",INPUT!C5)),"BH ED MLP",IF(ISNUMBER(SEARCH("OB",INPUT!C5)),"BH CLIN OB PROVIDER",IF(ISNUMBER(SEARCH("INTERNAL",INPUT!C5)),"BH CLIN SRVCS","ON-SITE EMPLOYEE"))). Press Enter.
- 4. Fill and save: Drag the fill handle down to classify more rows, then select File > Save or Save As to save the workbook.

Frequently Asked Questions
Does SEARCH distinguish uppercase and lowercase letters?
No. SEARCH("EMERGENCY",INPUT!C5) finds the text regardless of capitalization. Use FIND instead of SEARCH if the match must be case-sensitive.
What happens if a department name contains two search terms?
The nested IF formula returns the first matching result. In the example, EMERGENCY is checked before OB, and OB is checked before INTERNAL.
Why did a name containing an unrelated word match OB?
SEARCH("OB",INPUT!C5) finds those letters anywhere in the cell, including inside a longer word. Use a more specific search term or a rule based on how your department names are formatted.
How do I reference a sheet name that contains spaces?
Enclose the sheet name in single quotes. For example, use 'Input Data'!C5 in place of INPUT!C5 in the formula.
Can I add more department categories?
Yes. Add another nested IF condition before the default result, following the same ISNUMBER(SEARCH(...)) pattern. For a large list of categories, consider a lookup table with wildcard criteria.




