5 Quick Ways to Lookup Partial Text Match in Excel (5 Quick Methods)

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.


Method 1

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)

VLOOKUP function to lookup a partial text match in Excel

πŸ”¨ 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.


Method 2

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))

INDEX-MATCH function to lookup a partial text match in Excel

πŸ”¨ 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.


Method 3

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.


Method 4

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)

XLOOKUP, ISNUMBER and SEARCH functions to lookup a partial text match in Excel

πŸ”¨ 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.


Method 5

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)))

FILTER, ISNUMBER and SEARCH functions to lookup a partial text match in Excel

πŸ”¨ 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.

(Visited 98 times, 1 visits today)
Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo