Have you ever struggled with using Vlookup and comma-separated values in Google Sheets? If so, you’re in the right place! In this article, I’ll share two essential tips to help you conquer these challenges and become a Sheets pro.
Vlookup in a Comma-Separated Value Column (List) in Google Sheets
Using the Vlookup function in a comma-separated value column is easier than you think. Let me show you two simple methods that will make your life a lot easier.
Using Asterisk Wildcard Character with Vlookup Search Key
One widely-used method involves using the asterisk () wildcard character. Let’s say you want to search for the key “product 11” in the first column of a comma-separated list. To do this, use “product 11*” as your search key within the Vlookup formula. Here’s the syntax:
VLOOKUP(search_key, range, index, [is_sorted])For example, if your search key is in cell E2 and your range is B2:C8, your formula would be:
=vlookup("*" & E2 & "*",B2:C8,2,0)
Using Regexmatch with Vlookup Search Key
While the wildcard method is effective, there’s another technique you can use to achieve case-sensitive Vlookups in a comma-separated value column. This involves using the regexmatch function in combination with Vlookup. Although it may seem more complex, it offers some advantages. Let me explain.
Here’s an example formula:
=ArrayFormula(vlookup(TRUE,{regexmatch(B2:B8,E2),C2:C8},2,0))The search key here is the Boolean value TRUE, and the range is {regexmatch(B2:B8,E2),C2:C8}. The first column of this range contains TRUE or FALSE values due to the regex, rather than the actual comma-separated list. This formula achieves the same result as the wildcard formula mentioned earlier.
To make the Vlookup case-insensitive, modify the formula as follows:
=ArrayFormula(vlookup(TRUE,{regexmatch(lower(B2:B8),lower(E2)),C2:C8},2,0))Comma-Separated Search Keys in Vlookup in Google Sheets
Now, let’s explore a different scenario. Imagine you have comma-separated search keys in Google Sheets, rather than comma-separated values in a column. Don’t worry – I have a solution for this too!
Assuming you want to return the values 5 and 25 in two cells (F2:G2 or F2:F3), or the total 30 in cell F2, here’s the formula:
=ArrayFormula(IFNA(vlookup(trim(split(E2,",")),B2:C12,2,0)))Alternatively, if you want to achieve the same result in a single cell, use this formula:
=ArrayFormula(SUM(IFNA(vlookup(trim(split(E2,",")),B2:C12,2,0))))The key here is to split and trim the search keys before performing the Vlookup. By doing this, you ensure accurate results.
I hope you found these Vlookup tips helpful! By mastering these techniques, you’ll become a Google Sheets pro in no time. If you’re eager to learn more, don’t forget to check out Crawlan.com for additional valuable insights and tips!
Remember, Google Sheets is a powerful tool that can streamline your work and boost your productivity. Now go forth and conquer your spreadsheets like a pro!
