Are you tired of searching for a specific word within a sentence in Google Sheets? Look no further than the versatile Query function! While Vlookup and Index functions might come to mind, they can be complicated to use for vertical lookup in a column for a word in Google Sheets. So, what’s the best option? Let me show you how to master the Query function and perform a text lookup in a sentence, similar to vertical lookup.
How to Perform Vertical Lookup in Google Sheets
Let’s take a look at an example. In the dummy data below, I want to find a word or name, “Kent Kelly,” in column A and return related values from the same row. The search key “Kent Kelly” appears in cell A3, but with another name separated by a comma.
Impressive, isn’t it? The Query formula proves to be a worthy substitute for Vlookup, providing the same functionality for vertical lookup.
The formula I’m about to share uses a Query String Comparison Operator called “Contains.” Here it is:
=query(A2:C5,"select B,C where A contains 'Kent' ")In this formula, A2:C5 represents the data range. The formula retrieves values from columns B and C if column A contains the word “Kent.” It can even handle multiple matches by returning all relevant rows. If you want to limit the number of rows returned, you can use the Limit clause in the Query function.
Before you dive into using this formula, keep the following points in mind:
- The Query function is case sensitive.
- If the name is “Kent Kelly,” the formula treats it as a different text. To avoid this case sensitivity, use the full name “Kent Kelly” in the formula. Alternatively, you can use the Upper or Lower functions within Query.
In summary, Vlookup is not the only solution for vertical lookup in Google Sheets. You can also use Query or Index Match. However, for this particular scenario of vertical lookup in a column for a word in Google Sheets, Query is the one and only solution.
For a more detailed tutorial on using the “Contains” operator, check out this article on Crawlan.com.
Now that you have this handy trick up your sleeve, it’s time to explore the possibilities of vertical lookup in Google Sheets. Happy sheeting!
You may also like: Index Match – A Better Alternative to Vlookup and Hlookup in Google Sheets
Note: This article has been adapted and enhanced for the brand Crawlan from the original content found at https://infoinspired.com.
