In this guide, we’ll discuss how to use the VLOOKUP function if a cell contains a word within a text in Excel.
Excel VLOOKUP function helps to find and locate specific information inside any row or column. You can consider it a data search engine that scans through columns, helping you retrieve related data from the same row.
Whether you’re an Excel novice or a seasoned user, understanding VLOOKUP is like unlocking the ultimate data organization tool, and it also makes handling and analyzing data far more efficient. You can find the VLOOKUP function in Excel 2003 and all later versions.
The generic formula for VLOOKUP is as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments:
- lookup_value: This is the value you want to search for in the first column of your data table. It’s like the query or key you’re using to find related information.
- table_array: This is the range of cells that contains your data. The VLOOKUP function looks for the lookup_value in the first column of this range. The data you’re working with is organized in this table.
- col_index_num: This argument specifies the column number in the table_array from which you want to retrieve the result. If, for example, your desired information is in the third column of your table, you would put 3 here.
- [range_lookup]: This is an optional argument. If you want an exact match, you can set this to FALSE. If you’re okay with an approximate match, you can either set this to TRUE or leave it blank. An approximate match is useful when your data is sorted in ascending order.
So let’s look at three examples of how you can use VLOOKUP if the cell contains a word within the text.
Example 1 – Basic VLOOKUP for an exact match
In our first example, we’ll start with a basic application of VLOOKUP for an exact match. Imagine you have a list of products, and you want to find the price of the product containing the word “Strawberry.”
For this purpose, you can use the formula below:
=VLOOKUP(“*” & A9 & “*”, A2:B6, 2, FALSE)
Result
This VLOOKUP formula searches for the product mentioned in cell A9 within the list and returns the corresponding price. For instance, in the above screenshot, A9 contains “Strawberry,” hence the formula would return the price of $2.50 as shown in the screenshot below:
Example 2: Case-Insensitive Search
In our second example, we’ll explore a case-insensitive search scenario using the VLOOKUP function. Let’s say you have a list of software titles, and in cell A9, you have the term “photoshop.”
Our objective is to identify the type of software associated with this title, which in this case is “Graphics Software.” The VLOOKUP function will be configured to perform a case-insensitive search, ensuring accurate results even if the title varies in case, such as “PHOTOSHOP” or “PhotoShop.”
This example highlights the flexibility of VLOOKUP in handling different variations of text, making it a versatile tool for your data lookup needs in Excel.
The following formula will be used here:
=VLOOKUP(“*” & A9& “*”, A2:B6, 2, FALSE)
Result
- If A9 contains “Photoshop,” the formula should return “Graphics Software.”
- If A9 contains “photoshop,” “PHOTOSHOP,” or any other case variation, it will still return “Graphics Software” due to the case-insensitive search.
Example 3 – Partial Match with Wildcards
In our third example, we’ll explore the versatility of the VLOOKUP function by addressing partial matches using wildcards.
Let’s consider a scenario where we want to find the price of a product containing the word “Blue.” The VLOOKUP function will be configured to accommodate partial matches, allowing us to retrieve the corresponding price for any product with “Blue” in its name.
This example showcases the power of using wildcards with VLOOKUP, offering a flexible approach to searching and extracting data based on partial text matches in Excel.
You will use the following formula:
=VLOOKUP(“*” & A12 & “*”, A2:B6, 2, FALSE)
Result
This VLOOKUP formula searches for the term mentioned in cell A12 within the list and returns the corresponding price. For instance, in the above screenshot, A12 contains “Blue,” hence the formula would return the price of $3.00.
Conclusion
So there you have it – three different ways of using VLOOKUP if the cell contains a word within text. For more Excel guides, be sure to check back in with us soon.
