The topic, Vlookup, and comma-separated values in Google Sheets may not seem to give you a clear picture of the content, right?

So before starting, let me clarify it.

In this Google Sheets tutorial related to Vlookup tips, I am trying to answer the below two queries pertaining to Vlookup.

1. How to use Vlookup in a comma-separated value column (list) in Google Sheets?
2. How to use comma-separated search keys in Vlookup?

Since I am going to address/elaborate on two Vlookup problems, I have decided to keep the topic so.

Let’s start with the first Vlookup Google Sheets tip.

Vlookup in a Comma-Separated Value Column (List) in Google Sheets

Using the Vlookup function in a comma-separated value column is pretty simple in Google Sheets.

Here is how to do that.

There are different Vlookup approaches, and I am introducing to you two simple methods.

The second method is for those who want to bring case sensitivity to the searches.

Using Asterisk Wildcard Character with Vlookup Search Key

It is the widely used method to Vlookup in a comma-separated value column in Google Sheets.

It’s by using the asterisk (*) wildcard character.

You May Like: How to Use Wildcard Characters in Google Sheets Functions.

Assume the search key you wish to search down in the first column of the comma-separated list is “product 11”.

If you plan to use the search key within the Vlookup, use it as “*product 11*”.

Syntax:

VLOOKUP(search_key, range, index, [is_sorted])

search_key: "*product 11*"

range: B2:C8 (in this B2:B8 contain the comma-separated list, see the image below)

Formula 1:

=vlookup("*product 11*",B2:C8,2,0)

Formula 2:

Else, if the search key “product 11” is in a cell, for example, in cell E2, use it as "*"&E2&"*".

=vlookup("*"&E2&"*",B2:C8,2,0)

Must Read: Partial Match in Vlookup in Google Sheets [Text, Numeric, and Date]

Note:-

When you Vlookup comma-separated value using wildcards as above in Google Sheets, you may face an issue.

Imagine the value in B2 is “product 10, product 11, product 12” and B8 is “product 1, product 3”.

The search key “product 1” in E2 will find a match in B2, not in B8!

In that case, you must intelligently use the wildcard. Just include a comma before the second asterisk as below.

Formula 3:

=vlookup("*"&E2&",*",B2:C8,2,0)

Let’s go to the second Vlookup Google Sheets tip.

Using Regexmatch with Vlookup Search Key

The wildcard character method itself is enough when we Vlookup in a comma-separated value column in Google Sheets.

Then why should one prefer the below regex method to the wildcard approach?

I’ll answer this question after one example.

Formula 4:

=ArrayFormula(vlookup(TRUE,{regexmatch(B2:B8,E2),C2:C8},2,0))

As you can see, the Vlookup search key here is the Boolean value TRUE, not the cell reference E2 (please see the formula above).

Also, the range is not B2:C8, but {regexmatch(B2:B8,E2),C2:C8} where the first column contains TRUE or FALSE values because of the regex, not the comma-separated list.

This formula is equal to the earlier wildcard formula # 2.

Consider this method for case-sensitive (exact) Vlookup in the comma-separated value column.

For example, it will match “product 11”, not “PRODUCT 11”, or “Product 11”. I hope you have got it.

It’s simple to turn this Vlookup case insensitive! Here it is.

Formula 5:

=ArrayFormula(vlookup(TRUE,{regexmatch(lower(B2:B8),lower(E2)),C2:C8},2,0))

Must Read: Case Sensitive Vlookup in Google Sheets [Solved]

Comma-Separated Search Keys in Vlookup in Google Sheets

Let’s address a quite different scenario!

In this case, there are no columns, especially the first column, in the Vlookup range containing comma-separated values.

Instead, the search keys are comma-separated or delimited.

See this sample data and comma-separated Vlookup search keys in cell E2.

Here either I want to return the values 5 and 25 in two cells (in F2:G2 or F2:F3) or the total 30 in F2.

Didn’t get it?

The comma-separated search keys to use in Vlookup are “product 1” and “product 11”.

These two keys will be available in the Vlookup search in cell B2 and B12 in the range B2:C12.

The corresponding values to return from column 2 are 5 and 25.

Vlookup Formula without Sum:

=ArrayFormula(IFNA(vlookup(trim(split(E2,",")),B2:C12,2,0)))

Vlookup Formula with Sum:

=ArrayFormula(SUM(IFNA(vlookup(trim(split(E2,",")),B2:C12,2,0))))

Formula Explanation

By default, Vlookup will consider the comma-separated search keys as a whole and may fail to find a match.

So we must split and trim the keys and search. Find the steps below

Split and Trim Vlookup Search Keys

1. Split:

=split(E2,",")

2. Split and Trim:

=ArrayFormula(trim(split(E2,",")))

Included IFNA to remove mismatch #NA! error values and SUM to sum the Vlookup Output.

Related: Vlookup with Sum in Multiple Rows in Google Sheets.

I hope you have enjoyed the above two Vlookup tips in Google Sheets.