Google sheets - query cell value contains value in comma separated cell
-1
I'm trying to query a group of say fruits so I'm grouped these into a comma-separated cell and I want to query that cell but I don't get any results. When I just one value in that cell says Apple it wants a result. I thought using contains would allow me to do that in a query? If not are they better methods?
Table
A B
Bob Apple
May Orange
Simon Apple
Sam Pear
Tom Grape
Query
=IFERROR(QUERY('Sheet1'!$A2:$AB500, "select A,C,Y where D contains 'Staff' and (K='Y' or L='Y') and C contains '"&G7&"' ", 0),"None")
G7 contains 'Apple,Pear,Grape,Orange'
google-sheets google-sheets-query
asked Nov 22 at 18:05
Grimlockz
1,03741931
add a comment |
-1
I'm trying to query a group of say fruits so I'm grouped these into a comma-separated cell and I want to query that cell but I don't get any results. When I just one value in that cell says Apple it wants a result. I thought using contains would allow me to do that in a query? If not are they better methods?
Table
A B
Bob Apple
May Orange
Simon Apple
Sam Pear
Tom Grape
Query
=IFERROR(QUERY('Sheet1'!$A2:$AB500, "select A,C,Y where D contains 'Staff' and (K='Y' or L='Y') and C contains '"&G7&"' ", 0),"None")
G7 contains 'Apple,Pear,Grape,Orange'
google-sheets google-sheets-query
asked Nov 22 at 18:05
Grimlockz
1,03741931
add a comment |
-1
-1
-1
I'm trying to query a group of say fruits so I'm grouped these into a comma-separated cell and I want to query that cell but I don't get any results. When I just one value in that cell says Apple it wants a result. I thought using contains would allow me to do that in a query? If not are they better methods?
Table
A B
Bob Apple
May Orange
Simon Apple
Sam Pear
Tom Grape
Query
=IFERROR(QUERY('Sheet1'!$A2:$AB500, "select A,C,Y where D contains 'Staff' and (K='Y' or L='Y') and C contains '"&G7&"' ", 0),"None")
G7 contains 'Apple,Pear,Grape,Orange'
google-sheets google-sheets-query
asked Nov 22 at 18:05
Grimlockz
1,03741931
I'm trying to query a group of say fruits so I'm grouped these into a comma-separated cell and I want to query that cell but I don't get any results. When I just one value in that cell says Apple it wants a result. I thought using contains would allow me to do that in a query? If not are they better methods?
Table
A B
Bob Apple
May Orange
Simon Apple
Sam Pear
Tom Grape
Query
=IFERROR(QUERY('Sheet1'!$A2:$AB500, "select A,C,Y where D contains 'Staff' and (K='Y' or L='Y') and C contains '"&G7&"' ", 0),"None")
G7 contains 'Apple,Pear,Grape,Orange'
google-sheets google-sheets-query
google-sheets google-sheets-query
asked Nov 22 at 18:05
Grimlockz
1,03741931
asked Nov 22 at 18:05
Grimlockz
1,03741931
asked Nov 22 at 18:05
Grimlockz
1,03741931
asked Nov 22 at 18:05
Grimlockz
1,03741931
asked Nov 22 at 18:05
Grimlockz
1,03741931
1,03741931
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
Try changing this part
C contains '"&G7&"'
to
C matches '"&SUBSTITUTE(G7, ",", "|")&"'
and see if that helps?
EDIT: if you have brackets in the range and in G7 try
=ArrayFormula(IFERROR(QUERY({A:C, regexreplace(B:B, "(|)",)}, "select Col1 where Col3 contains 'Team' and Col4 matches '"&SUBSTITUTE(REGEXREPLACE(D2, "(|)",), ",", "|")&"' ", 0),"None"))
and see if that works?
answered Nov 23 at 8:26
JPV
10.5k21425
Works like a charm
– Grimlockz
Nov 23 at 10:38
It doesn't like brackets for G7 if you apple (red), apple (green), Orange,..., etc it doesn't pick up any of the cells with the brackets
– Grimlockz
Nov 27 at 14:25
That is because brackets represent a capturing group in a regular expression. You can remove those brackets by replacing those brackets:: C matches '"&SUBSTITUTE(REGEXREPLACE(G7, "(|)"; ), ",", "|")&"'
– JPV
Nov 27 at 15:17
Thanks - that does make sense with it being an escaping parenthesis but I'm still getting the same results
– Grimlockz
Nov 27 at 15:25
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 at 12:51
|
show 16 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436243%2fgoogle-sheets-query-cell-value-contains-value-in-comma-separated-cell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Try changing this part
C contains '"&G7&"'
to
C matches '"&SUBSTITUTE(G7, ",", "|")&"'
and see if that helps?
EDIT: if you have brackets in the range and in G7 try
=ArrayFormula(IFERROR(QUERY({A:C, regexreplace(B:B, "(|)",)}, "select Col1 where Col3 contains 'Team' and Col4 matches '"&SUBSTITUTE(REGEXREPLACE(D2, "(|)",), ",", "|")&"' ", 0),"None"))
and see if that works?
answered Nov 23 at 8:26
JPV
10.5k21425
Works like a charm
– Grimlockz
Nov 23 at 10:38
It doesn't like brackets for G7 if you apple (red), apple (green), Orange,..., etc it doesn't pick up any of the cells with the brackets
– Grimlockz
Nov 27 at 14:25
That is because brackets represent a capturing group in a regular expression. You can remove those brackets by replacing those brackets:: C matches '"&SUBSTITUTE(REGEXREPLACE(G7, "(|)"; ), ",", "|")&"'
– JPV
Nov 27 at 15:17
Thanks - that does make sense with it being an escaping parenthesis but I'm still getting the same results
– Grimlockz
Nov 27 at 15:25
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 at 12:51
|
show 16 more comments
1
Try changing this part
C contains '"&G7&"'
to
C matches '"&SUBSTITUTE(G7, ",", "|")&"'
and see if that helps?
EDIT: if you have brackets in the range and in G7 try
=ArrayFormula(IFERROR(QUERY({A:C, regexreplace(B:B, "(|)",)}, "select Col1 where Col3 contains 'Team' and Col4 matches '"&SUBSTITUTE(REGEXREPLACE(D2, "(|)",), ",", "|")&"' ", 0),"None"))
and see if that works?
answered Nov 23 at 8:26
JPV
10.5k21425
Works like a charm
– Grimlockz
Nov 23 at 10:38
It doesn't like brackets for G7 if you apple (red), apple (green), Orange,..., etc it doesn't pick up any of the cells with the brackets
– Grimlockz
Nov 27 at 14:25
That is because brackets represent a capturing group in a regular expression. You can remove those brackets by replacing those brackets:: C matches '"&SUBSTITUTE(REGEXREPLACE(G7, "(|)"; ), ",", "|")&"'
– JPV
Nov 27 at 15:17
Thanks - that does make sense with it being an escaping parenthesis but I'm still getting the same results
– Grimlockz
Nov 27 at 15:25
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 at 12:51
|
show 16 more comments
1
1
1
Try changing this part
C contains '"&G7&"'
to
C matches '"&SUBSTITUTE(G7, ",", "|")&"'
and see if that helps?
EDIT: if you have brackets in the range and in G7 try
=ArrayFormula(IFERROR(QUERY({A:C, regexreplace(B:B, "(|)",)}, "select Col1 where Col3 contains 'Team' and Col4 matches '"&SUBSTITUTE(REGEXREPLACE(D2, "(|)",), ",", "|")&"' ", 0),"None"))
and see if that works?
answered Nov 23 at 8:26
JPV
10.5k21425
Try changing this part
C contains '"&G7&"'
to
C matches '"&SUBSTITUTE(G7, ",", "|")&"'
and see if that helps?
EDIT: if you have brackets in the range and in G7 try
=ArrayFormula(IFERROR(QUERY({A:C, regexreplace(B:B, "(|)",)}, "select Col1 where Col3 contains 'Team' and Col4 matches '"&SUBSTITUTE(REGEXREPLACE(D2, "(|)",), ",", "|")&"' ", 0),"None"))
and see if that works?
answered Nov 23 at 8:26
JPV
10.5k21425
edited Nov 29 at 8:16
answered Nov 23 at 8:26
JPV
10.5k21425
answered Nov 23 at 8:26
JPV
10.5k21425
answered Nov 23 at 8:26
JPV
10.5k21425
10.5k21425
Works like a charm
– Grimlockz
Nov 23 at 10:38
It doesn't like brackets for G7 if you apple (red), apple (green), Orange,..., etc it doesn't pick up any of the cells with the brackets
– Grimlockz
Nov 27 at 14:25
That is because brackets represent a capturing group in a regular expression. You can remove those brackets by replacing those brackets:: C matches '"&SUBSTITUTE(REGEXREPLACE(G7, "(|)"; ), ",", "|")&"'
– JPV
Nov 27 at 15:17
Thanks - that does make sense with it being an escaping parenthesis but I'm still getting the same results
– Grimlockz
Nov 27 at 15:25
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 at 12:51
|
show 16 more comments
Works like a charm
– Grimlockz
Nov 23 at 10:38
It doesn't like brackets for G7 if you apple (red), apple (green), Orange,..., etc it doesn't pick up any of the cells with the brackets
– Grimlockz
Nov 27 at 14:25
That is because brackets represent a capturing group in a regular expression. You can remove those brackets by replacing those brackets:: C matches '"&SUBSTITUTE(REGEXREPLACE(G7, "(|)"; ), ",", "|")&"'
– JPV
Nov 27 at 15:17
Thanks - that does make sense with it being an escaping parenthesis but I'm still getting the same results
– Grimlockz
Nov 27 at 15:25
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 at 12:51
Works like a charm
– Grimlockz
Nov 23 at 10:38
Works like a charm
– Grimlockz
Nov 23 at 10:38
It doesn't like brackets for G7 if you apple (red), apple (green), Orange,..., etc it doesn't pick up any of the cells with the brackets
– Grimlockz
Nov 27 at 14:25
It doesn't like brackets for G7 if you apple (red), apple (green), Orange,..., etc it doesn't pick up any of the cells with the brackets
– Grimlockz
Nov 27 at 14:25
That is because brackets represent a capturing group in a regular expression. You can remove those brackets by replacing those brackets:: C matches '"&SUBSTITUTE(REGEXREPLACE(G7, "(|)"; ), ",", "|")&"'
– JPV
Nov 27 at 15:17
That is because brackets represent a capturing group in a regular expression. You can remove those brackets by replacing those brackets:: C matches '"&SUBSTITUTE(REGEXREPLACE(G7, "(|)"; ), ",", "|")&"'
– JPV
Nov 27 at 15:17
Thanks - that does make sense with it being an escaping parenthesis but I'm still getting the same results
– Grimlockz
Nov 27 at 15:25
Thanks - that does make sense with it being an escaping parenthesis but I'm still getting the same results
– Grimlockz
Nov 27 at 15:25
1
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 at 12:51
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 at 12:51
|
show 16 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436243%2fgoogle-sheets-query-cell-value-contains-value-in-comma-separated-cell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
