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 '18 at 18:05
GrimlockzGrimlockz
1,04241931
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 '18 at 18:05
GrimlockzGrimlockz
1,04241931
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 '18 at 18:05
GrimlockzGrimlockz
1,04241931
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 '18 at 18:05
GrimlockzGrimlockz
1,04241931
asked Nov 22 '18 at 18:05
GrimlockzGrimlockz
1,04241931
asked Nov 22 '18 at 18:05
GrimlockzGrimlockz
1,04241931
asked Nov 22 '18 at 18:05
GrimlockzGrimlockz
1,04241931
asked Nov 22 '18 at 18:05
GrimlockzGrimlockz
1,04241931
1,04241931
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 '18 at 8:26
JPVJPV
11.1k21526
Works like a charm
– Grimlockz
Nov 23 '18 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 '18 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 '18 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 '18 at 15:25
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 '18 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 '18 at 8:26
JPVJPV
11.1k21526
Works like a charm
– Grimlockz
Nov 23 '18 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 '18 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 '18 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 '18 at 15:25
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 '18 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 '18 at 8:26
JPVJPV
11.1k21526
Works like a charm
– Grimlockz
Nov 23 '18 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 '18 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 '18 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 '18 at 15:25
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 '18 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 '18 at 8:26
JPVJPV
11.1k21526
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 '18 at 8:26
JPVJPV
11.1k21526
edited Nov 29 '18 at 8:16
answered Nov 23 '18 at 8:26
JPVJPV
11.1k21526
answered Nov 23 '18 at 8:26
JPVJPV
11.1k21526
answered Nov 23 '18 at 8:26
JPVJPV
11.1k21526
11.1k21526
Works like a charm
– Grimlockz
Nov 23 '18 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 '18 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 '18 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 '18 at 15:25
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 '18 at 12:51
|
show 16 more comments
Works like a charm
– Grimlockz
Nov 23 '18 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 '18 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 '18 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 '18 at 15:25
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 '18 at 12:51
Works like a charm
– Grimlockz
Nov 23 '18 at 10:38
Works like a charm
– Grimlockz
Nov 23 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 15:25
1
1
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 '18 at 12:51
In your new formula, change Col2 to Col4.. that should do it.
– JPV
Dec 4 '18 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.
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
