You can Lookup in a column for a word in a sentence in Google Sheets and return corresponding values. The text can be comma separated in a cell, in the beginning, middle or end of a sentence etc. When I say this, instantly the name of one function may flash in your mind. It’s Vloolup or Index functions, right? But it’s very complicated to do such a vertical lookup in a column for a word in Google Sheets. Then what is the best option?

No doubt, we can depend on Google Sheets the versatile Query function. I am sure that you can quickly learn this. I believe the below example is enough for you to learn this awesome trick to lookup a text in Sentence similar to Vertical Lookup.

How to Vertical Lookup in a Column for a Word in Google Sheets [Not Single Word, But Word Within Sentence]

In the below dummy data, I want to lookup column A for a word or name “Kent kelly” and returns related values from the same row. As you can see, the lookup search key “Kent kelly” is in Cell A3, but with another name separated by a comma.

See the versatility of the Query formula. Instead of Vlookup, it takes the role of Vertical lookup.

The above Query formula lookup Column A for the text “Kent kelly” and returns the corresponding values from Column B and C, similar to Vlookup. How?

I’ve used Query String Comparison Operator called Contains in this example.

How to Use “Contains” String Comparison Operator in Google Sheets Query

Here is the formula which I’ve used in the above example.

=query(A2:C5,"select B,C where A contains 'Kent' ")

Here in this formula, A2: C5 is the data range. The formula returns the values from Column B and Column C if Column A contains the word “Kent”.

If there are multiple matches it would return those rows too. But you can limit that with the Limit clause in Query. Here are that tips.

Similar: Three Different Ways to Limit Number of Rows in Google Sheets Query

From the formula itself, you can understand how to use Contains String Comparison operator in Google Sheets Query. But you should know the following points before using this formula.

1. Query function is case sensitive.

2. If the name is “Kent Kelly” the formula treats this as a different text. So you should use the full name “Kent Kelly” in the formula. When you use “Kent” only in the formula, it can find matching words in the column like “Kent kelly” also. If you want to overcome this case sensitivity, you may want to use Upper or Lower functions within Query.

In concise, for Vertical Lookup in Google Sheets, Vlookup is not the only solution. You can use Query or Index Match also.

You May Also Like: Index Match – Better Alternative to Vlookup and Hlookup in Google Sheets

But for this particular example, i.e., Vertical Lookup in a Column for a Word in Google Sheets, Query is the one and only solution. Below is a detailed tutorial on the Contain operator.

Detailed Reading: CONTAINS Substring Match in Google Sheets Query for Partial Match

We are ready with some inspirational new year picture quotes.

Wish you a very happy and prosperous new year 2018. Get Inspired.