Welcome to the ultimate guide on how to Vlookup part of a sentence in a list of words in Google Sheets. In this tutorial, we will explore a workaround formula that allows you to achieve this task effortlessly. So, let’s get started!
The Challenge with Regular VLOOKUP
First, let’s understand the difference between the regular VLOOKUP formula and the one we are about to learn. The regular VLOOKUP formula is designed to search for a specific value in a column. However, when it comes to searching for part of a sentence or a larger search key in a list of words, the regular VLOOKUP falls short.
For instance, imagine we have the search key “South Dakota Apples” and we want to find it in a column containing a list of places such as North Carolina, South Dakota, and North Dakota, and retrieve the respective capital city from the next column. Simply splitting the sentence and using the outputs as search keys won’t yield the correct result. Therefore, we need a different approach.
Image #1 – Non-working Sentence Lookup
The Workaround Using Countif, Filter, Len, Sort, Index, and Ifna
To tackle this challenge, we will leverage a combination of Countif, Filter, Len, Sort, Index, and Ifna functions. This workaround formula can be used in two ways:
- Non-array formula: This formula handles one search key at a time. Simply insert it in cell D2 and copy-paste it down as needed.
=ifna(index(sort(filter(B2:B,countif(C2,"*"&A2:A&"*")),filter(Len(A2:A),countif(C2,"*"&A2:A&"*")),0),1))
Image #2 – Working Sentence Lookup
- Spill Down formula: This array formula allows you to handle multiple search keys simultaneously. Start by ensuring that column D (D2:D) is empty, then use the following formula in cell D2:
=Map(C2:C,lambda(r,ifna(index(sort(filter(B2:B,countif(r,"*"&A2:A&"*")),filter(Len($A$2:$A),countif(r,"*"&A2:A&"*")),0),1))))And that’s it! With these formulas, you can successfully Vlookup a sentence in a list of words in Google Sheets.
Anatomy of the Formula
Let’s delve into the logic behind the formula for a better understanding.
The formula starts with the COUNTIF function, which counts the occurrences of specific values (A2:A) against the search key (C2). By using wildcards, we can capture partial matches.
COUNTIF(range, criterion)Next, we apply a FILTER function to extract the values (B2:B) that match the Countif result.
=filter(B2:B,countif(C2,"*"&A2:A&"*"))To ensure the best possible match, we SORT the filtered values based on the length of the corresponding search key values (A2:A) in descending order.
=sort(filter(B2:B,countif(C2,"*"&A2:A&"*")),filter(Len(A2:A),countif(C2,"*"&A2:A&"*")),0)Finally, we use the INDEX function to extract the first value and IFNA to return a blank cell if no match is found.
Conclusion
By following this workaround formula, you can effectively Vlookup part of a sentence in a list of words in Google Sheets. We hope you find this tutorial helpful for your data analysis needs. For more insightful tips and tricks, visit Crawlan.com. Stay tuned for more exciting content. Happy analyzing!
