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.

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

Leave a Reply

Your email address will not be published.