CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup
MBearnstein37
MBearnstein37

Reputation: 75

Using VLOOKUP or MATCH for cells that contain comma separated values

I am trying to use either VLOOKUP or a combination of INDEX and MATCH for a small project I am working on in Google Sheets, but I am running into trouble when a cell contains comma-separated values. I have provided an example below:

Example

Column A and Column B make up the search range, and Column C contains the search keys. Column D uses the VLOOKUP method, and Column E uses the INDEX and MATCH method. The formulas for the cells in the first row are as follows:

=VLOOKUP("*"&$C1&"*", A$1:B$4, 2, FALSE)

=INDEX($B$1:$B$4, MATCH("*"&$C1&"*", $A$1:$A$4, 0))

Columns D and E should spell out "SUCCESS" vertically if everything works correctly, but obviously, there are some issues. When I originally started looking for help, I found the method of appending a wildcard character (*) to the beginning and end of the search key in order to disregard text before or after the value I am searching for. This works except when the criteria for one search key can match a different search key, for example, when I am searching in Row 1 for "Pg 3" but "Pg 32" and "Pg 33" also exist. The problem only seems to occur when the value I am searching for is not in a cell by itself AND when it is preceded by a "matching" value in an earlier row (e.g. "Pg 33" matching the "Pg 3" key in Row 1 and "Pg 64" matching the "Pg 6" key in Row 7).

Unfortunately, in the project I am working on, I will not be able to sort the range into alphanumeric order, so any help towards a workaround is appreciated, preferably just using formulas and not delving into Google Apps Script. Hopefully, my explanation is clear enough, and I have no preference whether I use VLOOKUP or MATCH.

Upvotes: 1

Views: 1670

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(IFERROR(VLOOKUP(C1:C, 
 TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(IFERROR(SPLIT(A1:A, ","))<>"", "♦"&SPLIT(A1:A, ",")&"♠"&B1:B, ))
 ,,999^99)),,999^99), "♦")), "♠")), 2, 0)))

0

Upvotes: 2

Related Questions