## Locate first partial match with wildcards

To get the position of the first partial match that contains specific text string in a range in Excel, you can use a MATCH formula with wildcard characters - the asterisk (*) and question mark (?).

#### How to get the position of the first partial match with wildcards?

To get the position of the first match containing “China” and the first match that has the letter “e” as its 5th letter from the table as shown above, you should first know how to use the two wildcards – the asterisk (*), which matches any number of characters; and question mark (?), which matches only one character. For example, *ice could mean nice, police, ice, 9\$ice, etc.; and mo?? could mean more, mode, mo1\$, mood, etc. After we grasp the usage of wildcards, we can incorporate them into a MATCH formula to get the position we want.

#### Generic syntax

=MATCH(lookup_value,lookup_array,0)

• lookup_value: The value MATCH used to return the position of the first match that meets the given condition in the lookup_array. Here refers to the first match containing “China” and the first match that has “e” as its 5th letter.
• lookup_array: The range of cells to get the position from.
• match_type 0: Forces MATCH to find the first value that exactly equals to the lookup_value.

To get the first match containing “China” and the first match that has the letter “e” as its 5th letter, please copy or enter the formulas below in the cells E6 and E9, and press Enter to get the results:

Cell E6 =MATCH("*china*",B5:B11,0)
Cell E7 =MATCH("????e*",B5:B11,0)

Or, use cell references to make the formulas dynamic:

Cell E6 =MATCH("*"&E5&"*",B5:B11,0)
Cell E7 =MATCH("????"&E8&"*",B5:B11,0)

√ Note: To stick a cell reference and text together, you must add an ampersand (&) in between. And the text should be enclosed in double quotes.

#### Explanation of the formulas

=MATCH("*"&E5&"*",B5:B11,0)

• The lookup value "*"&E5&"*" ("*china*") can match any text string that contains the string “China”, no matter what position the string is in the word or sentence.
• The match_type 0 asks the MATCH function to find the exact match.
• The MATCH function will then return the position of the first match containing the string “China” in the range B5:B11, which is 4.

=MATCH(("????"&E8&"*",B5:B11,0)

• The lookup value "????"&E8&"*" ("????e*") can match any text string that has “e” as its 5th character, as long as the text string contains 5 or more characters.
• The match_type 0 asks the MATCH function to find the exact match.
• The MATCH function will then return the position of the first text string that has “e” as its 5th character in the range B5:B11, which is 2.

#### Related functions

Excel MATCH function

The Excel MATCH function searches for a specific value in a range of cells, and returns the relative position of the value.

#### Related Formulas

Lookup the first partial match number

There are cases that you need to get the position of the first partial match that contains specific number in a range of numeric values in Excel. In this case, a MATCH and TEXT formula that incorporates asterisk (*), the wildcard that matches any number of characters, will do you a favor. And if you also need to know the exact value at that position, you can add the INDEX function to the formula.

Lookup closest match

To look for the closest match of a lookup value in a numeric dataset in Excel, you can use the INDEX, MATCH, ABS and MIN functions together.

Lookup closest match value with multiple criteria

In some cases, you may need to lookup the closest or approximate match value based on more than one criteria. With the combination of INDEX, MATCH and IF functions, you can quickly get it done in Excel.

Two-way approximate match with multiple criteria

In this tutorial, we will talk about how to look for an approximate match based on multiple criteria listed both in columns and rows in an Excel spreadsheet, with the help of INDEX, MATCH, and IF functions.

### The Best Office Productivity Tools

#### Kutools for Excel - Helps You To Stand Out From Crowd

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ... Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup... Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ... Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

#### Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

• One second to switch between dozens of open documents!
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Increases your productivity by 50% when viewing and editing multiple documents.
• Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.