Table of Contents
The VLOOKUP function in Excel can be used to search for a specific word within a larger text in a cell. This function allows users to easily locate and retrieve data from a table by specifying the search criteria. By using the VLOOKUP function, users can quickly find and extract the desired information from a cell containing a larger text, saving time and effort. This feature is particularly useful for organizing and analyzing large datasets, as it allows for efficient data retrieval based on specific keywords within a cell.
Excel: VLOOKUP if Cell Contains Word within Text
You can use the following VLOOKUP formula to look up cells that contain a specific word within a text string:
=VLOOKUP("*"&A11&"*",A2:B8,2,FALSE)
This particular formula will look for the cell in the range A2:B8 that contains the word in cell A11 and return the corresponding value in the second column.
The following example shows how to use this formula in practice.
Example: VLOOKUP if Cell Contains Word within Text
Suppose we have the following dataset that shows the total sales made at various stores:
Now suppose we would like to look up the store that contains the word “Axel” and return the corresponding sales value.
We can use the following formula to do so:
=VLOOKUP("*"&A11&"*",A2:B8,2,FALSE)The following screenshot shows how to use this formula in practice:
This VLOOKUP formula is able to look up “Axel” in the list of store names and return the correct sales value of 30.
Note that this formula is also case-insensitive.
This means that if we use “axel” then the formula will still be able to find this text in the list of store names:
The formula still returns the correct sales value of 30.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can Excel’s VLOOKUP function be used if a cell contains a specific word within a larger text?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-excels-vlookup-function-be-used-if-a-cell-contains-a-specific-word-within-a-larger-text/
stats writer. "How can Excel’s VLOOKUP function be used if a cell contains a specific word within a larger text?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-excels-vlookup-function-be-used-if-a-cell-contains-a-specific-word-within-a-larger-text/.
stats writer. "How can Excel’s VLOOKUP function be used if a cell contains a specific word within a larger text?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-excels-vlookup-function-be-used-if-a-cell-contains-a-specific-word-within-a-larger-text/.
stats writer (2024) 'How can Excel’s VLOOKUP function be used if a cell contains a specific word within a larger text?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-excels-vlookup-function-be-used-if-a-cell-contains-a-specific-word-within-a-larger-text/.
[1] stats writer, "How can Excel’s VLOOKUP function be used if a cell contains a specific word within a larger text?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can Excel’s VLOOKUP function be used if a cell contains a specific word within a larger text?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.