Find the Cell Address Containing a Text Match in Excel
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.

- 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.
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.
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.
Click the cell where the returned address should appear.
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.
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.

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. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the workbook containing the search range.
- 2. Select the result cell: Click the cell where you want the address to appear.
- 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. Confirm the formula: Press Enter. If your WPS Spreadsheets version uses legacy array entry, press Ctrl+Shift+Enter instead.

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.




