How to Use VLOOKUP if Cell Contains Word within Text in Excel

Using VLOOKUP if a cell contains a word within the text in Excel can be done by combining VLOOKUP with other functions like IF, ISNUMBER, and SEARCH. Follow the instructions below to achieve this:

1. Assuming you have a list of data in column A and you want to look up a specific word and return the corresponding value from column B.
2. In an empty cell, type the specific word you want to search for. For this example, let's use cell D1.
3. In another empty cell, type the following formula:

=IF(ISNUMBER(SEARCH(D1, A1)), VLOOKUP(D1, A:B, 2, FALSE), "Not Found")

4. Replace D1 with the cell that contains the specific word you want to search for.
5. Replace A1 with the cell where you want to start searching for the specific word.
6. Replace A:B with the range of cells where you want to search for the specific word and return the corresponding value.
7. Replace 2 with the column number that you want to return the corresponding value from.
8. Press Enter to apply the formula.

Example

Let's assume we have the following data in column A and column B:

A         B
1  Apple     10
2  Banana    20
3  Orange    30
4  Grapes    40

We want to search for the word "Apple" and return the corresponding value from column B.

1. Type "Apple" in cell D1.
2. In cell E1, type the following formula:

=IF(ISNUMBER(SEARCH(D1, A1:A4)), VLOOKUP(D1, A1:B4, 2, FALSE), "Not Found")

3. Press Enter to apply the formula.
4. The formula will return the value 10 in cell E1, which is the corresponding value for "Apple" in column B. If the word "Apple" is not found in the range A1:A4, the formula will return "Not Found".