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:
- 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.
- In an empty cell, type the specific word you want to search for. For this example, let's use cell D1.
- In another empty cell, type the following formula:
=IF(ISNUMBER(SEARCH(D1, A1)), VLOOKUP(D1, A:B, 2, FALSE), "Not Found")
- Replace
D1
with the cell that contains the specific word you want to search for. - Replace
A1
with the cell where you want to start searching for the specific word. - Replace
A:B
with the range of cells where you want to search for the specific word and return the corresponding value. - Replace
2
with the column number that you want to return the corresponding value from. - 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.
- Type "Apple" in cell D1.
- In cell E1, type the following formula:
=IF(ISNUMBER(SEARCH(D1, A1:A4)), VLOOKUP(D1, A1:B4, 2, FALSE), "Not Found")
- Press Enter to apply the formula.
- 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".
Did you find this useful?