Priti
In Excel, we can look up values by using different functions and logical comparisons. But most of the time these functions require an exact match and return a #N/A error if there is no exact match. Therefore, we showed how to lookup text matches partially combining different functions. So, this article will demonstrate 5 formulas to lookup a partial text match in Excel.
📁 Download Excel File
Download the file used for the demonstration from the link below.
Learn to Lookup Partial Text Match in Excel with These 5 Methods
Firstly, let us get acquainted with our dataset used throughout the article as an example. We know the names of the students as well as their test scores in math, physics, and chemistry. We now intend to look at the grades of a student in a particular course. Therefore, we will use this dataset to illustrate 5 straightforward Excel formulas for looking up partial text matches.
1. Using VLOOKUP Function
VLOOKUP function finds something in a table or a range by row. This method will use this function to lookup a partial text match in Excel. Let us see the steps now.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell C14 and enter the formula.
=VLOOKUP(C13,B6:E11,2,FALSE)
🔨 Formula Breakdown
Here, the formula inserted in cell C14 is:
VLOOKUP(C13,B6:E11,2,FALSE)
👉 In this function, C13 contains the value that we have to look up.
👉 B6:E11 is the array where the function will search for the lookup value.
👉 Then, 2 shows that we have to look up in the second column of the array and FALSE represents that it needs to be an exact match.
- Finally, we will have the result for partial match.
2. Applying INDEX-MATCH Functions
This method will combine INDEX and MATCH to lookup partial text match in Excel. INDEX function extracts a value and returns it from a table, range, or reference to a value. On the other hand, the MATCH function looks for a specific item within a set of cells, finds it, and then returns its position. Let us go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell C15 and insert the formula. We will get the grade for Emma in Math.
=INDEX(C6:E11,MATCH(C13,B6:B11,0),MATCH(C14,C5:E5,0))
🔨 Formula Breakdown
Here, the formula we inserted in cell C15 is:
INDEX(C6:E11,MATCH(C13,B6:B11,0),MATCH(C14,C5:E5,0))
👉 MATCH(C13,B6:B11,0), here C13 contains the lookup value that we want to match within the lookup array B6:B11, and 0 says that we want an exact match. So, from this part, we will have 2 as output since “Emm” matches with “Emma”, the second item in the range.
👉 Similarly, MATCH(C14,C5:E5,0) will look up the exact match for “Math” in the array C5:E5 and return 3.
👉 Lastly, the INDEX function looks up in the range C6:E11 and returns the intersecting values of the second-row and third column of the range, and displays the desired output.
3. Utilising XLOOKUP with the Wildcard Character
XLOOKUP function looks for a thing in an array. Then it returns the first match it found and if it can not find any match then it returns the closest match. This method will use the XLOOKUP function with wildcard characters to lookup a partial match. A wildcard is a particular type of character that can replace any other character in Microsoft Excel. So, here we used wildcard characters for partial lookup. Now, let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell C15 and insert the formula and we will have the marks in Chemistry for Olivia.
=XLOOKUP(“*”&C13,B6:B11,D6:D11,,2)
🔨 Formula Breakdown
Here, the formula we inserted in cell C15 is:
XLOOKUP(“*”&C13,B6:B11,D6:D11,,2)
👉 “*”&C13, here is the lookup value that we want to match within the lookup array B6:B11, and D6:D11 is the return array means the value to be returned for the matched value.
👉 Then, the gap says a blank to return if there is no match and lastly, 2 is for a wildcard match.
4. Implying XLOOKUP, ISNUMBER & SEARCH Functions
The XLOOKUP function looks for a thing in an array and returns the first match it found and if it can not find any match then it returns the closest match and ISNUMBER function searches for a number value and returns True or False. On the other hand, SEARCH function searches the text string in another text string and returns the starting position of the first text string from the first character of the second text string. This method will use XLOOKUP, ISNUMBER and SEARCH functions to lookup a partial match in Excel. Now, let us demonstrate the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Insert the formula in cell C15 and we will have the marks in Physics for Emily.
=XLOOKUP(TRUE,ISNUMBER(SEARCH(C13,B6:B11)),C6:C11)
🔨 Formula Breakdown
Here, the formula inserted in C15 is:
XLOOKUP(TRUE,ISNUMBER(SEARCH(C13,B6:B11)),C6:C11)
👉 Here, this part SEARCH(C13,B6:B11) searches the text string in the cell C13 in the range B6:B11. Since the text string in C13 matches with Emily so it returns 1.
👉 Now, ISNUMBER returns TRUE if the number is 1 otherwise returns FALSE.
👉 Lastly, XLOOKUP matches TRUE with the output from the ISNUMBER function and returns the result from the return array C6:C11.
5. Combining FILTER, ISNUMBER & SEARCH Functions
The FILTER function filters from a range or array according to the defined criteria and the ISNUMBER function checks a number value and returns True or False. On the other hand, the SEARCH function searches the text string in another text string and returns the number of the starting position of the first text string from the first character of the second text string. This method will use ISNUMBER, FILTER, and SEARCH functions in the lookup a partial match in Excel. Now, let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Insert the formula in cell C15 and we will have the marks in Chemistry for Matt.
=FILTER(D6:D11,ISNUMBER(SEARCH(C13,B6:B11)))
🔨 Formula Breakdown
Here, the formula inserted in C15 is:
FILTER(D6:D11,ISNUMBER(SEARCH(C13,B6:B11)))
👉 Here, this part SEARCH(C13,B6:B11) searches the text string in the cell C13 in the range B6:B11.
👉 Now, the ISNUMBER function returns TRUE if the number is 1 otherwise returns FALSE.
👉 Lastly, the FILTER function filters from the range D6:D11 depending on the output from the ISNUMBER function and return the result.
How to Check for Cell That Contains Partial Text with IF in Excel
Now, we will demonstrate how to use IF and COUNTIF functions to find out partial text with a wildcard character in Excel. The IF function compares a value with the expected value logically and the COUNTIF function counts the number of cells based on a criterion. Let us go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Insert the formula in cell C15 and we will have the marks in Chemistry for Matt.
=IF(COUNTIF(C13,”*E*”),C6,””)
🔨 Formula Breakdown
Here, the formula inserted in C15 is:
IF(COUNTIF(C13,”*E*”),C6,””)
👉 Here, COUNTIF(C13,”*E*”) counts the number of cells containing “*E*” in cell C13.
👉 Now, the IF function returns cell C6 if the logical test from the COUNTIF function is TRUE and blank if FALSE.
📄 Important Notes
🖊️ Here, we have used an exact match for the MATCH function. If the value does not match exactly, it will show a #N/A error. So, make sure that the values match precisely.
🖊️ In the INDEX function, if we keep the column number empty or enter 0, it will return the value from all the columns.
📝 Takeaways from This Article
📌 This article demonstrated five formulas to lookup partial text match in Excel.
📌 In the first method, we showed the VLOOKUP function to lookup partially text matches in Excel.
📌 Then, we demonstrated a formula that applies the INDEX-MATCH functions to look for a partial match.
📌 Next, we showed the XLOOKUP function with a wildcard character to lookup partial matches in Excel.
📌 After that, we demonstrated a formula that applies XLOOKUP, ISNUMBER and SEARCH functions to lookup half text matches in Excel.
📌 Lastly, we showed FILTER, ISNUMBER and SEARCH functions to lookup matches in Excel.
Conclusion
This article has demonstrated five functions to lookup a partial text match in excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.
For more guides like this, visit Excelden.com.
