Use VLOOKUP if Cell Contains Word within Text in Excel

VLOOKUP formula in Excel can be used in combination with a lot of different formulas and text options to help us with extracting the right data.

In the example below, we will show how to use VLOOKUP if a certain cell contains words within the text.

Use VLOOKUP if Cell Contains Word within Text

For our example, let’s assume that we have a list of cars, including the brand name and the model, and their prices (prices are randomized):

Now suppose that we want to find out the price of a certain car, with the sole information available being the model of the car, but not the manufacturer.

We will put the car model in cell D2, and will put the following VLOOKUP function in cell E2:

Now, as you are probably aware by now, VLOOKUP has four parameters, three of which are mandatory, and one, the last one, that is optional:

1. lookup_value. What we are searching for. In our formula, this is where the trick lies. We will use concatenate option to add any possible text before and after the text that we want to find. Our lookup value will not be just the text located in cell D2, but any text where this value is located.
2. table_array. The range in which we are looking for our value.
3. col_index_number. The column from which we want to derive our value in our range.
4. range_lookup. If we are searching for exact or approximate values. FALSE is an exact match, and TRUE is an approximate match.

With our formula, we will search for the value in our cell D2 combined with any text before and after this value, in the range A1:B26, and will try to find the price of the car from the table.

When we insert the formula above, and we search for the price of the car whose model is Altima, this is the result we will end up with:

Which is the exact value of the Nissan Altima, found in cell B15.

In our formula, we used the asterisk (*), before and after the text located in cell D2, in our case, the model. For Nissan Altima, we did not need the asterisk after the text, because the model is the last word in the cell. But for other models, this does not have to be the case.

For example, if we would input “Cross“ in cell D2, we would get the $80,348 price in cell E2, which is the exact price of the Volkswagen Atlas Cross Sport:

In our particular case, you have to be careful about the fact that some cars have the same model, but are differentiated by additional descriptions, such as Honda Accord and Honda Accord Hybrid.

If we would search for the price by only typing “Accord“ in cell D2, the first value in column A that matches the description will be our result, i.e. the value in cell B12: