Users frequently need to use **VLOOKUP **if the cell contains a word within text in Excel. It’s quite straightforward and easy to use **VLOOKUP**. With the function, it’s easy to find the word in the data. I’ll demonstrate how to use **VLOOKUP **if a cell contains a word in a specific text with examples in excel.

## 📁 Download Excel File

In this article, we used the following Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.

## Overview of VLOOKUP Function

The **VLOOKUP **function generally looks for given value within the column in the most left in the given table and then gives value from the matched row row from the given column. It’s in Excel 2003 and all later versions.

Syntax of **VLOOKUP **function:

**=VLOOKUP(lookup value, table array, col index num, [range lookup])**

Here,

**lookup value**** –** It will try to find the value in first column in the table.

**table array**** –** It is the table from where it will retrieve a value.

**column index num**** – **It is the column of the table from which it will retreive a value.

**range lookup- **Approximate match returns **TRUE** and exact match returns **FALSE**.

## Basics of Using VLOOKUP with Wildcards

Let’s imagine we have a dataset where the full names of students are included in one column and their grades are listed in another. And using only the initial name, you wish to search for a certain student’s grades using this data. It is not possible to look up a value like this with a standard **VLOOKUP**.

But if you use a wildcard character like an asterisk, you can get a student’s grades with only an incomplete match. In Excel, you can utilize a total of three wildcard characters:

- Find any amount of characters following a text using the
**asterisk (*)**. You could use “**Micro***” to match the word “**Microsoft**” in a list, for instance. - Put a
**question mark (?)**instead of a character. For instance, you can look up “**Robber**” or “**Rubber**” using “**R?bber**”. - The effects of the previous two letters can be balanced by the
**tilde (~)**. For instance, you can use “**PD~***” in instead of “**PD***” to look for a value.

**📕 Read More: 8 Ways to Return Value If Cell Contains Text in Excel**

## Learn to Use VLOOKUP If Cell Contains a Word Within Text in Excel in These 2 Examples

In Microsoft Excel, we sometimes need to find the data relating to a word or information in a cell from a dataset or table. VLOOKUP lets us detect that word in the table and retrieve data from the cell value containing it. Here we will clear the concept using 2 easy examples.

**Example 1**### 1. Using VLOOKUP to Extract Data from Particular Word

Column B lists the names of candidates for an admission examination, while Column C lists their obtained total marks. We’ll search for a partial similarity or match of the candidate’s name and then determine his or her obtained total marks.

**⬇️⬇️ STEPS ⬇️⬇️**

- Suppose we need to know total marks of
**Robert Hills**, but we just want to search by the first name**Robert**. So we type**Robert**in cell**E10**.

- We put the below formula in cell
**F10**:

**=VLOOKUP(“*”&E10&”*”,B6:C15,2,FALSE)**

- Then we press
**Enter**. We will get the result of the total marks for**Robert**.

`🔨`

Formula Breakdown

`👉`

**B18 **indicates what to look up.

`👉`

**B6:C15 **shows where to look up.

`👉`

Out result is located at column no 2. So we put 2 in the function to tell Excel what column to pick the data from.

`👉`

Finally, we put **FALSE** to suggest in our VLOOKUP example as we want the exact match for **ROBERT**.

**📕 Read More: If Cell Contains Text From List Then Return Value in Excel**

**Example 2**### 2. Extracting Data from Particular Position

Now, the picture below will show a different set of data. In **column B**, there are some random phone numbers from different US states. In **columns B** and **column C**, you can see the area-specific codes and the state names that go with them. From the **B** **Column**, we’ll copy down one telephone number. Then get the name of the state by taking the specific code number from the very first three numbers of the phone number. Finally, **VLOOKUP **will search the range **C4:D10 **for that code.

**⬇️⬇️ STEPS ⬇️⬇️**

- Suppose we want to look up a phone number:
**239-202-6354**, we put the number in cell**B14**.

- Then we put the following formula in cell
**C14**:

**=VLOOKUP(VALUE(LEFT(B14,3)),C5:D11,2,FALSE)**

- Then we press
**Enter**. We will get the result of the state belonging to that phone number.

`🔨`

Formula Breakdown

`👉`

**B14 **indicates what to look up and **3 **indicates which column holds the result or the result to be found from.

`👉`

**C****6:D11 **shows where to look up.

`👉`

Our area code is located at column no **2**. So we put **2** in the function to tell Excel what column to pick the relation from.

`👉`

Finally we put **FALSE** to indicate in our VLOOKUP example as we want an exact match for **ROBERT**.

**📕 Read More: Excel Formula: If Cell Contains Text Then Return Value in Another Cell**

## 📄 Important Notes

`🖊️`

In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

`🖊️`

A practice workbook is given so that you can practice yourself.

`🖊️`

All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

`🖊️`

At the end of the Excel file, there is a sheet where they can practice.

## 📝 Takeaways from This Article

`📌`

The reader will be able to use **VLOOKUP** if a cell’s text contains a word in Excel.

`📌`

Readers can apply **VLOOKUP** to get data based on a value from a certain place in a cell.

`📌`

Finally, the readers will get familiar with Excel’s **VLOOKUP** function and its other usages.

## Conclusion

That concludes today’s session. These are the methods for using **VLOOKUP **if a cell’s text contains a word in Excel. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, **ExcelDen**, the best Excel solutions provider.