I am a complete beginner. I built a simple database following a YouTube video and it works fine, then I wanted to add a search box, and this works following another YouTube video for one column only, my problem is how do I add other columns.
This is the script that works for 1 column
=QUERY(Data!A1:J,"SELECT * WHERE LOWER(E)= LOWER("""&B2&""")", 1)
If I change the E in (E) to one of the other columns it works, but when I try to add them altogether is doesn't
I would like the columns (E, F, G, H and J). I've never done anything like this before, its just for my movies and TV series, I wanted to search for keywords, etc, so if I wanted to look for just comedy then I could write comedy in the search box B2 and all the comedies would show up, etc.
It looks like the issue is related to the use of the LOWER function when the value in the column is not just text but also includes numbers. To handle this, you can modify the formula to use LOWER only for text columns and leave it out for columns that contain numbers.
Here's an adjusted formula:
=QUERY(Data!A1:J, "SELECT * WHERE LOWER(E) = LOWER('"&B2&"') OR LOWER(F) = LOWER('"&B2&"') OR LOWER(G) = LOWER('"&B2&"') OR LOWER(H) = LOWER('"&B2&"') OR LOWER(J) = LOWER('"&B2&"')", 1)
In this modified formula, I've kept the LOWER function only for columns E, F, G, H, and J. If column E contains text and numbers, you might want to apply LOWER only to the text part of it, or you can remove LOWER altogether for such columns.
Try this adjustment, and it should help resolve the issue with the TV series "24" in your database.