Today I will be showing how to **VLOOKUP** partial text from a single cell in **Excel**. As we know, the **VLOOKUP **function is one of the most important and widely used functions of Excel. We can use the **VLOOKUP function** of Excel to look for any data related to any value from a range of cells in Excel. Today I will be showing how you can use the **VLOOKUP** **function** to look for a partial text in a range of cells.

**Download Practice Workbook**

**2 Easy Methods to Use VLOOKUP to Find Partial Text from a Single Cell**

In this section, I will demonstrate 2 straightforward methods to use **the VLOOKUP function** to search partial text from a single cell. To illustrate that, here we have a data set with the **Names**, **Authors,** and the **Book Types** of some books from a bookshop named **Martinez Bookstore**.

Our objective today is to use the **VLOOKUP** function of Excel to look for a partial text from this data set. So let’s check out the 1st method.

**1. Use of Asterisk Symbol (*) to VLOOKUP Partial Text**

Here we will try to find out whether there is any book related to the **Second World War** in the data set. If there is, then we will find who the author is. That means we need a book with the text **“Second World War” **in the data set. There may be any number of characters before the text **“Second World War”**, or after the text. We do not know exactly. In this situation, the** Wildcard** Character **Asterisk** (*****) will come in handy here. You can use one **Asterisk** symbol for any number of unknown characters in the **VLOOKUP** formula. To apply the **Asterisk** symbol, follow the steps below.

**Steps:**

- On cell
**F5**, write down the following formula.

`=VLOOKUP("*Second World War*",B5:D15,2,FALSE)`

- Here, there is one book with the words
**“Second World War”**on the list and the author of that book is**Winston Churchill**. Thus the result we got is**Winston Churchill**.

**How Does the Formula Work?**

**VLOOKUP(“*Second World War*”,B5:D15,2,FALSE)**

Here we have used the **Asterisk(*)** Symbol on both sides of the text **“Second World War” **because we were looking for a book name with unknown characters on both sides of the text. The VLOOKUP function searched this piece of text (**Second World War)** in the 1st column (**Name of the Book**) of the array of **B5:D5 **and returns the 2nd column data (Author) which is **Winston Churchill**.

- To look for a text with unknown characters on a specific side of the text, use the
**Asterisk(*)**symbol only on that side. - For example, to look for the author of a book with the text
**“Harry Potter”**in the beginning, you can use the following formula:

`=VLOOKUP("Harry Potter*",B5:D15,2,FALSE)`

**Asterisk(*)**at the end].

- Similarly, you can also look for a book with the text
**“Freedom”**at the end. This time use the formula with the**Asterisk (*)**symbol in the beginning:

`=VLOOKUP("*Freedom",B5:D15,2,FALSE)`

- On the other hand, if you have the piece of text to be searched in another cell, use the
**Ampersand (&)**symbol to join the**Asterisk (*)**symbol with the text. - For example, if the text
**“Second World War”**is in cell**B18**, use the formula:

`=VLOOKUP("*"&B18&"*",B5:D15,2,FALSE)`

- Consequently, we can see that we have got the same name,
**Winston Churchill**in the result.

**Read More: Wildcard with VLOOKUP in Excel (6 Examples)**

**2. Use of Question Mark (?) to VLOOKUP Partial Text**

The previous method is usually applied when the number of characters before or after the searched text is unknown. However, when we know the number of characters, we can use the Question Mark(?) instead of the Asterisk symbol(*). To illustrate the 2nd method, here we’ve another data set with the **Code Names**, **Names,** and **Salaries** of some employees of a company named Mercury Group.

As we can see from the dataset above, all the code names consist of 6 characters. Therefore, to find out an employee with **“JEN”** at the beginning of the code name, exactly 3 more unknown characters will remain. We can use the **Question Mark (?)** in this case. To apply for a particular code name such as “**JEN”, **follow the steps below.

**Steps:**

- To find out an employee with a code name starting with
**“JEN”,**apply the following formula.

`=VLOOKUP("JEN???",B5:D16,2,FALSE)`

**Question Marks (???)**for three unknown characters.]

- Consequently, we can see that there is one employee with the code name starting with
**“JEN”**, Jennifer Marlo. - Similarly, we can find out an employee with the code name ending with the letters
**“INA”**by applying the following formula.

`=VLOOKUP("???INA",B5:D16,2,FALSE)`

- In addition to that, if you have the partial text in a
**cell**, use the**Ampersand (&)**symbol to join the partial text with the**Question Marks (?).** - For example, if you have
**“INA”**in cell**B19**, the formula to find the employee with the code name**“INA”**will be:

`=VLOOKUP("???"&B19,B5:D16,2,FALSE)`

**Read More: How to Use VLOOKUP for Partial Match in Excel (4 Suitable Ways) **

**Conclusion**

Finally, this is the end of this article. Hopefully, by using these methods, you can utilize the **VLOOKUP** function of Excel to look for any partial text in any data set. Moreover, if you have any questions, feel free to ask us.

## Further Readings

**How to Vlookup Partial Match for First 5 Characters in Excel****Highlight Partial Text in Excel Cell (9 Methods)****How to Use VLOOKUP Function with Exact Match in Excel****Excel VLOOKUP for Partial Match in Table Array (3 Examples)****Conditional Formatting for Partial Text Match in Excel (9 Examples)****How to Use Named Range in Excel VLOOKUP Function****[Fixed!] Excel VLOOKUP Partial Match Not Working**