Google Sheets – Using Query to Find a Value in any Column

The saga continues on from this and this to find a workable solution to find a value from any column using query. I happened upon another route (see accepted answer), which was to use a spreadsheet function to generate a csv list of columns which could then be applied to the select query. Credits as always to the original poster! I then needed to modify the substitutions to create more of the select query, and found I could build the whole query syntax in two cells then apply these to the query function. I was so chuffed with myself i made up a proper spreadsheet layout with comments and made a video of it for demonstration purposes.

The essence of the thing is as follows:

You have a table with various values assigned to each name in a list down the left. you want to call back the names who have a particular value assigned to them. You could have many many columns (in fact the solution I will use this with has @ 70 !) so it is not practical to code the select query by hand.

Generate the csv list of columns

In its simplest form like this:

which produces:

B,C,D,E,F,G,H,I,J,K,L

I needed to edit this formula to match the correct syntax for the query, so replaced the join delimiter “,” with ” or “ , and the blank; “” at the end with ‘”&D9&”‘ , in order to pull in the value placed in cell D9. If the value in D9 was A.

This then produced:

B = ‘A’ or C = ‘A’ or D = ‘A’ or E = ‘A’ or F = ‘A’ or G = ‘A’ or H = ‘A’ or I = ‘A’ or J = ‘A’ or K = ‘A’ or L = ‘A’

I couldn’t get the query formula to take this as it was, so needed to create the rest of the select query in another cell and to then concatenate the two together:

select A where

select A where B = ‘A’ or C = ‘A’ or D = ‘A’ or E = ‘A’ or F = ‘A’ or G = ‘A’ or H = ‘A’ or I = ‘A’ or J = ‘A’ or K = ‘A’ or L = ‘A’

All that was then needed was to build the query formula using the table range and the cell reference for the concatenate function:

This method seems to be much quicker and easier than my last effort with a google apps script, and provides for more options in terms of ways to build queries from spreadsheet cells, using dropdowns.

Leave a Reply

Your email address will not be published. Required fields are marked *