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.
How to Use VLOOKUP If Cell Contains a Word within Text in Excel: 2 Ways
The 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])
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: How to Return TRUE If Cell Contains Text in Excel
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 show 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.
Read More: How to Highlight Cell If Contains Text in Excel
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])
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.
Read More: Check If Cell Contains Partial Text in Excel
Download Practice Workbook
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!