ExcelInsider Team
When you are working with Excel datasets, you might need to search for information that partially matches another value. That can be finding a product name that contains a particular keyword or matching customer names with typos. This is where an Excel lookup partial text match comes in handy. There are a few easy to apply methods that you can implement to lookup partial text matches in excel.
To lookup partial text match in excel, follow these steps:
➤ Click on any empty cell (eg. F1) and type your search term.
➤ Use this formula on another empty cell (eg. F2) and refer to the cell where you typed the search term: =VLOOKUP(“*” & F1 & “*”, A2:C11, 3, FALSE)
➤ Press Enter and you will get the result with the particular search term.
In this article, we’ll cover multiple methods to perform partial text lookups in Excel, including VLOOKUP, combination of INDEX & MATCH, and IF function.
Using VLOOKUP with Wildcards to Lookup Partial Text Match
This method uses the VLOOKUP function with wildcard characters to find and return values that are related to partial text matches. You can use this when you only know a fragment of the lookup value (like part of a name or word) and want to return related data, such as a department, ID, or location.
We have a dataset that represents a company employee list with their department names. We will perform a partial name match (e.g., any employee name containing “John”) and retrieve the corresponding department.
Steps:
➤ Create a dataset in Excel. We have taken a dataset that has employee names in column A, employee IDs in column B, and departments in column C (from A1 to C11).
➤ Name the cell E1 as Search Name and the cell E2 as the Result Department.
➤ In cell F1 type the search term, such as John. This will be used in the formula.
➤ In another cell (e.g., F2), enter the formula:
=VLOOKUP(“*” & F1 & “*”, A2:C11, 3, FALSE)
Here, * & E2 & * Adds wildcards to search anywhere inside text. And A2:C11 is the Table range.
This tells Excel to look in the first column (A2:A11) for any cell that contains the text typed in F1 (e.g., “John”), and return the corresponding value from the 3rd column, i.e., Department.
➤ Press Enter and see the result.
Note:
➥ The wildcard * matches any number of characters before and after the search text.
➥ VLOOKUP returns only the first match it finds from top to bottom.
➥ This method is case-insensitive.
Combining INDEX & MATCH Functions to Lookup Partial Text Match
The combination of INDEX, MATCH, and SEARCH functions can return the first cell in a list that partially matches a specific keyword. It is useful in Excel datasets where you need to locate a record containing part of a word or phrase. We commonly use it in text heavy columns such as project titles, product names, or job descriptions.
➤ Open up your dataset. We have taken a dataset where we have Office ID in Column A, Office Locations in Column B and Country in Column C. We will Lookup partial text matches in column B.
➤ Name the cell E1 as Search Term and the cell E2 as the Search Output.
➤ In cell F2 type the search term, such as Berlin. This will be used in the formula.
➤ In another cell (e.g., F3), enter the following array formula:
=INDEX(B2:B11, MATCH(TRUE, ISNUMBER(SEARCH(F1, B2:B11)), 0))
➤ In modern Excel (Office 365 or 2021+), this formula works directly. In older versions, press Ctrl + Shift + Enter to enter as an array formula.
➤ Replace “Berlin” in F2 with any other keyword to dynamically update the result. If we type Paris in cell F2, it returns the first partial match “Paris North Division”.
Note:
➥ This formula finds the first match only.
➥ SEARCH is case-insensitive. Use FIND if you need case sensitive results.
➥ The formula works as arrays.
Using IF Function to Return Conditional Output Based on Partial Lookup
IF function can lookup a specific word or phrase appearing anywhere in a cell’s text. We often use this for partial text matching in datasets such as emails, messages, comments, or descriptions. It is very effective in filtering records based on keyword detection in a column of free text entries.
Steps:
➤ Open your Excel workbook. We have taken a dataset where the dataset has a column that contains the full text (e.g., “Customer Message” in Column B). We will apply the formula in column C which is named as “Keyword Match”.
➤ Click on the first empty cell in the “Keyword Match” column (e.g., C2). This is where the result will appear.
➤ Enter the following formula into cell C2 and click Enter.
=IF(ISNUMBER(SEARCH(“refund”, B2)), “Yes”, “No”)
Here,
- SEARCH(“refund”, B2) Looks for the word “refund” in cell B2. It returns a number if found.
- ISNUMBER Converts the result to TRUE if a number is returned (i.e., text was found).
- IF(…, “Yes“, “No”) Returns “Yes” if the keyword is found, otherwise “No”.
➤ Drag the fill handle (small square at the bottom-right of C2) down to apply the formula to the remaining cells in the “Keyword Match” column.
Note:
➥ The SEARCH function is not case-sensitive, so it will match “Refund”, “refund”, or “REFUND”.
➥ For case-sensitive search, replace SEARCH with FIND.
Frequently Asked Questions (FAQs)
Can You Do a VLOOKUP with Partial Text?
Yes, you can use wildcards with VLOOKUP. For example, =VLOOKUP(“*apple*”, A2:B10, 2, FALSE) can match a cell containing the word “apple“.
How Do You Do a Partial Text Match in XLOOKUP?
XLOOKUP also supports wildcards. Example: =XLOOKUP(“*red*”, A2:A10, B2:B10, “Not found”, 0, 2) will find the first value in column A containing “red “.
How to Search For Part of Text in Excel?
Use the SEARCH or FIND function. Example: =ISNUMBER(SEARCH(“code”, A1)) returns TRUE if “code” appears anywhere in A1.
Concluding Words
I have described 3 methods that you can use to lookup partial text matches. Partial text matching in Excel can be accomplished using VLOOKUP with wildcards, INDEX & MATCH combination and IF Function. If you face any issues or you want to share your experience, you can do that through the commence section.
