Partial match with VLOOKUP

There are times when you need Excel to retrieve data based on partial information. To solve the problem, you can use aformula together with wildcard characters - the asterisk (*) and question mark (?).

How to perform a partial match with VLOOKUP?

To get the information about the products and sales made by the salesman, whose name contains “ale”, from the table as shown above, you should know how to use the wildcard – the asterisk (*), which matches any number of characters. In the case above, you can put “ale” between two asterisks ("*ale*") to match all the names that contain ale. After we grasp the usage of wildcards, we can use the VLOOKUP function to apply a partial match.

Tips for other cases: Match the names that start with ale, use "ale*"; Match the names that end with ale, use "*ale"; Match the names that has ale as their 2-4th letters, use "?ale*". Note that one question mark matches one character.

Generic syntax

=VLOOKUP(lookup_value,table_array,column_num,FALSE)

• lookup_value: The value VLOOKUP used to apply the partial match in the table_array. Here refers to the name contains “ale”. The lookup_value should be in the leftmost column of the table_array.
• table_array: The range of cells to look from.
• column_num: The column number in the table_array containing the value to return.

To get the information about the products and sales made by the salesman, whose name contains “ale”, please copy or enter the formulas below in the cells G7 and G8, and press Enter to get the results:

Cell G7 =VLOOKUP("*ale*",B5:D11,2,FALSE)
Cell G8 =VLOOKUP("*ale*",B5:D11,3,FALSE)

Or, use a cell reference to make the formulas dynamic:

Cell G7 =VLOOKUP("*"&G5&"*",B5:D11,2,FALSE)
Cell G8 =VLOOKUP("*"&G5&"*",B5:D11,3,FALSE)

√ Note: To stick a cell reference and text together, you must use ampersand (&) to join them. And the text should be enclosed in double quotes.

Explanation of the formula

Here we use the formula below as an example:

=VLOOKUP("*"&G5&"*",B5:D11,2,FALSE)

• The lookup value "*"&G5&"*" ("*ale*") can match any names that contain the string “ale”, no matter what position the string is in the names.
• The range_lookup FALSE asks the VLOOKUP function to find the exact match.
• The VLOOKUP function will then return the value that matches "*"&G5&"*" and is in the 2nd column of the range B5:D11, which is Ruler, Glue, Notebook.

Related functions

Excel VLOOKUP function

The Excel VLOOKUP function searches for a value by matching on the first column of a table and returns the corresponding value from a certain column in the same row.

Related Formulas

Lookup a value containing specific text with wildcards

To find the first match that contains certain text string in a range in Excel, you can use an INDEX and MATCH formula with wildcard characters - the asterisk (*) and question mark (?).

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.

Case-Sensitive Lookup

You may know that you can combine the INDEX and MATCH functions, or use the VLOOKUP function to lookup values in Excel. However, the lookups aren’t case-sensitive. So, in order to perform a case-sensitive match, you should take advantage of the EXACT and CHOOSE 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.