Tanzim
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.
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:
- 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
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:
- 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.
👉 C6: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.
