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

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

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

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

**🔨 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**.