-2

I wrote the Indexes of my notebooks in Google sheets to make them easy to find. But the Find function only looks for one word in the Google Sheets cell.

My notes look like this:

  • "Chemistry class notes of Sept. 14, 2021"

  • "Chemistry class notes of Sep. 15, 2021"

I want to find the index for Chemistry Class notes of Sept. 15, 2021 by typing:

  • "chemistry sep 15"

How can I get it done?

Link to my spreadsheet

thx so much

This is the code I have written after watching some YouTube Videos (Google Sheets - Search, QUERY function by chicagocomputerclasses).

It works perfectly but it only searches in one column.

I want it to work in four or five columns.

I don't know how to modify this code to accomplish that.

=QUERY(AMF!A1:Z, "SELECT A WHERE 1=1 "&"AND LOWER (A) LIKE LOWER (""%" &JOIN("%"") AND LOWER (A) LIKE LOWER(""%", SPLIT(B1, " "))&"%"")",2)

I want to modify the code so it works in other columns besides Column "A"

Thx so much for your help in advance.

5
  • 1
    The sample spreadsheet gives "Access denied". Share it as anyone with link can edit. Sep 19 at 7:58
  • 1
    Does this answer your question? Query or Filter formula to 'search for' and 'return' multiple words
    – Tedinoz
    Sep 20 at 1:43
  • Hi and welcome. You ask How can I get it done? Would you please summarise any research that you have done, or your own efforts to solve this problem, or adapt solutions for similar scenarios to the problem.
    – Tedinoz
    Sep 20 at 1:45
  • This is the code I have written after watching some YouTube Videos. It works perfectly but it only searches in one column. I want it to work in four or five columns. I don't know how to modify this code to accomplish that. =QUERY(AMF!A1:Z, "SELECT A WHERE 1=1 "&"AND LOWER (A) LIKE LOWER (""%" &JOIN("%"") AND LOWER (A) LIKE LOWER(""%", SPLIT(B1, " "))&"%"")",2) I want to modify the code so it works in other columns besides Column "A" Thx so much for your help in advance.
    – arshad
    Sep 20 at 6:39
  • Your question should include ALL the relevant information relating to your problem. Please include an example of your data and an example of a successful enquiry in your question - this could be text or an image. Please do not require volunteers to open and view your spreadsheet in order to understand your question regarding searching multiple columns.
    – Tedinoz
    Sep 20 at 8:21
0

You have a QUERY that takes space-separated values and searches for match(es) in Column A. However you have several columns of data and you want to modify your formula to include those other columns.

The solution is to create a array by "stacking" each of the data columns on top of the other. This becomes the data array for your Query.

Note: The where clause of your Query is completely unchanged.

Use this formula:

=query({A2:A;B2:B;C2:C}, "select Col1 where Col1 is not null and 1=1 and lower(Col1) like LOWER(""%" &JOIN("%"") and lower(Col1) like LOWER(""%", SPLIT($G$1, " "))&"%"")", 0)

  • Cell $G$1 contains the search terms. You can change this cell reference to any another location.
  • The query formula, data array and the location of the search terms can be on different sheets.
  • In the example below, the "Output" header is manually entered. The query is in the cell immediately below this header

Data

data


Results

results


Had you better understood the mechanics of Google Sheets in terms of your scenario, you would have found an answer in Consolidate columns from a dynamic range into a single column in Google Sheets ht: @Rubén

6
  • thx a million Let me try & update you. thx again
    – arshad
    Sep 21 at 5:06
  • Your formula is working fine but I would prefer if it gives me a column by column result like in Column A the following were the matches & in column B the following were the matches etc. & if the results could be on the next page not on on the page where is data is. Thx so much again
    – arshad
    Sep 21 at 22:05
  • but I would prefer if I can understand this BUT... it's not what you asked in your question (in fact you didn't include an example of a search parameter nor an expected outcome) nor is it something that one can concoct in a few moments (I know, I've tried). You should prepare a new question for this.
    – Tedinoz
    Sep 22 at 5:54
  • BTW, if this answer answered your question, you might consider "accepting" it.
    – Tedinoz
    Sep 22 at 5:55
  • Thx so much for your help. I have noticed that if the same formula is pasted where ever the results are expected & change all references to any Column to that column, it also works. Thx again.
    – arshad
    Sep 22 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.