In Microsoft Excel, sometimes we have to look for different types of data related to a specific word or information within the text in a cell from the dataset or a table. With the help of the VLOOKUP function, we can easily find that word from the table and extract data related to the cell value containing that word.

**Download Practice Workbook**

You can download the Excel workbook that weâ€™ve used to prepare this article.

**2 Useful Methods to Apply VLOOKUP If Cell Contains a Word within Text in Excel**

**VLOOKUP** function is generally used to look for a value in the leftmost column of a table and the function then returns a value in the same row from a column you specify. The generic formula of this VLOOKUP function is:

**=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])**

You can have a detailed overview **here** of how this **VLOOKUP** function works.

**1. VLOOKUP to Find Data from Text Containing a Word in Excel**

In the following picture,** Column B** contains the model names of several random chipsets and in **Column C**, there are names of the smartphone models which are using the mentioned chipsets. What weâ€™ll do here is look for a partial match of a chipset model and then weâ€™ll extract which device uses this specified chipset.

For example, we want to know the device model of a smartphone that uses the **Snapdragon** chipset. In **Column B**, the name Snapdragon is present with a model name but weâ€™ll look for this data with a partial match by mentioning** â€˜snapdragonâ€™** only.

So, in the output **Cell C14**, the related formula to find the smartphone model name that uses the specified chipset will be:

`=VLOOKUP("*"&C13&"*",B4:C11,2,FALSE)`

After pressing **Enter**, the function will return **Xiaomi Mi 11 Pro**. So, this specific device uses the chipset of **Snapdragon** that lies in **Cell B6** with its model number.

**2. VLOOKUP to Extract Data Based on a Value from a Particular Position in the Cell**

Now weâ€™ll have a different dataset in the picture below. **Column B** lies with some random telephone numbers in different states of the USA.** Columns D and E** are showing the area codes and related state names respectively. Weâ€™ll copy a phone number from **Column B** and then find out the state name by extracting the code from the left 3 digits of the telephone number. The **VLOOKUP** function will look for that extracted code in the table array of** D4:E10**.

In the output **Cell C13**, the required formula to find the state name from the phone number stated in **Cell B13** will be:

`=VLOOKUP(VALUE(LEFT(B13,3)),D4:E10,2,FALSE)`

After pressing **Enter**, the function will return the state name- **New York**. So, the stated telephone number with the specific code at the beginning in **Cell B13** is registered for New York state.

**An Alternative to VLOOKUP to Find Data Based on a Word within Text**

A suitable alternative to the **VLOOKUP** function is the** XLOOKUP** function. The XLOOKUP function is the combination of **VLOOKUP and HLOOKUP** functions. It extracts data based on the inputs of the lookup array and returns the array. The generic formula of this function is as follows:

**=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])**

You can absorb a detailed overview of this function by clicking **here**.

Based on our first dataset in the first method, if we opt to use the **XLOOKUP** function then the required formula in the output **Cell C14** should look like this:

`=XLOOKUP("*"&C13&"*",B4:B11,C4:C11,"Not Found",2)`

After pressing **Enter**, the function will return the similar result obtained previously.

In this function, the** fourth argument** contains a customized message that will be shown if the lookup value is not found in the table. The **fifth argument (match_mode)** has been defined by **â€˜2â€™** which denotes **wildcard** match based on the input in the first argument.

**Concluding Words**

I hope the methods mentioned above to extract data under specified criteria with the** VLOOKUP** function will now provoke you to apply them in your necessary Excel tasks. If you have any questions or feedback, please let us know through comments. Or you can check out our other articles related to Excel functions on this website.

is it possible to do something like put “A14, Snapdragon” as the lookup word in one cell and the function would reply list of all possible lookup results “iPhone 12, Xiaomi Mi 11 Pro” also in one cell.