Function Problems

Find the Cell Address Containing a Text Match in Excel

Maira MehtabMaira Mehtab Nov 13, 2026 869 views

Question details

The user needs to search a rectangular Excel range for a text value such as C59 and return the address of the cell containing that value.

Find the Cell Address Containing a Text Match in Excel
Product
Microsoft Excel / WPS Spreadsheets
Device & OS
not provided
Scenario
A rectangular range contains strings that resemble cell addresses, and the user wants a formula to find a specific text match and report its cell address.
Observed behavior
The goal is to return the address of the cell containing the matching text, for example D87.
Before you start

Open the workbook and confirm the search range (for example C84:G94) and the lookup cell (for example E96). If your version uses legacy array formulas, be ready to press Ctrl+Shift+Enter after typing the formula.

Solution 1Recommended

Use an Array Formula to Locate the Matching Cell Address

Use this solution when you need a single formula to search a rectangular range and return the address of the cell that matches the lookup text.

This formula checks each cell in the search range against the lookup value. If the range contains exactly one match, it returns the row and column of that cell and converts them to an address with ADDRESS.

If multiple matches may exist, verify the count with COUNTIF before relying on the returned address.

1
Select the result cell

Click the cell where the returned address should appear.

2
Enter the array formula

Type =ADDRESS(MAX((C84:G94=E96)*ROW(C84:G94)),MAX((C84:G94=E96)*COLUMN(C84:G94))) and replace C84:G94 with your search range and E96 with the lookup cell.

3
Confirm the formula

In Microsoft 365 or WPS Office dynamic arrays, press Enter. In older Excel versions, press Ctrl+Shift+Enter to enter it as an array formula.

Use an Array Formula to Locate the Matching Cell Address
Address updates automatically: The formula returns the cell address, such as $D$87, and recalculates when the range or lookup value changes.
Use WPS Spreadsheets

Find a Matching Cell Address in WPS Spreadsheets

You can use the same ADDRESS, ROW, COLUMN, and MAX formula in WPS Spreadsheets to locate the cell containing a text match. WPS Spreadsheets supports the Excel functions and array formula entry used in this task, so your workbook can be opened and edited without changing the logic.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the workbook containing the search range.
  2. 2. Select the result cell: Click the cell where you want the address to appear.
  3. 3. Enter the lookup formula: Type =ADDRESS(MAX((C84:G94=E96)*ROW(C84:G94)),MAX((C84:G94=E96)*COLUMN(C84:G94))) and replace C84:G94 and E96 with your actual range and lookup cell.
  4. 4. Confirm the formula: Press Enter. If your WPS Spreadsheets version uses legacy array entry, press Ctrl+Shift+Enter instead.
Uses the same Excel formula syntax, so existing workbooks work unchangedFull compatibility with .xlsx, .xls, and .csv filesFree to download and lightweight for everyday spreadsheet tasksSupports array formulas and Excel functions such as ADDRESS, ROW, COLUMN, and MAXFamiliar ribbon interface for a smoother transition from Microsoft Excel
QA img-9

Frequently Asked Questions

Why does the formula need Ctrl+Shift+Enter in older Excel versions?

In legacy Excel, an array formula must be entered with Ctrl+Shift+Enter so the comparison across a range is evaluated element by element. In Microsoft 365 and newer WPS Spreadsheets versions, dynamic array formulas usually only need Enter.

What should I do if the formula returns #VALUE! or an incorrect address?

Check that the search range is valid, make sure the lookup cell is a single cell, confirm that the range contains exactly one match, and in older Excel versions enter the formula with Ctrl+Shift+Enter.

Can I use this formula when the search range has blank cells or duplicate values?

Yes, but blank cells are treated as non-matches. If multiple cells match, the formula returns the last matching row and column, which may not be the intended result, so verify the match count with COUNTIF first.

Does this formula work in WPS Spreadsheets?

Yes, WPS Spreadsheets supports ADDRESS, ROW, COLUMN, and MAX, and it can open the workbook without changing the formula. For legacy array entry, press Ctrl+Shift+Enter.

How do I adapt the formula for a different range or lookup cell?

Replace C84:G94 with your actual search range and E96 with your lookup cell throughout the formula. Keep the references consistent in both the row and column parts of the formula.