Function Problems

Find Tutor Initials and Flag Duplicate Cell Text in Excel

Rana GarciaRana Garcia Nov 14, 2026 869 views

Question details

Find a tutor's initials in a row on one worksheet, return the first matching cell text to another sheet, and flag duplicate entries.

Find Tutor Initials and Flag Duplicate Cell Text in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook has tutor initials on a Tutors sheet and source entries on a QV sheet.
Observed behavior
The desired result is to display the first source cell containing the initials and highlight it when the same cell text occurs more than once in the source row.
Before you start

Identify the cell containing the initials, the source row, and the destination cell. The example uses Tutors!G3 for initials, QV!A11:Z11 for source text, and Tutors!D4 for the result.

Solution 1Recommended

Return the First Match and Highlight Duplicate Source Text

Use INDEX and MATCH to retrieve the first text cell containing the initials, then use a helper cell and conditional formatting to flag repeated cell text.

This example matches the initials anywhere within a text cell and returns only the first match. The duplicate check counts exact repetitions of the returned text, not all different cells containing the same initials.

1
Enter the initials

On the Tutors sheet, enter the tutor's initials in G3. Replace this reference in the formulas if your initials are stored elsewhere.

2
Return the matching text

In Tutors!D4, enter =IF(G$3="","",IFERROR(INDEX(QV!$A11:$Z11,1,MATCH("*"&G$3&"*",QV!$A11:$Z11,0)),"")) . Change both QV ranges to cover the source row you want to search.

3
Count duplicate source entries

In Tutors!E4, enter =IF(D4="","",SUMPRODUCT(--(QV!$A11:$Z11=D4))) . This counts cells in the source row whose full text equals the result in D4.

4
Create the highlight rule

Select D4, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =$E4>1, choose a fill or font format, and select OK.

5
Extend the formulas if needed

Copy D4 and E4 down for additional source rows, then extend the conditional formatting rule's Applies to range. Check that each copied formula points to the intended QV row.

Return the First Match and Highlight Duplicate Source Text
Checking for multiple initials matches: If you want to flag any multiple cells containing the initials, even when their full text differs, use =IF(G$3="","",COUNTIF(QV!$A11:$Z11,"*"&G$3&"*")) in E4 instead.
Work with tutor data in WPS

Find and Flag Tutor Entries in WPS Spreadsheets

Use formulas and conditional formatting in WPS Spreadsheets to retrieve a matching tutor entry and highlight repeated source text.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and locate the Tutors and QV worksheets.
  2. 2. Enter the initials: On Tutors, enter the initials in G3 and identify the source range on QV.
  3. 3. Find the first match: In Tutors!D4, enter =IF(G$3="","",IFERROR(INDEX(QV!$A11:$Z11,1,MATCH("*"&G$3&"*",QV!$A11:$Z11,0)),"")) and adjust the references for your workbook.
  4. 4. Count repeated text: In Tutors!E4, enter =IF(D4="","",SUMPRODUCT(--(QV!$A11:$Z11=D4))) to count exact repetitions in the source row.
  5. 5. Highlight duplicates: Select D4 and use Home > Conditional Formatting to create a formula-based rule with =$E4>1. Choose a highlight format and apply the rule.
Find initials within text across worksheets.Highlight repeated entries with conditional formatting.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface for formulas and formatting.
microsoft office alternative - wps office

Frequently Asked Questions

Does the lookup return every cell containing the initials?

No. MATCH returns the position of the first matching cell in QV!A11:Z11, and INDEX returns that cell's text. Additional matches are not listed.

How can I find initials only at the end of a cell?

In the MATCH formula, replace "*"&G$3&"*" with "*"&G$3. This searches for text ending with the initials in G3.

Why is the result cell blank?

Check that Tutors!G3 contains initials and that QV!A11:Z11 covers the correct source cells. The IFERROR formula returns a blank when it finds no match.

Can I highlight multiple matches even if their text differs?

Yes. In E4, use =IF(G$3="","",COUNTIF(QV!$A11:$Z11,"*"&G$3&"*")) and keep the conditional formatting rule =$E4>1. This counts cells containing the initials rather than exact repetitions of D4.