Formula Errors

Excel Formula for Partial Text Matching Across Worksheets

Ayan MasoodAyan Masood Nov 22, 2026 868 views

Question details

Use part of a department name on one worksheet to return a corresponding category on another worksheet.

Match Partial Text Across Worksheets in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the output cell

Open the DATA worksheet and select cell A5, or select the cell where you want the result.

2
Enter the formula

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.

3
Apply it to other rows

Drag the fill handle from DATA!A5 down the column. The relative reference to INPUT!C5 changes for each row.

4
Check the results

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.

Rule order matters: If a department name contains more than one search term, EMERGENCY takes priority, followed by OB and then INTERNAL. Reorder the nested conditions if you need different priority.
WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
  2. 2. Check the source: Select the INPUT sheet tab and confirm that the department name is in C5.
  3. 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. 4. Fill and save: Drag the fill handle down to classify more rows, then select File > Save or Save As to save the workbook.
Match department text across worksheets without case sensitivityOpen and edit Microsoft Excel XLS and XLSX filesUse familiar spreadsheet formulas and worksheet tabsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

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.