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.

**Read More:** **Check If Cell Contains Partial Text in Excel (5 Ways)**

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

**Related Content:** **Excel If Cell Contains Text Then Return Value (8 Easy Ways)**

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

Hello, Thomas!

You can apply the following formula in cell C14 to do that.

`=TEXTJOIN(", ",,(VLOOKUP(LEFT(C13,(FIND(",",C13,1)-1))&"*",B5:C11,2)), (VLOOKUP(TRIM(RIGHT(C13, LEN(C13)-FIND(",",C13,1))&"*"), B5:C11,2)))`

**Notes:

1. If multiple results are associated with the lookup value, the formula will return the first result only.

2. You must enter at least 2 lookup values separated by comma. Otherwise, you may see #VALUE!

Regards

Shamim

No, it is not possible cause the VLOOKUP function in Excel cannot be used to enter multiple criteria as a lookup value, so you won’t be able to get your desired result.

Is it possible to do a lookup with the search key being something along the lines of AMZN234567 and the range has just AMZN? im trying to easily categorize expenses, and i want to create a rule where it looks in the expense description for certain terms in the description, such as finding a partial match of AMZN in the AMZN234567 description, and bring in the budget category mapping, something like ‘office supplies’ which will sit in another data table. How can i do that? it sounds like i would need a fuzzy lookup, but am unsure. Thanks!

Hello BEN!

I think the solution to your problem is already solved in method 1 of this article. To search for partial match, you have the wild cards (*) that have been shown in method 1.

If the cell C13 contains the value of the search item. Then use the following formula:

=VLOOKUP(“”&C13&””,$B$4:$C$11,2,FALSE)

I hope, your problem will be solved in this way. If not, please share the Excel file and send us the problem with little more explanation in an email at

[email protected]Thank You!