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:

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:

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:

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.

You May Also Like to Explore

How to Use VLOOKUP Function in Excel (2 Examples)

How to Get the Max Value Using VLOOKUP in Excel

VLOOKUP Formula to Compare Two Columns in Different Sheets!

Using VLOOKUP with IF Condition in Excel (5 Real-Life Examples)

How to Find Duplicate Values in Excel using VLOOKUP

The post How to Use VLOOKUP If Cell Contains a Word within Text in Excel appeared first on ExcelDemy.