Function Problems

How to Find Partial Matches and Return Row Numbers in Excel

Guest Writer Oct 8, 2026 869 views

Question details

The user needs a formula to search for values from one column within another column using partial or wildcard matches, and return the corresponding row numbers.

How to Find Partial Matches and Return Row Numbers in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Setting up a dynamic formula to search for partial string matches across a data range, which can be dragged down a column to evaluate multiple search criteria.
Observed behavior: The user requires an automated way to retrieve either the first matching row position or all matching row positions for a specific partial text value.

Before you start

Ensure your lookup data ranges are locked with absolute references (such as $A$2:$A$9) so that the formula evaluates the exact same area when copied down to other rows.

Solution 1Recommended

Use MATCH with Wildcards for the First Partial Match

This method uses the MATCH function combined with asterisk wildcards to locate the first cell containing a partial text match.

The MATCH function typically looks for exact matches. By concatenating asterisks before and after the lookup value, you instruct Excel to find the lookup string anywhere within the target cells. Wrapping this in IFERROR ensures cells remain clean if no match is found.

Select the target cell

Click on the cell where you want the row number to appear (for example, cell C2).

Input the MATCH formula

Type the formula: =IFERROR(MATCH("*"&B2&"*", $A$2:$A$9, 0), ""). In this formula, B2 is the value you are searching for, and $A$2:$A$9 is the range being searched.

Apply to the entire column

Press Enter to get the row position. Then, click and drag the fill handle at the bottom-right of the cell to copy the formula down for B3, B4, and so on.

Use MATCH with Wildcards for the First Partial Match

Row Position vs. Worksheet Row: The MATCH function returns the relative position within the range ($A$2:$A$9). If the match is in A2, it returns 1. To get the actual worksheet row number, add the starting row offset to the formula (e.g., +1).

Solution 2

Use TEXTJOIN, FILTER, and SEARCH for All Matches

This advanced method is ideal for Microsoft 365 users who need to return all row positions where a partial match occurs, separated by commas.

WPS Spreadsheet

Effortlessly Search and Match Data in WPS Spreadsheet

WPS Spreadsheet offers full support for advanced search functions like MATCH, SEARCH, and wildcards, making it easy to find partial matches and extract row numbers perfectly.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
2. Enter the formula: Click the desired output cell and type your wildcard MATCH formula, just as you would in Excel.
3. Drag to fill: Hover over the bottom-right corner of the cell and drag the fill handle down to apply the formula across your dataset.

Fully compatible with Microsoft Excel formulas, wildcards, and array functionsAdvanced data analysis and lookup tools included for freeLightweight application with intuitive tabbed document management

Frequently Asked Questions

Why does my MATCH formula return #N/A even when a partial match exists?

This usually happens if you forget to use wildcard characters ("*") around your search reference, or if the match type is not set to 0 (exact match). Ensure your formula looks like MATCH("*"&B1&"*", A:A, 0).

Are these partial match formulas case-sensitive?

No, both the MATCH and SEARCH functions are not case-sensitive. If you need a case-sensitive partial match, you will need to use the FIND function combined with array formulas instead of SEARCH or MATCH.

How can I return the value from an adjacent column instead of the row number?

You can wrap your MATCH formula inside an INDEX function. For example, to return a value from column C, use =INDEX($C$2:$C$9, MATCH("*"&B2&"*", $A$2:$A$9, 0)).

Excel & Data Problems