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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Find Duplicates Using VLOOKUP
Excel: How to Use VLOOKUP to Return All Matches
Excel: How to Use VLOOKUP to Return Multiple Columns